15 Messages
Digest #4203
2b
Re: I have always used RCHGetYahooQuotes for my current or last day by "Randy Harmelink" rharmelink
2d
Re: I have always used RCHGetYahooQuotes for my current or last day by "Randy Harmelink" rharmelink
Messages
Fri Nov 3, 2017 2:59 pm (PDT) . Posted by:
eburgos@comcast.net
Hi Randy thanks for all your help.
I am using the YahooPortfolioView spreadsheet but ran into a snag.
I added your spreadsheet as a tab on my existing excel investment tracking file. I added all my stocks to the YahooPortfolioView worksheet.
Example I have a tab which lists all my existing stock purchases listed by date of purchase. I can have multiple purchases of the same stock at different dates.
This tab is named "Stocks" All stock symbols are in Column A.
The new tab is named "YahooPortfolioView"
I used this formula but get inconsistent results. Only a few stock prices show up in my "Stocks" tab even though they are all in the "YahooPortfolioView" tab.
=IF(A32=YahooPortfolioView!C:C,YahooPortfolioView!Q:Q,0)
A32 has stock symbol in Stock tab
C:C has column with stock symbol in YahooPortfolioView tab
Q:Q has stock price l in YahooPortfolioView tab.
Any help is appreciated
best regards,
Ernie
I am using the YahooPortfolioView spreadsheet but ran into a snag.
I added your spreadsheet as a tab on my existing excel investment tracking file. I added all my stocks to the YahooPortfolioView worksheet.
Example I have a tab which lists all my existing stock purchases listed by date of purchase. I can have multiple purchases of the same stock at different dates.
This tab is named "Stocks" All stock symbols are in Column A.
The new tab is named "YahooPortfoli
I used this formula but get inconsistent results. Only a few stock prices show up in my "Stocks" tab even though they are all in the "YahooPortfoli
=IF(A32=YahooPortfo
A32 has stock symbol in Stock tab
C:C has column with stock symbol in YahooPortfolioView tab
Q:Q has stock price l in YahooPortfolioView tab.
Any help is appreciated
best regards,
Ernie
Fri Nov 3, 2017 3:38 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I like the method. To me, it makes sense to separate the data retrieval
from the data presentation. It makes it easier if the data retrieval
process needs to be updated. I've been doing that on more and more of my
workbooks.
You need to do a lookup, which I usually do as a MATCH/INDEX combination.
For example, something like this for getting "Last Price":
=INDEX(smfGetYahooPortfolioView!$Q:$Q,MATCH($C4,smfGetYahooPortfolioView!$C:$C,0))
...where:
-- $C4 is the ticker symbol I am looking up.
-- smfGetYahooPortfolioView!$C:$C is the column of that workbook with the
ticker symbols being returned by the function
-- smfGetYahooPortfolioView!$Q:$Q is the column of that workbook with the
data you want that is returned by the function
You'd just need to change that the $Q:$Q column to get data from a
different column.
You could even assign a name to each of those two columns of data. Then the
lookup becomes:
=INDEX(rLastPrice,MATCH($C4,rSymbol,0))
On Fri, Nov 3, 2017 at 2:59 PM,
faaxh4q3wklntudi4mkv4wnauszlv2ovhedlnbjn@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I am using the YahooPortfolioView spreadsheet but ran into a snag.
>
> I added your spreadsheet as a tab on my existing excel investment tracking
> file. I added all my stocks to the YahooPortfolioView worksheet.
>
> Example I have a tab which lists all my existing stock purchases listed by
> date of purchase. I can have multiple purchases of the same stock at
> different dates.
>
> This tab is named "Stocks" All stock symbols are in Column A.
> The new tab is named "YahooPortfolioView"
>
> I used this formula but get inconsistent results. Only a few stock prices
> show up in my "Stocks" tab even though they are all in the
> "YahooPortfolioView" tab.
>
> =IF(A32=YahooPortfolioView!C:C,YahooPortfolioView!Q:Q,0)
>
> A32 has stock symbol in Stock tab
> C:C has column with stock symbol in YahooPortfolioView tab
> Q:Q has stock price l in YahooPortfolioView tab.
>
>
from the data presentation. It makes it easier if the data retrieval
process needs to be updated. I've been doing that on more and more of my
workbooks.
You need to do a lookup, which I usually do as a MATCH/INDEX combination.
For example, something like this for getting "Last Price":
=INDEX(smfGetYahooPortfolioView!$Q:$Q,MATCH($C4,smfGetYahooPortfolioView!$C:$C,0))
...where:
-- $C4 is the ticker symbol I am looking up.
-- smfGetYahooPortfolioView!$C:$C is the column of that workbook with the
ticker symbols being returned by the function
-- smfGetYahooPortfolioView!$Q:$Q is the column of that workbook with the
data you want that is returned by the function
You'd just need to change that the $Q:$Q column to get data from a
different column.
You could even assign a name to each of those two columns of data. Then the
lookup becomes:
=INDEX(rLastPrice,MATCH($C4,rSymbol,0))
On Fri, Nov 3, 2017 at 2:59 PM,
faaxh4q3wklntudi4mkv4wnauszlv2ovhedlnbjn@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I am using the YahooPortfolioView spreadsheet but ran into a snag.
>
> I added your spreadsheet as a tab on my existing excel investment tracking
> file. I added all my stocks to the YahooPortfolioView worksheet.
>
> Example I have a tab which lists all my existing stock purchases listed by
> date of purchase. I can have multiple purchases of the same stock at
> different dates.
>
> This tab is named "Stocks" All stock symbols are in Column A.
> The new tab is named "YahooPortfoli
>
> I used this formula but get inconsistent results. Only a few stock prices
> show up in my "Stocks" tab even though they are all in the
> "YahooPortfoli
>
> =IF(A32=YahooPortfo
>
> A32 has stock symbol in Stock tab
> C:C has column with stock symbol in YahooPortfolioView tab
> Q:Q has stock price l in YahooPortfolioView tab.
>
>
Fri Nov 3, 2017 3:49 pm (PDT) . Posted by:
eburgos@comcast.net
Thank You. Where would you like a donation to go?
Best regards,
Ernie
Best regards,
Ernie
Fri Nov 3, 2017 3:53 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Just pick your favorite charity. My "Amazon Smile" choice these days is the
FFRF. :)
On Fri, Nov 3, 2017 at 3:49 PM, faaxh4q3wklntudi4mkv4wnauszlv2ovhedlnbjn@
...
wrote:
>
> Where would you like a donation to go?
>
>
FFRF. :)
On Fri, Nov 3, 2017 at 3:49 PM, faaxh4q3wklntudi4mk
...
wrote:
>
> Where would you like a donation to go?
>
>
Fri Nov 3, 2017 4:35 pm (PDT) . Posted by:
eburgos@comcast.net
just set my account to FFRF.
Best regards,
Ernie
Best regards,
Ernie
Fri Nov 3, 2017 5:02 pm (PDT) . Posted by:
johnross999
I am wondering where can I find the documentation on this new function. Tx
Fri Nov 3, 2017 5:08 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The only documentation at this time is the cited example and the workbook
example on the announcements blog:
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171031.html
On Fri, Nov 3, 2017 at 5:02 PM, johnrr9@
...
wrote:
>
> I am wondering where can I find the documentation on this new function. Tx
>
example on the announcements blog:
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171031.html
On Fri, Nov 3, 2017 at 5:02 PM, johnrr9@
...
wrote:
>
> I am wondering where can I find the documentation on this new function. Tx
>
Fri Nov 3, 2017 4:09 pm (PDT) . Posted by:
"Allan" aprivoisermoi2005
Doesn't work for me...
=smfGetCSVFile("https://docs.google.com/spreadsheets/d/1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv")
<!DOCTYPE html...
On 11/03/2017 04:50 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]
wrote:
> Please don't do the RCHGetElementNumber() method unless it's for only
> one or two quotes. Doing it regularly for dozens or hundreds of stocks
> is just an incentive for a web site to restrict access to such data.
>
> Take a look at the latest entry
> <https://smf-add-in.blogspot.com/2017/11/tip-possible-google-method-to-get.html>
> on the announcements blog. The Google alternative might fit your needs?
>
> On Fri, Nov 3, 2017 at 2:16 PM, rr76012@
> ...
> wrote:
>
> Re: previous or last stock price quoate
>
> I have always used RCHGetYahooQuotes( , l1) for my current or last
> day stock price.
>
> Does anyone know of a rchgetelementnumber number or another way to
> get the last stock price?
>
>
=smfGetCSVFile("https://docs.google.com/spreadsheets/d/1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv")
<!DOCTYPE html...
On 11/03/2017 04:50 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]
wrote:
> Please don't do the RCHGetElementNumber() method unless it's for only
> one or two quotes. Doing it regularly for dozens or hundreds of stocks
> is just an incentive for a web site to restrict access to such data.
>
> Take a look at the latest entry
> <https://smf-add-in.blogspot.com/2017/11/tip-possible-google-method-to-get.html>
> on the announcements blog. The Google alternative might fit your needs?
>
> On Fri, Nov 3, 2017 at 2:16 PM, rr76012@
> ...
> wrote:
>
> Re: previous or last stock price quoate
>
> I have always used RCHGetYahooQuotes( , l1) for my current or last
> day stock price.
>
> Does anyone know of a rchgetelementnumber number or another way to
> get the last stock price?
>
>
Fri Nov 3, 2017 4:32 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
That's because you're using the link to the workbook, not to the published
CSV file.
If I click on the link in my example, it opens up in an EXCEL workbook as a
CSV file. If I click on your link below, it opens me up into view mode on
your Google sheet, within my browser.
On Fri, Nov 3, 2017 at 4:09 PM, Allan allanhasmail@
...
wrote:
> Doesn't work for me...
>
> =smfGetCSVFile("https://docs.google.com/spreadsheets/d/
> 1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv"
> <https://docs.google.com/spreadsheets/d/1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv>
> )
>
> <!DOCTYPE html...
> On 11/03/2017 04:50 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]
> wrote:
>
>
> Please don't do the RCHGetElementNumber() method unless it's for only one
> or two quotes. Doing it regularly for dozens or hundreds of stocks is just
> an incentive for a web site to restrict access to such data.
>
> Take a look at the latest entry
> <https://smf-add-in.blogspot.com/2017/11/tip-possible-google-method-to-get.html>
> on the announcements blog. The Google alternative might fit your needs?
>
> On Fri, Nov 3, 2017 at 2:16 PM, rr76012@
> ...
> wrote:
>
>> Re: previous or last stock price quoate
>>
>> I have always used RCHGetYahooQuotes( , l1) for my current or last day
>> stock price.
>> Does anyone know of a rchgetelementnumber number or another way to get
>> the last stock price?
>>
>
CSV file.
If I click on the link in my example, it opens up in an EXCEL workbook as a
CSV file. If I click on your link below, it opens me up into view mode on
your Google sheet, within my browser.
On Fri, Nov 3, 2017 at 4:09 PM, Allan allanhasmail@
...
wrote:
> Doesn't work for me...
>
> =smfGetCSVFile("https://docs.google.com/spreadsheets/d/
> 1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv"
> <https://docs.google.com/spreadsheets/d/1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv>
> )
>
> <!DOCTYPE html...
> On 11/03/2017 04:50 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]
> wrote:
>
>
> Please don't do the RCHGetElementNumber() method unless it's for only one
> or two quotes. Doing it regularly for dozens or hundreds of stocks is just
> an incentive for a web site to restrict access to such data.
>
> Take a look at the latest entry
> <https://smf-add-in.blogspot.com/2017/11/tip-possible-google-method-to-get.html>
> on the announcements blog. The Google alternative might fit your needs?
>
> On Fri, Nov 3, 2017 at 2:16 PM, rr76012@
> ...
> wrote:
>
>> Re: previous or last stock price quoate
>>
>> I have always used RCHGetYahooQuotes( , l1) for my current or last day
>> stock price.
>> Does anyone know of a rchgetelementnumber number or another way to get
>> the last stock price?
>>
>
Fri Nov 3, 2017 5:06 pm (PDT) . Posted by:
"Allan" aprivoisermoi2005
Oh, I see. Publish to the web, CSV file... but:
=smfGetCSVFile("https://docs.google.com/spreadsheets/d/e/2PACX-1vT_vHjLjmEtSC0SUJSOlbJKYV8dqMDob-rpdbwaf-6OjkrjHIX5il8d2gcYEXpwNyiKkGwG8ef3pRh9/pub?output=csv")
Nothing.
On 11/03/2017 06:32 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]
wrote:
> That's because you're using the link to the workbook, not to the
> published CSV file.
>
> If I click on the link in my example, it opens up in an EXCEL workbook
> as a CSV file. If I click on your link below, it opens me up into view
> mode on your Google sheet, within my browser.
>
> On Fri, Nov 3, 2017 at 4:09 PM, Allan allanhasmail@
> ...
> wrote:
>
> Doesn't work for me...
>
> =smfGetCSVFile("https://docs.google.com/spreadsheets/d/1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv"
> <https://docs.google.com/spreadsheets/d/1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv>)
>
> <!DOCTYPE html...
>
> On 11/03/2017 04:50 PM, Randy Harmelink rharmelink@gmail.com
> <mailto:rharmelink@gmail.com> [smf_addin] wrote:
>> Please don't do the RCHGetElementNumber() method unless it's for
>> only one or two quotes. Doing it regularly for dozens or hundreds
>> of stocks is just an incentive for a web site to restrict access
>> to such data.
>>
>> Take a look at the latest entry
>> <https://smf-add-in.blogspot.com/2017/11/tip-possible-google-method-to-get.html>
>> on the announcements blog. The Google alternative might fit your
>> needs?
>>
>> On Fri, Nov 3, 2017 at 2:16 PM, rr76012@
>> ...
>> wrote:
>>
>> Re: previous or last stock price quoate
>>
>> I have always used RCHGetYahooQuotes( , l1) for my current
>> or last day stock price.
>>
>> Does anyone know of a rchgetelementnumber number or another
>> way to get the last stock price?
>>
>
>
=smfGetCSVFile("https://docs.google.com/spreadsheets/d/e/2PACX-1vT_vHjLjmEtSC0SUJSOlbJKYV8dqMDob-rpdbwaf-6OjkrjHIX5il8d2gcYEXpwNyiKkGwG8ef3pRh9/pub?output=csv")
Nothing.
On 11/03/2017 06:32 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]
wrote:
> That's because you're using the link to the workbook, not to the
> published CSV file.
>
> If I click on the link in my example, it opens up in an EXCEL workbook
> as a CSV file. If I click on your link below, it opens me up into view
> mode on your Google sheet, within my browser.
>
> On Fri, Nov 3, 2017 at 4:09 PM, Allan allanhasmail@
> ...
> wrote:
>
> Doesn't work for me...
>
> =smfGetCSVFile("https://docs.google.com/spreadsheets/d/1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv"
> <https://docs.google.com/spreadsheets/d/1dpnsMwkd8mr9E0qK1OfJqlrkVbLfIZlGUfnZldCTajw/edit?usp=sharing,output=csv>)
>
> <!DOCTYPE html...
>
> On 11/03/2017 04:50 PM, Randy Harmelink rharmelink@gmail.com
> <mailto:rharmelink@gmail.com> [smf_addin] wrote:
>> Please don't do the RCHGetElementNumber() method unless it's for
>> only one or two quotes. Doing it regularly for dozens or hundreds
>> of stocks is just an incentive for a web site to restrict access
>> to such data.
>>
>> Take a look at the latest entry
>> <https://smf-add-in.blogspot.com/2017/11/tip-possible-google-method-to-get.html>
>> on the announcements blog. The Google alternative might fit your
>> needs?
>>
>> On Fri, Nov 3, 2017 at 2:16 PM, rr76012@
>> ...
>> wrote:
>>
>> Re: previous or last stock price quoate
>>
>> I have always used RCHGetYahooQuotes( , l1) for my current
>> or last day stock price.
>>
>> Does anyone know of a rchgetelementnumber number or another
>> way to get the last stock price?
>>
>
>
Fri Nov 3, 2017 5:26 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Hmm. Not sure what to tell you. I copied and pasted your formula and it
worked fine for me:
CurrentPrice
AAPL Apple Inc. $172.50
ADT ADT Corp $41.98
BAX Baxter International Inc $64.90
BBL BHP Billiton plc (ADR) $37.26
BCE BCE Inc. (USA) $47.45
BLX Banco Latinoamericano de Comerci Extr SA $28.47
CAT Caterpillar Inc. $136.63
FAST Fastenal Company $47.91
JNJ Johnson & Johnson $140.08
LLY Eli Lilly and Co $83.51
MRK Merck & Co., Inc. $56.06
Wait. A thought. Are your array-entering the formula over a range? If you
just entered it into a single cell, you'd get what looks like "nothing",
because it would just be showing the first column and first row of my
posted range above. The one that doesn't show anything?
On Fri, Nov 3, 2017 at 5:05 PM, Allan allanhasmail@
...
wrote:
> Oh, I see. Publish to the web, CSV file... but:
>
> =smfGetCSVFile("https://docs.google.com/spreadsheets/d/e/2PACX-1vT_
> vHjLjmEtSC0SUJSOlbJKYV8dqMDob-rpdbwaf-6OjkrjHIX5il8d2gcYEXpwNyiKkGwG
> 8ef3pRh9/pub?output=csv"
> <https://docs.google.com/spreadsheets/d/e/2PACX-1vT_vHjLjmEtSC0SUJSOlbJKYV8dqMDob-rpdbwaf-6OjkrjHIX5il8d2gcYEXpwNyiKkGwG8ef3pRh9/pub?output=csv>
> )
>
> Nothing.
>
worked fine for me:
CurrentPrice
AAPL Apple Inc. $172.50
ADT ADT Corp $41.98
BAX Baxter International Inc $64.90
BBL BHP Billiton plc (ADR) $37.26
BCE BCE Inc. (USA) $47.45
BLX Banco Latinoamericano de Comerci Extr SA $28.47
CAT Caterpillar Inc. $136.63
FAST Fastenal Company $47.91
JNJ Johnson & Johnson $140.08
LLY Eli Lilly and Co $83.51
MRK Merck & Co., Inc. $56.06
Wait. A thought. Are your array-entering the formula over a range? If you
just entered it into a single cell, you'd get what looks like "nothing",
because it would just be showing the first column and first row of my
posted range above. The one that doesn't show anything?
On Fri, Nov 3, 2017 at 5:05 PM, Allan allanhasmail@
...
wrote:
> Oh, I see. Publish to the web, CSV file... but:
>
> =smfGetCSVFile("https://docs.google.com/spreadsheets/d/e/2PACX-1vT_
> vHjLjmEtSC0SUJSOlbJKYV8dqMDob-rpdbwaf-6OjkrjHIX5il8d2gcYEXpwNyiKkGwG
> 8ef3pRh9/pub?output=csv"
> <https://docs.google.com/spreadsheets/d/e/2PACX-1vT_vHjLjmEtSC0SUJSOlbJKYV8dqMDob-rpdbwaf-6OjkrjHIX5il8d2gcYEXpwNyiKkGwG8ef3pRh9/pub?output=csv>
> )
>
> Nothing.
>
Fri Nov 3, 2017 5:55 pm (PDT) . Posted by:
"Allan" aprivoisermoi2005
Thats it. Sorry, I didn't realize you had to do it that way.
Thank you! Do you have a tip jar somewhere?
On 11/03/2017 07:26 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]
wrote:
>
>
> Wait. A thought. Are your array-entering the formula over a range? If
> you just entered it into a single cell, you'd get what looks like
> "nothing", because it would just be showing the first column and first
> row of my posted range above. The one that doesn't show anything?
>
>
Thank you! Do you have a tip jar somewhere?
On 11/03/2017 07:26 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]
wrote:
>
>
> Wait. A thought. Are your array-entering the formula over a range? If
> you just entered it into a single cell, you'd get what looks like
> "nothing"
> row of my posted range above. The one that doesn't show anything?
>
>
Fri Nov 3, 2017 4:12 pm (PDT) . Posted by:
"Yahoo!" lewglenn
Which blog? I have looked all over and I can't find the template to which you're referring. Can you please give the url?
I think the problem a lot of us are having is in specifying the fields in the arguments to smfGetPortfolioView. For example, with the old RCHGetYahooQuotes I was using the vba code segment:
'Following code extracts the previous, open, high, low, last trade, date and time of last trade,
' and 50-day SMA for each of the listings in SelStr
Sheets(MySheet).Select
Range("A2:I70") = RCHGetYahooQuotes(SelStr, "spohgl1d1t1m3", , , 0)
where SelStr = Portfolio2016 and Portfolio2016 = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO,T,TQQQ,MORL,DFT,BAX," _
& "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC,GILD,SRC,MRCC,DES,TQQQ," _
& "AHH,HDV,SPHD,XBI,EEM,UVE,KRE,VOE,GDX,SILJ,DSENX,EFA," _
& "DGRW,REML"
If I want to duplicate the results with smfGetPortfolioView, where can I find the corresponding arguments? I understand that some of them may not be currently available.
On Friday, November 3, 2017, 12:41:58 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
It's the element numbers across the top in the template mentioned on the blog, referred to by the array-entered formula.
You can also hard-code them, as in the example from the blog:
=smfGetYahooPortfolioView("MMM,IBM","010215",,1)
On Fri, Nov 3, 2017 at 12:09 PM, rho49m@... wrote:
Sorry but where do I find the variables to choose which fields to download? Looked but didn't come up with anything. Maybe a little help and example file would help the less gifted?
#yiv6802150068 #yiv6802150068 -- #yiv6802150068ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6802150068 #yiv6802150068ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6802150068 #yiv6802150068ygrp-mkp #yiv6802150068hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv6802150068 #yiv6802150068ygrp-mkp #yiv6802150068ads {margin-bottom:10px;}#yiv6802150068 #yiv6802150068ygrp-mkp .yiv6802150068ad {padding:0 0;}#yiv6802150068 #yiv6802150068ygrp-mkp .yiv6802150068ad p {margin:0;}#yiv6802150068 #yiv6802150068ygrp-mkp .yiv6802150068ad a {color:#0000ff;text-decoration:none;}#yiv6802150068 #yiv6802150068ygrp-sponsor #yiv6802150068ygrp-lc {font-family:Arial;}#yiv6802150068 #yiv6802150068ygrp-sponsor #yiv6802150068ygrp-lc #yiv6802150068hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6802150068 #yiv6802150068ygrp-sponsor #yiv6802150068ygrp-lc .yiv6802150068ad {margin-bottom:10px;padding:0 0;}#yiv6802150068 #yiv6802150068actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6802150068 #yiv6802150068activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6802150068 #yiv6802150068activity span {font-weight:700;}#yiv6802150068 #yiv6802150068activity span:first-child {text-transform:uppercase;}#yiv6802150068 #yiv6802150068activity span a {color:#5085b6;text-decoration:none;}#yiv6802150068 #yiv6802150068activity span span {color:#ff7900;}#yiv6802150068 #yiv6802150068activity span .yiv6802150068underline {text-decoration:underline;}#yiv6802150068 .yiv6802150068attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv6802150068 .yiv6802150068attach div a {text-decoration:none;}#yiv6802150068 .yiv6802150068attach img {border:none;padding-right:5px;}#yiv6802150068 .yiv6802150068attach label {display:block;margin-bottom:5px;}#yiv6802150068 .yiv6802150068attach label a {text-decoration:none;}#yiv6802150068 blockquote {margin:0 0 0 4px;}#yiv6802150068 .yiv6802150068bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv6802150068 .yiv6802150068bold a {text-decoration:none;}#yiv6802150068 dd.yiv6802150068last p a {font-family:Verdana;font-weight:700;}#yiv6802150068 dd.yiv6802150068last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6802150068 dd.yiv6802150068last p span.yiv6802150068yshortcuts {margin-right:0;}#yiv6802150068 div.yiv6802150068attach-table div div a {text-decoration:none;}#yiv6802150068 div.yiv6802150068attach-table {width:400px;}#yiv6802150068 div.yiv6802150068file-title a, #yiv6802150068 div.yiv6802150068file-title a:active, #yiv6802150068 div.yiv6802150068file-title a:hover, #yiv6802150068 div.yiv6802150068file-title a:visited {text-decoration:none;}#yiv6802150068 div.yiv6802150068photo-title a, #yiv6802150068 div.yiv6802150068photo-title a:active, #yiv6802150068 div.yiv6802150068photo-title a:hover, #yiv6802150068 div.yiv6802150068photo-title a:visited {text-decoration:none;}#yiv6802150068 div#yiv6802150068ygrp-mlmsg #yiv6802150068ygrp-msg p a span.yiv6802150068yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6802150068 .yiv6802150068green {color:#628c2a;}#yiv6802150068 .yiv6802150068MsoNormal {margin:0 0 0 0;}#yiv6802150068 o {font-size:0;}#yiv6802150068 #yiv6802150068photos div {float:left;width:72px;}#yiv6802150068 #yiv6802150068photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv6802150068 #yiv6802150068photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6802150068 #yiv6802150068reco-category {font-size:77%;}#yiv6802150068 #yiv6802150068reco-desc {font-size:77%;}#yiv6802150068 .yiv6802150068replbq {margin:4px;}#yiv6802150068 #yiv6802150068ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv6802150068 #yiv6802150068ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6802150068 #yiv6802150068ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6802150068 #yiv6802150068ygrp-mlmsg select, #yiv6802150068 input, #yiv6802150068 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv6802150068 #yiv6802150068ygrp-mlmsg pre, #yiv6802150068 code {font:115% monospace;}#yiv6802150068 #yiv6802150068ygrp-mlmsg * {line-height:1.22em;}#yiv6802150068 #yiv6802150068ygrp-mlmsg #yiv6802150068logo {padding-bottom:10px;}#yiv6802150068 #yiv6802150068ygrp-msg p a {font-family:Verdana;}#yiv6802150068 #yiv6802150068ygrp-msg p#yiv6802150068attach-count span {color:#1E66AE;font-weight:700;}#yiv6802150068 #yiv6802150068ygrp-reco #yiv6802150068reco-head {color:#ff7900;font-weight:700;}#yiv6802150068 #yiv6802150068ygrp-reco {margin-bottom:20px;padding:0px;}#yiv6802150068 #yiv6802150068ygrp-sponsor #yiv6802150068ov li a {font-size:130%;text-decoration:none;}#yiv6802150068 #yiv6802150068ygrp-sponsor #yiv6802150068ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv6802150068 #yiv6802150068ygrp-sponsor #yiv6802150068ov ul {margin:0;padding:0 0 0 8px;}#yiv6802150068 #yiv6802150068ygrp-text {font-family:Georgia;}#yiv6802150068 #yiv6802150068ygrp-text p {margin:0 0 1em 0;}#yiv6802150068 #yiv6802150068ygrp-text tt {font-size:120%;}#yiv6802150068 #yiv6802150068ygrp-vital ul li:last-child {border-right:none !important;}#yiv6802150068
I think the problem a lot of us are having is in specifying the fields in the arguments to smfGetPortfolioView. For example, with the old RCHGetYahooQuotes I was using the vba code segment:
'Following code extracts the previous, open, high, low, last trade, date and time of last trade,
' and 50-day SMA for each of the listings in SelStr
Sheets(MySheet).Select
Range("A2:I70") = RCHGetYahooQuotes(SelStr, "spohgl1d1t1m3", , , 0)
where SelStr = Portfolio2016 and Portfolio2016 = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO,T,TQQQ,MORL,DFT,BAX," _
& "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC,GILD,SRC,MRCC,DES,TQQQ," _
& "AHH,HDV,SPHD,XBI,EEM,UVE,KRE,VOE,GDX,SILJ,DSENX,EFA," _
& "DGRW,REML"
If I want to duplicate the results with smfGetPortfolioView, where can I find the corresponding arguments? I understand that some of them may not be currently available.
On Friday, November 3, 2017, 12:41:58 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
It's the element numbers across the top in the template mentioned on the blog, referred to by the array-entered formula.
You can also hard-code them, as in the example from the blog:
=smfGetYahooPortfol
On Fri, Nov 3, 2017 at 12:09 PM, rho49m@... wrote:
Sorry but where do I find the variables to choose which fields to download? Looked but didn't come up with anything. Maybe a little help and example file would help the less gifted?
#yiv6802150068 #yiv6802150068 -- #yiv6802150068ygrp-
Fri Nov 3, 2017 5:05 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The announcements blog is here:
https://smf-add-in.blogspot.com/
I created it so that such things don't get lost or buried within the
discussions on the Yahoo group. The template/example is here:
http://ogres-crypt.com/SMF/Templates/
As far as your VBA, I just used this line of code:
Range("C3:K30") = smfGetYahooPortfolioView(Range("B4:B30"),
Range("C2:K2"), , 1)
...and got something like this result (the two yellow ranges are the
function's inputs):
01 35 18 19 20 15 16 35 35
*Symbol* *--* *Open* *High* *Low* *Last Price* *Last Traded* *--* *--*
TLT TLT -- $125.47 $125.70 $125.14 $125.64 1509739201 -- --
MMM MMM -- $231.56 $232.58 $230.92 $232.22 1509739368 -- --
MCD MCD -- $168.00 $169.45 $167.60 $168.65 1509739237 -- --
SPY191220C00260000 SPY191220C00260000 -- $21.00 $21.76 $21.00 $21.76
1509730962 -- --
-- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- --
I used field "35" as a placeholder for fields not available yet --
previous close, last traded time, and 50-day SMA. The "Last Traded" column
is currently a UNIX date/time. It can be converted into an EXCEL serial
date/time with something like:
=smfUNIX2Date(I4)
Later, I'll be adding the converted last traded date and last traded time
as their own fields.
The Yahoo shutdown of the current quotes CSV file forced me to release this
function before I got everything into it that I would have liked. And now
I'm so busy looking into issues and answering questions, I don't have time
for much else. At least I have the Christmas movies from the Hallmark
channels in the background, to lighten my mood. :)
But it has been a frustrating few days. For all, I'm sure. :(
On Fri, Nov 3, 2017 at 4:11 PM, Yahoo! lewglenn@
...
wrote:
>
> Which blog? I have looked all over and I can't find the template to which
> you're referring. Can you please give the url?
>
> I think the problem a lot of us are having is in specifying the fields in
> the arguments to smfGetPortfolioView. For example, with the old
> RCHGetYahooQuotes I was using the vba code segment:
>
> 'Following code extracts the
> *previous, open, high, low, last trade, date and time of last trade,' and
> 50-day SMA* for each of the listings in SelStr
> Sheets(MySheet).Select
>
>
> Range("A2:I70") = RCHGetYahooQuotes(SelStr, "spohgl1d1t1m3", , , 0)
>
> where SelStr = Portfolio2016 and
> Portfolio2016 = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO,T,TQQQ,MORL,DFT,BAX," _
> & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC,GILD,SRC,MRCC,DES,TQQQ," _
> & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE,VOE,GDX,SILJ,DSENX,EFA," _
> & "DGRW,REML"
>
> If I want to duplicate the results with smfGetPortfolioView, where can I
> find the corresponding arguments? I understand that some of them may not be
> currently available.
>
>
>
https://smf-add-in.blogspot.com/
I created it so that such things don't get lost or buried within the
discussions on the Yahoo group. The template/example is here:
http://ogres-crypt.com/SMF/Templates/
As far as your VBA, I just used this line of code:
Range("
Range("
...and got something like this result (the two yellow ranges are the
function'
01 35 18 19 20 15 16 35 35
*Symbol* *--* *Open* *High* *Low* *Last Price* *Last Traded* *--* *--*
TLT TLT -- $125.47 $125.70 $125.14 $125.64 1509739201 -- --
MMM MMM -- $231.56 $232.58 $230.92 $232.22 1509739368 -- --
MCD MCD -- $168.00 $169.45 $167.60 $168.65 1509739237 -- --
SPY191220C00260000 SPY191220C00260000 -- $21.00 $21.76 $21.00 $21.76
1509730962 -- --
-- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- --
I used field "35" as a placeholder for fields not available yet --
previous close, last traded time, and 50-day SMA. The "Last Traded" column
is currently a UNIX date/time. It can be converted into an EXCEL serial
date/time with something like:
=smfUNIX2Date(
Later, I'll be adding the converted last traded date and last traded time
as their own fields.
The Yahoo shutdown of the current quotes CSV file forced me to release this
function before I got everything into it that I would have liked. And now
I'm so busy looking into issues and answering questions, I don't have time
for much else. At least I have the Christmas movies from the Hallmark
channels in the background, to lighten my mood. :)
But it has been a frustrating few days. For all, I'm sure. :(
On Fri, Nov 3, 2017 at 4:11 PM, Yahoo! lewglenn@
...
wrote:
>
> Which blog? I have looked all over and I can't find the template to which
> you're referring. Can you please give the url?
>
> I think the problem a lot of us are having is in specifying the fields in
> the arguments to smfGetPortfolioView
> RCHGetYahooQuotes I was using the vba code segment:
>
> 'Following code extracts the
> *previous, open, high, low, last trade, date and time of last trade,' and
> 50-day SMA* for each of the listings in SelStr
> Sheets(MySheet)
>
>
> Range("
>
> where SelStr = Portfolio2016 and
> Portfolio2016 = "TLT,TLT,
> & "BIP,EIGR,
> & "AHH,HDV,
> & "DGRW,REML&quo
>
> If I want to duplicate the results with smfGetPortfolioView
> find the corresponding arguments? I understand that some of them may not be
> currently available.
>
>
>
Fri Nov 3, 2017 5:43 pm (PDT) . Posted by:
bobobfd
Hi Randy,
any way to add "% Chg" to the parameter list?
Thanks ~ Bruce
any way to add "% Chg" to the parameter list?
Thanks ~ Bruce
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar