11 Messages
Digest #3978
5b
Re: Stopped Getting Yahoo Historical Data for DOW and S&P 500 recent by "Randy Harmelink" rharmelink
Messages
Fri Apr 21, 2017 1:11 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
For #13842, I changed the URL and the first search term, so this appears to
work:
=smfConvertData(smfStrExtr(smfGetTagContent("
http://c.finance.a1.b.yahoo.com/advances","td",3,"<em>Advances",">Advancing
Issues"),"~","("))
On Fri, Apr 21, 2017 at 12:54 PM, halstian1@
...wrote:
>
> Would it be possible to provide a work around even for this site? I'll
> take as much time as I can get
>
>
work:
=smfConvertData(smfStrExtr(smfGetTagContent("
http://c.finance.a1.b.yahoo.com/advances","
Issues"
On Fri, Apr 21, 2017 at 12:54 PM, halstian1@
...wrote:
>
> Would it be possible to provide a work around even for this site? I'll
> take as much time as I can get
>
>
Fri Apr 21, 2017 1:24 pm (PDT) . Posted by:
nmalhotr
=smfGetOptionQuotes("GOGO 1/19 2017 $10 PUT","b")
=smfGetOptionQuotes("EBAY 4/21 2017 $31 Call","b")
Your MMM works but many others don't. These are two examples that are not. \
=smfGetOptionQuotes
Your MMM works but many others don't. These are two examples that are not. \
Fri Apr 21, 2017 2:02 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The first one has an old expiration date on it, so no surprise it doesn't
work. The second works for me, returning $1.08, which is what I see online.
This does work for me for GOGO:
=smfGetOptionQuotes("GOGO 1/19 2018 $10 PUT","b")
...returning $1.00.
On Fri, Apr 21, 2017 at 1:24 PM, namit98@
...wrote:
>
> =
>
> smfGetOptionQuotes("GOGO 1/19 2017 $10 PUT","b")
>
> =smfGetOptionQuotes("EBAY 4/21 2017 $31 Call","b")
> Your MMM works but many others don't. These are two examples that are
> not. \
>
>
work. The second works for me, returning $1.08, which is what I see online.
This does work for me for GOGO:
=smfGetOptionQuotes
...returning $1.00.
On Fri, Apr 21, 2017 at 1:24 PM, namit98@
...wrote:
>
> =
>
> smfGetOptionQuotes(
>
> =smfGetOptionQuotes
> Your MMM works but many others don't. These are two examples that are
> not. \
>
>
Fri Apr 21, 2017 1:53 pm (PDT) . Posted by:
norton1717
Randy,
How does the one work which uses this link - http://finance.yahoo.com/quote/~~~~~/purchase-info yield
I was able to start building out a table with data items of interest from some of the modules. I will share with you when I have a more comprehensive list.
Thanks
How does the one work which uses this link - http://finance.yahoo.com/quote/~~~~~/purchase-info yield
I was able to start building out a table with data items of interest from some of the modules. I will share with you when I have a more comprehensive list.
Thanks
Fri Apr 21, 2017 2:12 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The ONLY difference is that it uses that URL instead of creating the normal
module URL, substituting the ticker symbol for the "~~~~~" string in the
URL.
That's because the JSON table is actually encoded within the source code of
that web page.
I think I have picked up everything from a "Source" of YahooPI and YahooPR.
But many had no value, so I'm not sure if they WOULD have a value with
another ticker symbol. That may present a problem?
That's why I haven't gone in general release. I don't do much with mutual
funds, so I'm not aware of all the nuances that might pop up. For example,
I typically use VFINX, but it had no bond holdings and didn't have a value
for "Duration". That was what got me to look for the closing "}" value, so
it didn't get something from the next variable.
On Fri, Apr 21, 2017 at 1:53 PM, rnorton@
...wrote:
>
> How does the one work which uses this link - http://finance.yahoo.com/
> quote/~~~~~/purchase-info yield
>
> I was able to start building out a table with data items of interest from
> some of the modules. I will share with you when I have a more
> comprehensive list.
>
>
module URL, substituting the ticker symbol for the "~~~~~" string in the
URL.
That's because the JSON table is actually encoded within the source code of
that web page.
I think I have picked up everything from a "Source" of YahooPI and YahooPR.
But many had no value, so I'm not sure if they WOULD have a value with
another ticker symbol. That may present a problem?
That's why I haven't gone in general release. I don't do much with mutual
funds, so I'm not aware of all the nuances that might pop up. For example,
I typically use VFINX, but it had no bond holdings and didn't have a value
for "Duration". That was what got me to look for the closing "}" value, so
it didn't get something from the next variable.
On Fri, Apr 21, 2017 at 1:53 PM, rnorton@
...wrote:
>
> How does the one work which uses this link - http://finance.yahoo.com/
> quote/~~~~~/
>
> I was able to start building out a table with data items of interest from
> some of the modules. I will share with you when I have a more
> comprehensive list.
>
>
Fri Apr 21, 2017 6:33 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm trying to work through the "fundPerformance" module. Because of the
structure, I had a need to change the function, so I could do qualified
field names such as:
=smfGetYahooJSONData(G$10,"fundPerformance","annualTotalReturns.returns.year",8,"")
=smfGetYahooJSONData(G$10,"fundPerformance","annualTotalReturns.returnsCat.annualValue",8)
=smfGetYahooJSONData(G$10,"fundPerformance","pastQuarterlyReturns.year",16,"")
=smfGetYahooJSONData(G$10,"fundPerformance","pastQuarterlyReturns.q1",16)
One thing I found helpful is to use the SMF-Quick-Webpage-Examination.xls
template. If you go to the :"By Cascading Value" sheet, you can enter:
D7:
https://query1.finance.yahoo.com/v10/finance/quoteSummary/VWEAX?modules=fundPerformance
D8: },"
...and it gives a good idea of the variables available. Not all, but most.
On Fri, Apr 21, 2017 at 1:53 PM, rnorton@
...wrote:
>
> How does the one work which uses this link - http://finance.yahoo.com/
> quote/~~~~~/purchase-info yield
>
> I was able to start building out a table with data items of interest from
> some of the modules. I will share with you when I have a more
> comprehensive list.
>
>
>
structure, I had a need to change the function, so I could do qualified
field names such as:
=smfGetYahooJSONData(G$10,"fundPerformance","annualTotalReturns.returns.year",8,"")
=smfGetYahooJSONData(G$10,"fundPerformance","annualTotalReturns.returnsCat.annualValue",8)
=smfGetYahooJSONData(G$10,"fundPerformance","pastQuarterlyReturns.year",16,"")
=smfGetYahooJSONData(G$10,"fundPerformance","pastQuarterlyReturns.q1",16)
One thing I found helpful is to use the SMF-Quick-Webpage-Examination.xls
template. If you go to the :"By Cascading Value" sheet, you can enter:
D7:
https://query1.finance.yahoo.com/v10/finance/quoteSummary/VWEAX?modules=fundPerformance
D8: },"
...and it gives a good idea of the variables available. Not all, but most.
On Fri, Apr 21, 2017 at 1:53 PM, rnorton@
...wrote:
>
> How does the one work which uses this link - http://finance.yahoo.com/
> quote/~~~~~/
>
> I was able to start building out a table with data items of interest from
> some of the modules. I will share with you when I have a more
> comprehensive list.
>
>
>
Fri Apr 21, 2017 1:57 pm (PDT) . Posted by:
azwarlord
RCHGetElementNumber 5059 no longer works. Can someone assist?
Fri Apr 21, 2017 2:23 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The workaround until the Yahoo Mutual Fund element numbers can be redefined
is:
=smfConvertData(smfStrExtr(smfWord(RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
"&F$3&"?modules=fundProfile";),2,"""categoryName"":"),"""",""""))
On Fri, Apr 21, 2017 at 1:52 PM, azwarlord@
...wrote:
>
> RCHGetElementNumber 5059 no longer works. Can someone assist?
>
is:
=smfConvertData(smfStrExtr(smfWord(RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
"&F$3&"
On Fri, Apr 21, 2017 at 1:52 PM, azwarlord@
...wrote:
>
> RCHGetElementNumber 5059 no longer works. Can someone assist?
>
Fri Apr 21, 2017 5:29 pm (PDT) . Posted by:
eburgos@comcast.net
Hi,
My portfolio spreadsheet was setup with the following dates to measure my portfolio performance against DOW and S&P500.
First date was Start Date for my portfolio Setup which was on 12/31/2015.
Second date is Current Year Start date which is currently 1/1/2017
Third date is 30 days ago which is currently 3/22/2017.
3 arrays existed for the 3 different dates for DOW and 3 arrays were setup for the three different dates for S&P500. That how I had 6 total arrays.
I got this spreadsheet from users that shared it on Motley Fool Options site. The spreadsheet was last maintained on March of 2015. Since then I have had to make several corrections. I have attached a copy of my file. Removed all but a few financial bits of data so you can see how the data looks.
The "Formulas"; tab will show the arrays I used.
Regards,
Ernie
My portfolio spreadsheet was setup with the following dates to measure my portfolio performance against DOW and S&P500.
First date was Start Date for my portfolio Setup which was on 12/31/2015.
Second date is Current Year Start date which is currently 1/1/2017
Third date is 30 days ago which is currently 3/22/2017.
3 arrays existed for the 3 different dates for DOW and 3 arrays were setup for the three different dates for S&P500. That how I had 6 total arrays.
I got this spreadsheet from users that shared it on Motley Fool Options site. The spreadsheet was last maintained on March of 2015. Since then I have had to make several corrections. I have attached a copy of my file. Removed all but a few financial bits of data so you can see how the data looks.
The "Formulas"
Regards,
Ernie
Attachment(s) from
1 of 1 File(s)
Fri Apr 21, 2017 6:34 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The changes I would make to the "Details" spreadsheet:
1. Get rid of TODAY(). That makes any function that refers to it volatile,
which means they recalculate every time something changes in the workbook.
I just use the last trading date of SPY, as in:
=Datevalue(RCHGetYahooQuotes("SPY","d1"))
2. Use smfPricesByDates() to get the adjusted closing prices for your
dates. You could use two functions to get the three prices for each index.
3. Use "SPY" and "DIA" instead of "^GSPC" and "^DJI", because the ETFs will
include dividend gains that holding the indexes would have earned. Not so
important in the short-term.
Ticker 2015-12-31 2016-12-31 2017-03-22 2017-04-21 Total Current Year Past
30 Days
^GSPC $2,043.94 $2,238.83 $2,348.45 $2,348.69 14.91% 4.91% 0.01%
SPY $198.72 $222.56 $234.28 $234.59 18.05% 5.40% 0.13%
^DJI $17,425.03 $19,762.60 $20,661.30 $20,547.76 17.92% 3.97% -0.55%
DIA $168.77 $196.40 $206.41 $205.25 21.62% 4.51% -0.56%
P.S. I cringe when I see so many worksheets. :)
On Fri, Apr 21, 2017 at 5:29 PM,
Ernie@... wrote:
>
> My portfolio spreadsheet was setup with the following dates to measure my
> portfolio performance against DOW and S&P500.
>
> First date was Start Date for my portfolio Setup which was on 12/31/2015.
>
> Second date is Current Year Start date which is currently 1/1/2017
>
> Third date is 30 days ago which is currently 3/22/2017.
>
>
> 3 arrays existed for the 3 different dates for DOW and 3 arrays were setup
> for the three different dates for S&P500. That how I had 6 total arrays.
>
> I got this spreadsheet from users that shared it on Motley Fool Options
> site. The spreadsheet was last maintained on March of 2015. Since then I
> have had to make several corrections. I have attached a copy of my file.
> Removed all but a few financial bits of data so you can see how the data
> looks.
>
> The "Formulas"; tab will show the arrays I used.
>
>
1. Get rid of TODAY(). That makes any function that refers to it volatile,
which means they recalculate every time something changes in the workbook.
I just use the last trading date of SPY, as in:
=Datevalue(RCHGetYa
2. Use smfPricesByDates(
dates. You could use two functions to get the three prices for each index.
3. Use "SPY" and "DIA" instead of "^GSPC" and "^DJI"
include dividend gains that holding the indexes would have earned. Not so
important in the short-term.
Ticker 2015-12-31 2016-12-31 2017-03-22 2017-04-21 Total Current Year Past
30 Days
^GSPC $2,043.94 $2,238.83 $2,348.45 $2,348.69 14.91% 4.91% 0.01%
SPY $198.72 $222.56 $234.28 $234.59 18.05% 5.40% 0.13%
^DJI $17,425.03 $19,762.60 $20,661.30 $20,547.76 17.92% 3.97% -0.55%
DIA $168.77 $196.40 $206.41 $205.25 21.62% 4.51% -0.56%
P.S. I cringe when I see so many worksheets. :)
On Fri, Apr 21, 2017 at 5:29 PM,
Ernie@... wrote:
>
> My portfolio spreadsheet was setup with the following dates to measure my
> portfolio performance against DOW and S&P500.
>
> First date was Start Date for my portfolio Setup which was on 12/31/2015.
>
> Second date is Current Year Start date which is currently 1/1/2017
>
> Third date is 30 days ago which is currently 3/22/2017.
>
>
> 3 arrays existed for the 3 different dates for DOW and 3 arrays were setup
> for the three different dates for S&P500. That how I had 6 total arrays.
>
> I got this spreadsheet from users that shared it on Motley Fool Options
> site. The spreadsheet was last maintained on March of 2015. Since then I
> have had to make several corrections. I have attached a copy of my file.
> Removed all but a few financial bits of data so you can see how the data
> looks.
>
> The "Formulas"
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar