15 Messages
Digest #4206
Messages
Sat Nov 4, 2017 9:31 am (PDT) . Posted by:
codyklein
Where can I find documentation for smfGetGuruFocusCSVItem?
Sat Nov 4, 2017 10:11 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sorry, but for now, the only documentation is the cited example and example
workbook mentioned on the blog:
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171031.html
On Sat, Nov 4, 2017 at 9:31 AM, codyklein@
...
wrote:
> Where can I find documentation for smfGetGuruFocusCSVItem?
>
workbook mentioned on the blog:
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171031.html
On Sat, Nov 4, 2017 at 9:31 AM, codyklein@
...
wrote:
> Where can I find documentation for smfGetGuruFocusCSVI
>
Sat Nov 4, 2017 9:45 am (PDT) . Posted by:
ridgebacksexcel
Randy,
I took a good look at your new method to build a quote sheet in Google Docs and send the output via a csv file to my spreadsheet. It took a little while to figure out but I think I have the basics down. Before I go and build the data on google docs and then link it to my sheet I have a quick question.
Once I have the data built as an array formula with the tickers all in column A and the array on columns B, C, D etc..., will the data update when my spreadsheet is opened or recalculates (Ctrl-Shift-R)? Or will I have to open the Google Doc sheet first each time?
I just want to make sure this method will work much like the Yahoo Quotes function before I do all the work.
I had a quotes sheet built in an array with Yahoo Quotes and just did a vlookup to each column I wanted to access. Looking to do the same now in a Google Quotes sheet instead.
I took a good look at your new method to build a quote sheet in Google Docs and send the output via a csv file to my spreadsheet. It took a little while to figure out but I think I have the basics down. Before I go and build the data on google docs and then link it to my sheet I have a quick question.
Once I have the data built as an array formula with the tickers all in column A and the array on columns B, C, D etc..., will the data update when my spreadsheet is opened or recalculates (Ctrl-Shift-
I just want to make sure this method will work much like the Yahoo Quotes function before I do all the work.
I had a quotes sheet built in an array with Yahoo Quotes and just did a vlookup to each column I wanted to access. Looking to do the same now in a Google Quotes sheet instead.
Sat Nov 4, 2017 10:16 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
If you've got it working, you've got as much experience with it as I have.
As far as I know, the CSV file is available any time, and should update as
it is requested. smfGetCSVFile() would update the first time you open your
workbook, and additional updates would require the smfForceRecalculation
macro. You shouldn't need to open the Google Doc at all, unless you need to
change the content that is being published.
On Sat, Nov 4, 2017 at 9:45 AM, tmallen2@
...
wrote:
> I took a good look at your new method to build a quote sheet in Google
> Docs and send the output via a csv file to my spreadsheet. It took a little
> while to figure out but I think I have the basics down. Before I go and
> build the data on google docs and then link it to my sheet I have a quick
> question.
>
> Once I have the data built as an array formula with the tickers all in
> column A and the array on columns B, C, D etc..., will the data update when
> my spreadsheet is opened or recalculates (Ctrl-Shift-R)? Or will I have to
> open the Google Doc sheet first each time?
>
> I just want to make sure this method will work much like the Yahoo Quotes
> function before I do all the work.
>
> I had a quotes sheet built in an array with Yahoo Quotes and just did a
> vlookup to each column I wanted to access. Looking to do the same now in a
> Google Quotes sheet instead.
>
As far as I know, the CSV file is available any time, and should update as
it is requested. smfGetCSVFile(
workbook, and additional updates would require the smfForceRecalculati
macro. You shouldn'
change the content that is being published.
On Sat, Nov 4, 2017 at 9:45 AM, tmallen2@
...
wrote:
> I took a good look at your new method to build a quote sheet in Google
> Docs and send the output via a csv file to my spreadsheet. It took a little
> while to figure out but I think I have the basics down. Before I go and
> build the data on google docs and then link it to my sheet I have a quick
> question.
>
> Once I have the data built as an array formula with the tickers all in
> column A and the array on columns B, C, D etc..., will the data update when
> my spreadsheet is opened or recalculates (Ctrl-Shift-
> open the Google Doc sheet first each time?
>
> I just want to make sure this method will work much like the Yahoo Quotes
> function before I do all the work.
>
> I had a quotes sheet built in an array with Yahoo Quotes and just did a
> vlookup to each column I wanted to access. Looking to do the same now in a
> Google Quotes sheet instead.
>
Sat Nov 4, 2017 10:55 am (PDT) . Posted by:
ridgebacksexcel
Yes I have it working now. I was having a little trouble till I noticed the way the =smfgetcvsfile() function was spelled.
You actually have it as: =smfGetCSVFIle()
Once I type that exact way in it worked.
Thanks.
You actually have it as: =smfGetCSVFIle(
Once I type that exact way in it worked.
Thanks.
Sat Nov 4, 2017 12:02 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Odd, as upper/lower case shouldn't matter.
On Sat, Nov 4, 2017 at 10:55 AM, tmallen2@
...
wrote:
> Yes I have it working now. I was having a little trouble till I noticed
> the way the =smfgetcvsfile() function was spelled.
>
> You actually have it as: =smfGetCSVFIle()
>
> Once I type that exact way in it worked.
>
>
>
On Sat, Nov 4, 2017 at 10:55 AM, tmallen2@
...
wrote:
> Yes I have it working now. I was having a little trouble till I noticed
> the way the =smfgetcvsfile(
>
> You actually have it as: =smfGetCSVFIle(
>
> Once I type that exact way in it worked.
>
>
>
Sat Nov 4, 2017 9:59 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Hmm. That's because a hypen is one of the extraction strings. That element
comes from the title of the web page, something like:
MMM : Summary for 3M Company - Yahoo Finance
The definition extracts anything between "Summary for " and "-".
So a hypen in the company name would shorten up the extraction. I'll look
at alternatives.
The issue with EPD is that that's what is in the title on the Yahoo page.
On Sat, Nov 4, 2017 at 7:35 AM, oakapple2@
...
wrote:
> I am using RCHGetElementNumber, element 13863, to retrieve the company
> name of a ticker.
>
> It gets confused with any company that has a hyphen in its name. For
> example, SHW returns just "Sherwin", not "Sherwin-Williams". WMT returns
> just "Wal", not "Wal-Mart";. BUD returns "Anheuser";, not "Anheuser-Busch".
>
> It appears to also get confused by periods, though this is a less common
> problem. EPD returns
>
> "Enterprise Products Partners L.", missing out on the last "P."
>
> Is there any other function that converts a ticker to a name? I didn't see
> any.
>
comes from the title of the web page, something like:
MMM : Summary for 3M Company - Yahoo Finance
The definition extracts anything between "Summary for " and "-"
So a hypen in the company name would shorten up the extraction. I'll look
at alternatives.
The issue with EPD is that that's what is in the title on the Yahoo page.
On Sat, Nov 4, 2017 at 7:35 AM, oakapple2@
...
wrote:
> I am using RCHGetElementNumber
> name of a ticker.
>
> It gets confused with any company that has a hyphen in its name. For
> example, SHW returns just "Sherwin"
> just "Wal"
>
> It appears to also get confused by periods, though this is a less common
> problem. EPD returns
>
> "Enterprise Products Partners L.", missing out on the last "P."
>
> Is there any other function that converts a ticker to a name? I didn't see
> any.
>
Sat Nov 4, 2017 10:23 am (PDT) . Posted by:
"V. Shankar" tarakayan
thank you...it's working fine now.
On Saturday, November 4, 2017, 3:27:49 PM GMT+5:30, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
You may need the most recent version of the add-in.
On Sat, Nov 4, 2017 at 2:38 AM, 'V. Shankar' tarakayan@... wrote:
Version 2.1.20017.10.24; win 32 bit
On 03-Nov-2017, at 10:40 PM, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Which version of the add-in are you using?
=RCHGetElementNumber("Version" )
What are your first half-dozen tickers? Does it work OK just with them?
On Fri, Nov 3, 2017 at 9:21 AM, 'V. Shankar' tarakayan@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
The same spreadsheet with smfGetYahooPortfolioView function was working fine earlier in the day...now only for the first 2 rows, it returned all values...I have around 135 US stock symbols in this spreadsheet. anything I am missing?
On Saturday, November 4, 2017, 3:27:49 PM GMT+5:30, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
You may need the most recent version of the add-in.
On Sat, Nov 4, 2017 at 2:38 AM, 'V. Shankar' tarakayan@... wrote:
Version 2.1.20017.10.24; win 32 bit
On 03-Nov-2017, at 10:40 PM, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Which version of the add-in are you using?
=RCHGetElementNumber("Version" )
What are your first half-dozen tickers? Does it work OK just with them?
On Fri, Nov 3, 2017 at 9:21 AM, 'V. Shankar' tarakayan@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
The same spreadsheet with smfGetYahooPortfoli
Sat Nov 4, 2017 10:39 am (PDT) . Posted by:
lewglenn
So my vba code now looks like:
Range("A2:I70") = smfGetYahooPortfolioView(SelStr, "013518192015163535", , 1)
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"
and this fails. I'm using the November 2 version of the add-in. What am I still doing wrong?
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
The announcements blog is here:
https://smf-add-in.blogspot.com/ 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/ 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.
Range("A2:I70") = smfGetYahooPortfolioView(SelStr, "013518192015163535", , 1)
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"
and this fails. I'm using the November 2 version of the add-in. What am I still doing wrong?
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
The announcements blog is here:
https://smf-add-in.blogspot.com/ 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/ http://ogres-crypt.com/SMF/Templates/
As far as your VBA, I just used this line of code:
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
'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
Sat Nov 4, 2017 11:59 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
This works for me:
SelStr = "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"
Range("A2:I70") = smfGetYahooPortfolioView(SelStr, "013518192015163535", ,
1)
...although my results are different now because I've added the additional
fields. So, adding the additional fields, I have this code:
SelStr = "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"
Range("A2:H70") = smfGetYahooPortfolioView(SelStr, "0151181920158756", , 1)
...which generates something like:
*Symbol* *Prev Close* *Open* *High* *Low* *Last Price* *Last Traded
Date/Time* *50-DMA*
TLT $125.30 $125.47 $125.70 $125.14 $125.64 11/3/2017 8:00:00 PM $124.87
-- -- -- -- -- -- -- --
EDV $118.52 $118.68 $119.27 $118.47 $119.25 11/3/2017 8:00:01 PM $117.60
CVX $115.33 $115.48 $115.75 $114.73 $114.99 11/3/2017 8:04:39 PM $117.43
SON $51.65 $51.68 $52.12 $51.35 $51.91 11/3/2017 8:00:58 PM $50.90
VNQ $83.30 $82.89 $83.41 $82.53 $83.09 11/3/2017 8:00:00 PM $83.43
SBIO $30.42 $30.58 $31.08 $30.58 $31.05 11/3/2017 7:59:30 PM $31.02
T $33.17 $33.34 $33.39 $32.95 $33.30 11/3/2017 8:04:34 PM $36.76
TQQQ $129.12 $130.49 $132.87 $129.17 $132.72 11/3/2017 8:00:00 PM $119.06
The reason the second row is empty is you passed TLT twice. As is, the
function only fills in the first occurrence of a ticker symbol.
On Sat, Nov 4, 2017 at 10:39 AM, lewglenn@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> So my vba code now looks like:
>
> Range("A2:I70") = smfGetYahooPortfolioView(SelStr, "013518192015163535",
> , 1)
>
> 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"
>
> and this fails. I'm using the November 2 version of the add-in. What am I
> still doing wrong?
>
>
SelStr = "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"
Range("A2:I70") = smfGetYahooPortfolioView(SelStr, "013518192015163535", ,
1)
...although my results are different now because I've added the additional
fields. So, adding the additional fields, I have this code:
SelStr = "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"
Range("A2:H70") = smfGetYahooPortfolioView(SelStr, "0151181920158756", , 1)
...which generates something like:
*Symbol* *Prev Close* *Open* *High* *Low* *Last Price* *Last Traded
Date/Time* *50-DMA*
TLT $125.30 $125.47 $125.70 $125.14 $125.64 11/3/2017 8:00:00 PM $124.87
-- -- -- -- -- -- -- --
EDV $118.52 $118.68 $119.27 $118.47 $119.25 11/3/2017 8:00:01 PM $117.60
CVX $115.33 $115.48 $115.75 $114.73 $114.99 11/3/2017 8:04:39 PM $117.43
SON $51.65 $51.68 $52.12 $51.35 $51.91 11/3/2017 8:00:58 PM $50.90
VNQ $83.30 $82.89 $83.41 $82.53 $83.09 11/3/2017 8:00:00 PM $83.43
SBIO $30.42 $30.58 $31.08 $30.58 $31.05 11/3/2017 7:59:30 PM $31.02
T $33.17 $33.34 $33.39 $32.95 $33.30 11/3/2017 8:04:34 PM $36.76
TQQQ $129.12 $130.49 $132.87 $129.17 $132.72 11/3/2017 8:00:00 PM $119.06
The reason the second row is empty is you passed TLT twice. As is, the
function only fills in the first occurrence of a ticker symbol.
On Sat, Nov 4, 2017 at 10:39 AM, lewglenn@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> So my vba code now looks like:
>
> Range("
> , 1)
>
> where SelStr = Portfolio2016 and
> Portfolio2016 = "TLT,TLT,
> & "BIP,EIGR,
> & "AHH,HDV,
> & "DGRW,REML&quo
>
> and this fails. I'm using the November 2 version of the add-in. What am I
> still doing wrong?
>
>
Sat Nov 4, 2017 11:22 am (PDT) . Posted by:
palminha
Dear Randy,
I use a lot in several files formulas like this =DATEVALUE(RCHGetYahooQuotes(Ticker,"d1")) and now that can´t be used anymore, what formula do you suggest to replace it?
I also like to have one alternative to replace =IF($B5=D$2,"--",RCHGetYahooQuotes($B5&D$2&"=X","l1")) used to have exchange rates.
Those are my major problems for now!
Thank you for you kind support and efforts to overcome this sad stop from Yahoo Finance.
Kind Regards
Paulo
I use a lot in several files formulas like this =DATEVALUE(RCHGetYa
I also like to have one alternative to replace =IF($B5=D$2,
Those are my major problems for now!
Thank you for you kind support and efforts to overcome this sad stop from Yahoo Finance.
Kind Regards
Paulo
Sat Nov 4, 2017 12:25 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Right now, the solution for that is:
=INT(smfUNIX2Date(0+smfGetYahooPortfolioView("SPY","16")))
...but in the updated version of the add-in I'm running, with new fields
added to the function, is a much simpler:
=smfGetYahooPortfolioView("SPY","78")
However, I would prefer people minimize usage of the function by doing
array-entered ranges to get all the data they need at once, instead of
using individual cell invocations.
My version also has fixes for currencies, so:
=smfGetYahooPortfolioView("CADUSD=X,CADGBP=X,CADAUD=X","0115",,1)
...now returns:
*Symbol* *Last Price*
CADUSD=X 0.7838219
CADGBP=X 0.5994513
CADAUD=X 1.0232011
-- --
On Sat, Nov 4, 2017 at 11:22 AM, palminha@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I use a lot in several files formulas like this
> =DATEVALUE(RCHGetYahooQuotes(Ticker,"d1")) and now that can´t be used
> anymore, what formula do you suggest to replace it?
>
> I also like to have one alternative to replace =IF($B5=D$2,"--",
> RCHGetYahooQuotes($B5&D$2&"=X","l1")) used to have exchange rates.
>
> Those are my major problems for now!
>
> Thank you for you kind support and efforts to overcome this sad stop from
> Yahoo Finance.
>
>
=INT(smfUNIX2Date(0+smfGetYahooPortfolioView("SPY","16")))
...but in the updated version of the add-in I'm running, with new fields
added to the function, is a much simpler:
=smfGetYahooPortfolioView("SPY","78")
However, I would prefer people minimize usage of the function by doing
array-entered ranges to get all the data they need at once, instead of
using individual cell invocations.
My version also has fixes for currencies, so:
=smfGetYahooPortfolioView("CADUSD=X,CADGBP=X,CADAUD=X","0115",,1)
...now returns:
*Symbol* *Last Price*
CADUSD=X 0.7838219
CADGBP=X 0.5994513
CADAUD=X 1.0232011
-- --
On Sat, Nov 4, 2017 at 11:22 AM, palminha@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I use a lot in several files formulas like this
> =DATEVALUE(RCHGetYa
> anymore, what formula do you suggest to replace it?
>
> I also like to have one alternative to replace =IF($B5=D$2,
> RCHGetYahooQuotes(
>
> Those are my major problems for now!
>
> Thank you for you kind support and efforts to overcome this sad stop from
> Yahoo Finance.
>
>
Sat Nov 4, 2017 12:06 pm (PDT) . Posted by:
"Pieter van Leeuwen" pietertvl
Hi Randy and anyone else who has succeeded all the way through.
Got my Google Docs s/s setup. Got it linked/exported into Excel xlsx
format. (Assuming that refresh will work Monday)
I need data ultimately in txt, but csv seems like the way to get it there.
Loaded your SMF addins. Tested it as suggested (via "Version"). Reports
back fine.
From your blog .....
I used the method to publish a CSV file of quotes data for the S&P 500
stocks. To get it into EXCEL, all that is needed is:
=smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRob
B_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?outp
ut=csv")
My Google Docs URL does not have any of this text at the back end ...
/pub?output=csv
So right now, the cell with this published content URL reports ERROR.
Did I miss a step along the way?
Also, I can't be sure, but is it CSVFile or CSVFIIe ? with an i or
two ll's ? Tried both ways, and get ERROR either way.
Thanks for the pointers!!!
pedro deleon
Got my Google Docs s/s setup. Got it linked/exported into Excel xlsx
format. (Assuming that refresh will work Monday)
I need data ultimately in txt, but csv seems like the way to get it there.
Loaded your SMF addins. Tested it as suggested (via "Version"). Reports
back fine.
From your blog .....
I used the method to publish a CSV file of quotes data for the S&P 500
stocks. To get it into EXCEL, all that is needed is:
=smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRob
B_tnWo-tiVZ9J8s74RE
ut=csv"
My Google Docs URL does not have any of this text at the back end ...
/pub?output=
So right now, the cell with this published content URL reports ERROR.
Did I miss a step along the way?
Also, I can't be sure, but is it CSVFile or CSVFIIe ? with an i or
two ll's ? Tried both ways, and get ERROR either way.
Thanks for the pointers!!!
pedro deleon
Sat Nov 4, 2017 12:12 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
If you're missing the "*/pub?output=csv*" string at the end of the URL, you
probably missed the step to publish your Google sheet. The "/pub" indicates
it's a published document and "output=csv" indicates it was published as a
CSV file.
Upper/Lower case on function names shouldn't make a difference. I see a lot
of people citing examples in all lower case.
On Sat, Nov 4, 2017 at 12:01 PM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
>
> Hi Randy and anyone else who has succeeded all the way through.
>
>
>
> Got my Google Docs s/s setup. Got it linked/exported into Excel xlsx
> format. (Assuming that refresh will work Monday)
>
>
>
> I need data ultimately in txt, but csv seems like the way to get it there.
>
>
>
> Loaded your SMF addins. Tested it as suggested (via "Version").
> Reports back fine.
>
>
>
> From your blog .....
>
>
>
>
> *I used the method to publish a CSV file of quotes data for the S&P 500
> stocks. To get it into EXCEL, all that is needed is: *
> *
> =smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRobB_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?output=csv
> <https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRobB_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?output=csv>")*
>
>
>
> My Google Docs URL does not have any of this text at the back end
> ... */pub?output=csv*
>
> So right now, the cell with this published content URL reports ERROR.
>
>
>
> Did I miss a step along the way?
>
>
>
> Also, I can't be sure, but is it CSVFile or CSVFIIe ? with an i or
> two ll's ? Tried both ways, and get ERROR either way.
>
>
>
>
>
probably missed the step to publish your Google sheet. The "/pub" indicates
it's a published document and "output=csv" indicates it was published as a
CSV file.
Upper/Lower case on function names shouldn't make a difference. I see a lot
of people citing examples in all lower case.
On Sat, Nov 4, 2017 at 12:01 PM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
>
> Hi Randy and anyone else who has succeeded all the way through.
>
>
>
> Got my Google Docs s/s setup. Got it linked/exported into Excel xlsx
> format. (Assuming that refresh will work Monday)
>
>
>
> I need data ultimately in txt, but csv seems like the way to get it there.
>
>
>
> Loaded your SMF addins. Tested it as suggested (via "Version").
> Reports back fine.
>
>
>
> From your blog .....
>
>
>
>
> *I used the method to publish a CSV file of quotes data for the S&P 500
> stocks. To get it into EXCEL, all that is needed is: *
> *
> =smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRobB_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?output=csv
> <https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRobB_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?output=csv>")*
>
>
>
> My Google Docs URL does not have any of this text at the back end
> ... */pub?output=
>
> So right now, the cell with this published content URL reports ERROR.
>
>
>
> Did I miss a step along the way?
>
>
>
> Also, I can't be sure, but is it CSVFile or CSVFIIe ? with an i or
> two ll's ? Tried both ways, and get ERROR either way.
>
>
>
>
>
Sat Nov 4, 2017 12:09 pm (PDT) . Posted by:
pietertvl
Hi Randy
Caught your blog update and proceeded through all the steps.
got the SMF addins into Excel, and tested it as suggested. It reported back properly.
So far so good, up until this step below. From your blog ...
I used the method to publish a CSV file of quotes data for the S&P 500 stocks. To get it into EXCEL, all that is needed is:
=smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRobB_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?output=csv")
I have the Google Docs file currently published to an xlxs file, not a csv file.
My URL does not have the /pub?output=csv series at the end. What step am I missing? THANKS for all the pointers.
PS - Paulo ... if you are reading this, see Randy's blog. Yahoo is dead; go to GoogleDocs.
Caught your blog update and proceeded through all the steps.
got the SMF addins into Excel, and tested it as suggested. It reported back properly.
So far so good, up until this step below. From your blog ...
I used the method to publish a CSV file of quotes data for the S&P 500 stocks. To get it into EXCEL, all that is needed is:
=smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRobB_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?output=csv")
I have the Google Docs file currently published to an xlxs file, not a csv file.
My URL does not have the /pub?output=
PS - Paulo ... if you are reading this, see Randy's blog. Yahoo is dead; go to GoogleDocs.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar