10 Messages
Digest #4010
Messages
Wed May 17, 2017 9:56 am (PDT) . Posted by:
eadamy
Once the page is pulled up, one can hover over the Download button and right click Inspect to view the HTML query (complete with crumb) which could presumably be used to retrieve the data w/o going to a CSV file. Getting to it is the challenge and the two step process would severely slow data retrieval.
Earl Adamy
Earl Adamy
Wed May 17, 2017 11:18 am (PDT) . Posted by:
rr76012
Hi Earl,
I hope you and any of your friends, can fix this, the downloading of yahoo historical stock prices to excel...
rr76012
I hope you and any of your friends, can fix this, the downloading of yahoo historical stock prices to excel...
rr76012
Wed May 17, 2017 4:14 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
It appears the web page that displays the Yahoo historical quotes and has
the download link also has all of the requested quotes in JSON format
within the source code of the web page. I've been able to extract them
manually, but am now considering transferring that processing to a new
function, smfGetYahooHistory().
I'm thinking the function syntax would be something like:
=smfGetYahooHistory(Ticker, [Start Date], [End Date], [Period], [Data
Items], [Header], [Resort])
Basically, combining the date items into a single date parameters and
dropping the adjust parameter. The Yahoo data appears to be adjusted for
splits only, with an unadjusted price carried along. I would like to adjust
for dividends, but will have to see how it goes. I may carry three
additional items -- split adjustment factor, dividend adjustment factor,
overall adjustment factor.
The periods available right now are daily data, weekly data, monthly data,
dividends only, splits only. I could add quarterly and annual data, by just
using every 3rd or 12th monthly item. But applying dividend adjustments may
require processing data at a daily level, so extractions of anything other
than daily data might be best done by smfPricesByDates(). That function
would allow custom definitions of weeks, months, quarters, and years,
whereas Yahoo defines them by Fridays for weekly data and the first of the
month for monthly data.
Thoughts? Needs? Things that would be nice to have?
I'm also juggling this with JSON extraction work for Yahoo financial
statements and Yahoo mutual fund data. I have no idea how other items might
be affected -- bonds, CEFs, ETFs,etc.
the download link also has all of the requested quotes in JSON format
within the source code of the web page. I've been able to extract them
manually, but am now considering transferring that processing to a new
function, smfGetYahooHistory(
I'm thinking the function syntax would be something like:
=smfGetYahooHistory
Items], [Header], [Resort])
Basically, combining the date items into a single date parameters and
dropping the adjust parameter. The Yahoo data appears to be adjusted for
splits only, with an unadjusted price carried along. I would like to adjust
for dividends, but will have to see how it goes. I may carry three
additional items -- split adjustment factor, dividend adjustment factor,
overall adjustment factor.
The periods available right now are daily data, weekly data, monthly data,
dividends only, splits only. I could add quarterly and annual data, by just
using every 3rd or 12th monthly item. But applying dividend adjustments may
require processing data at a daily level, so extractions of anything other
than daily data might be best done by smfPricesByDates(
would allow custom definitions of weeks, months, quarters, and years,
whereas Yahoo defines them by Fridays for weekly data and the first of the
month for monthly data.
Thoughts? Needs? Things that would be nice to have?
I'm also juggling this with JSON extraction work for Yahoo financial
statements and Yahoo mutual fund data. I have no idea how other items might
be affected -- bonds, CEFs, ETFs,etc.
Wed May 17, 2017 5:05 pm (PDT) . Posted by:
eadamy
A quick look at the page and the source shows that all 5 price columns, including adjusted close, plus volume are carried in the source terminated with the "loading more data". Y says these are carried in the daily, weekly, and monthly data.
The loading more seems to present a challenge in streaming the data. My Chrome just sits there is probably due to the ad blocker. Firefox, which I use occasionally, has no ad blocker installed but I note that it streams a steady mix of ads mixed in with the additional data. Parsing all of this appears to be doable at the price of considerable processing overhead.
The dividend adjustment for SPY on March 17 and December 16 appears to be reflected in the adjusted close on both the page and the source so I'm not sure why it would be necessary to dynamically calculate adjustments other than for adjusted intraday O/H/L.
Aside from the technical considerations, I think it is important to consider Y's goals in making these changes. It appears to me that Y is willing to continue providing historical and other data; however they intend to force users to consume a good deal more advertising. The recent actions by Y have thrown up the major roadblock. I see what will amount to a continuous rear-guard action which will be more successful than not in warding off the automated downloaders.
I had wondered why Y did not simply capture the download business they already had with a cheap download access option i/o chasing that business away. After re-reading their ToS, I concluded that their data vendors would not allow them enter the resale business w/o giving up access to the data for their heavily used financial web site.
While I'm not suggesting that it's not worth a try, I do think that the message is clear that those who want bulk data will need to look elsewhere. One man's opinion.
Earl
The loading more seems to present a challenge in streaming the data. My Chrome just sits there is probably due to the ad blocker. Firefox, which I use occasionally, has no ad blocker installed but I note that it streams a steady mix of ads mixed in with the additional data. Parsing all of this appears to be doable at the price of considerable processing overhead.
The dividend adjustment for SPY on March 17 and December 16 appears to be reflected in the adjusted close on both the page and the source so I'm not sure why it would be necessary to dynamically calculate adjustments other than for adjusted intraday O/H/L.
Aside from the technical considerations, I think it is important to consider Y's goals in making these changes. It appears to me that Y is willing to continue providing historical and other data; however they intend to force users to consume a good deal more advertising. The recent actions by Y have thrown up the major roadblock. I see what will amount to a continuous rear-guard action which will be more successful than not in warding off the automated downloaders.
I had wondered why Y did not simply capture the download business they already had with a cheap download access option i/o chasing that business away. After re-reading their ToS, I concluded that their data vendors would not allow them enter the resale business w/o giving up access to the data for their heavily used financial web site.
While I'm not suggesting that it's not worth a try, I do think that the message is clear that those who want bulk data will need to look elsewhere. One man's opinion.
Earl
Wed May 17, 2017 5:44 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Wed, May 17, 2017 at 5:05 PM, earladamy@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> A quick look at the page and the source shows that all 5 price columns,
> including adjusted close, plus volume are carried in the source terminated
> with the "loading more data". Y says these are carried in the daily,
> weekly, and monthly data.
>
Yes, but the table only contains 100 rows when delivered. Additional days
are supposed to be loaded as you scroll (which doesn't seem to work right,
BTW). That 100-row table can easily be loaded into EXCEL. For example:
=RCHGetHTMLTable("https://finance.yahoo.com/quote/SPY/history",">Volume")
> The loading more seems to present a challenge in streaming the data. My
> Chrome just sits there is probably due to the ad blocker. Firefox, which I
> use occasionally, has no ad blocker installed but I note that it streams a
> steady mix of ads mixed in with the additional data. Parsing all of this
> appears to be doable at the price of considerable processing overhead.
>
That may be why I don't see more data in the table when I scroll.
> The dividend adjustment for SPY on March 17 and December 16 appears to be
> reflected in the adjusted close on both the page and the source so I'm not
> sure why it would be necessary to dynamically calculate adjustments other
> than for adjusted intraday O/H/L.
>
I don't see that at all.
*Date* *Open* *High* *Low* *Close* *Adj Close** *Volume*
Mar 17, 2017 237.75 237.97 237.03 237.03 237.03 89002100
Mar 17, 2017 1.033 Dividend
Mar 16, 2017 239.11 239.2 238.1 238.48 238.48 78344000
If the prices were adjusted for dividends, the Mar 16 "Close" and "Adj
Close" should be different from each other, accounting for the $1.033
dividend. The JSON code within the web page for those three days looks like:
{"date":1489757400,"open":237.75,"high":237.97000122070312,"low":237.02999877929688,"close":237.02999877929688,"volume":89002100,"unadjclose":237.02999877929688},
{"amount":1.033,"date":1489757400,"type":"DIVIDEND","data":1.033},
{"date":1489671000,"open":239.11000061035156,"high":239.1999969482422,"low":238.10000610351562,"close":238.47999572753906,"volume":78344000,"unadjclose":238.47999572753906}
I do find it odd that the table display has "Close" and "Adj Close" while
the JSON data has "close" and "unadjclose".
Aside from the technical considerations, I think it is important to
> consider Y's goals in making these changes. It appears to me that Y is
> willing to continue providing historical and other data; however they
> intend to force users to consume a good deal more advertising. The
> recent actions by Y have thrown up the major roadblock. I see what will
> amount to a continuous rear-guard action which will be more successful than
> not in warding off the automated downloaders.
>
> I had wondered why Y did not simply capture the download business they
> already had with a cheap download access option i/o chasing that business
> away. After re-reading their ToS, I concluded that their data vendors would
> not allow them enter the resale business w/o giving up access to the data
> for their heavily used financial web site.
>
> While I'm not suggesting that it's not worth a try, I do think that the
> message is clear that those who want bulk data will need to look elsewhere.
> One man's opinion.
>
smf_addin@yahoogroups.com> wrote:
>
> A quick look at the page and the source shows that all 5 price columns,
> including adjusted close, plus volume are carried in the source terminated
> with the "loading more data". Y says these are carried in the daily,
> weekly, and monthly data.
>
Yes, but the table only contains 100 rows when delivered. Additional days
are supposed to be loaded as you scroll (which doesn't seem to work right,
BTW). That 100-row table can easily be loaded into EXCEL. For example:
=RCHGetHTMLTable("https://finance.yahoo.com/quote/SPY/history","
> The loading more seems to present a challenge in streaming the data. My
> Chrome just sits there is probably due to the ad blocker. Firefox, which I
> use occasionally, has no ad blocker installed but I note that it streams a
> steady mix of ads mixed in with the additional data. Parsing all of this
> appears to be doable at the price of considerable processing overhead.
>
That may be why I don't see more data in the table when I scroll.
> The dividend adjustment for SPY on March 17 and December 16 appears to be
> reflected in the adjusted close on both the page and the source so I'm not
> sure why it would be necessary to dynamically calculate adjustments other
> than for adjusted intraday O/H/L.
>
I don't see that at all.
*Date* *Open* *High* *Low* *Close* *Adj Close** *Volume*
Mar 17, 2017 237.75 237.97 237.03 237.03 237.03 89002100
Mar 17, 2017 1.033 Dividend
Mar 16, 2017 239.11 239.2 238.1 238.48 238.48 78344000
If the prices were adjusted for dividends, the Mar 16 "Close" and "Adj
Close" should be different from each other, accounting for the $1.033
dividend. The JSON code within the web page for those three days looks like:
{"date"
{"amount"
{"date"
I do find it odd that the table display has "Close" and "Adj Close" while
the JSON data has "close" and "unadjclose&qu
Aside from the technical considerations, I think it is important to
> consider Y's goals in making these changes. It appears to me that Y is
> willing to continue providing historical and other data; however they
> intend to force users to consume a good deal more advertising. The
> recent actions by Y have thrown up the major roadblock. I see what will
> amount to a continuous rear-guard action which will be more successful than
> not in warding off the automated downloaders.
>
> I had wondered why Y did not simply capture the download business they
> already had with a cheap download access option i/o chasing that business
> away. After re-reading their ToS, I concluded that their data vendors would
> not allow them enter the resale business w/o giving up access to the data
> for their heavily used financial web site.
>
> While I'm not suggesting that it's not worth a try, I do think that the
> message is clear that those who want bulk data will need to look elsewhere.
> One man's opinion.
>
Wed May 17, 2017 5:48 pm (PDT) . Posted by:
allen.stowe@rogers.com
Hi Randy;
I am not all that sophisticated but use a few of the functions you have available in the [data item].
Two such examples of Yahoo history are below where A3 is the stock symbol.
{RCHGetYahooHistory(A3,,,,,,,"v",,,,,)} to pull a 10 year dividend payment history for charting.
{RCHGetYahooHistory(A3,,,,,,,,"dlhc",,,1)} which I use to create myself pivot point & resistance charting plot.
Hope that helps for feedback on how I use some of the data.
I am not all that sophisticated but use a few of the functions you have available in the [data item].
Two such examples of Yahoo history are below where A3 is the stock symbol.
{RCHGetYahooHistory
{RCHGetYahooHistory
Hope that helps for feedback on how I use some of the data.
Wed May 17, 2017 5:54 pm (PDT) . Posted by:
bizmark_ee
FYI -- the may be working on fixing the div adjustment problem:
https://yahoo.uservoice.com/forums/382977-finance/suggestions/18220969--closing-price-adjusted-closing-price-are-the-sa https://yahoo.uservoice.com/forums/382977-finance/suggestions/18220969--closing-price-adjusted-closing-price-are-the-sa
https://yahoo.uservoice.com/forums/382977-finance/suggestions/18220969--closing-price-adjusted-closing-price-are-the-sa https://yahoo.uservoice.com/forums/382977-finance/suggestions/18220969--closing-price-adjusted-closing-price-are-the-sa
Wed May 17, 2017 5:59 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Not sure about "working on" -- that topic has been around since last year...
But I have been considering whether I should do something or not. Assuming
they MIGHT do something eventually?
On Wed, May 17, 2017 at 5:54 PM, dustinsmoak@
...wrote:
>
> FYI -- the may be working on fixing the div adjustment problem:
>
> https://yahoo.uservoice.com/forums/382977-finance/
> suggestions/18220969--closing-price-adjusted-closing-price-are-the-sa
>
> __
>
But I have been considering whether I should do something or not. Assuming
they MIGHT do something eventually?
On Wed, May 17, 2017 at 5:54 PM, dustinsmoak@
...wrote:
>
> FYI -- the may be working on fixing the div adjustment problem:
>
> https://yahoo.uservoice.com/forums/382977-finance/
> suggestions/
>
> __
>
Wed May 17, 2017 4:54 pm (PDT) . Posted by:
alankc_mok
Randy,
If I want to extract earning data from Busystock http://busystock.com/i.php?s=aapl http://busystock.com/i.php?s=aapl which add-in should be more appropriate ?
Regards
Alan
If I want to extract earning data from Busystock http://busystock.com/i.php?s=aapl http://busystock.com/i.php?s=aapl which add-in should be more appropriate ?
Regards
Alan
Wed May 17, 2017 6:00 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You should be able to grab the whole table with:
=RCHGetHTMLTable("http://busystock.com/i.php?s=aapl","Earning Date")
Individual items should be extractable using RCHGetTableCell() or
smfGetTagContent(), but the question would be how to identify a specific
item.
On Wed, May 17, 2017 at 4:54 PM, alankc_mok@
...wrote:
>
> If I want to extract earning data from Busystock http://busystock.
> com/i.php?s=aapl which add-in should be more appropriate ?
>
>
>
=RCHGetHTMLTable("http://busystock.com/i.php?s=aapl","Earning Date")
Individual items should be extractable using RCHGetTableCell() or
smfGetTagContent(), but the question would be how to identify a specific
item.
On Wed, May 17, 2017 at 4:54 PM, alankc_mok@
...wrote:
>
> If I want to extract earning data from Busystock http://busystock.
> com/i.php?s=
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar