15 Messages
Digest #4011
Messages
Wed May 17, 2017 7:54 pm (PDT) . Posted by:
eadamy
You are of course correct in your observation regarding the close and adjusted close being the same. I had one of those brain things.
I have given up on Y for now and have taken the plunge with a Quandl Quote Media sub to get adjusted close data for stocks and ETFs. The HTML API is pretty simple. I can always come back to Y if they get the adjusted close working right; however I'm not convinced that Y is a viable long term solution.
In my case, I suspect it's best to face the music and get on with things. In my case, it will mean maintaining two paid data subscriptions because each provides data I need which is not available with the other. Data has always been such a racket which is why your SMF functions have been so popular. I just looked at CSI today (Y's quote provider) and they want a one time fee of $50/year for history in addition to rather steep data fees.
I don't really know how long I've been using your SMF functions but it's probably been something like 10-15 years. I thank you for sharing your work.
Earl
I have given up on Y for now and have taken the plunge with a Quandl Quote Media sub to get adjusted close data for stocks and ETFs. The HTML API is pretty simple. I can always come back to Y if they get the adjusted close working right; however I'm not convinced that Y is a viable long term solution.
In my case, I suspect it's best to face the music and get on with things. In my case, it will mean maintaining two paid data subscriptions because each provides data I need which is not available with the other. Data has always been such a racket which is why your SMF functions have been so popular. I just looked at CSI today (Y's quote provider) and they want a one time fee of $50/year for history in addition to rather steep data fees.
I don't really know how long I've been using your SMF functions but it's probably been something like 10-15 years. I thank you for sharing your work.
Earl
Wed May 17, 2017 8:22 pm (PDT) . Posted by:
for30sqn
Thanks Randy for all you do for us. I've used the history function for a few years to compute a chandelier stop. The only data I need for this is the high, low, close prices for the last 22 periods. Things were working well until just recently. I know some smf users may wish for more detailed data, but I would be happy with hi/lo/close for the day.
Your support to this group has been fantastic, and I thank you again.
Allen
Your support to this group has been fantastic, and I thank you again.
Allen
Wed May 17, 2017 11:34 pm (PDT) . Posted by:
bizmark_ee
Ah, I did not realize it had been going on for that long.
Thu May 18, 2017 4:08 am (PDT) . Posted by:
jaja3279
Thank you Randy for your work and continuing support. While you are writing this new function, I have this for your consideration.
I use smfGetYahooHistory for monthly data with "DA" as the Data Item parameter to get the dates in column A and prices in column B. It returns the first-of-the-month date in the date column and the end-of-the-month price in the price column. Of course, this is how Yahoo provides the dates for monthly data, but obviously the dates are not consistent with the prices. It becomes an issue when performing date dependent calculations such as CAGR with the result being slightly off. So, I wonder if it is it possible to adjust the date to the actual date of the price, that is, the end of-the-month date?
I use smfGetYahooHistory for monthly data with "DA" as the Data Item parameter to get the dates in column A and prices in column B. It returns the first-of-the-
Thu May 18, 2017 6:51 am (PDT) . Posted by:
rr76012
5/18/2017
Randy,
I found this on the internet.
I hope this helps?
rr76012
Yahoo finance URL not working http://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working
http://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working http://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working
I'm in the same boat. Getting there slowly. The download link on the historical prices page still works. So I added the export cookies extension to firefox, logged in to yahoo, dumped the cookies. Used the crumb value from interactive session and I was able to retrieve values.
Here's part of a test perl script that worked.
$p1= timelocal(0,0,0,1,0,114);
$p2= timelocal(0,0,0,31,11,117);
$symbol = 'AAPL';
$task = "wget --load-cookies cookies.txt --no-check-certificate -T 30 -O $symbol.csv \"https://query1.finance.yahoo.com/v7/finance/download/$symbol?period1=$p1&period2=$p2&interval=1d&events=history&crumb=7WhHVu5N4e3\" ";
print $task;
`$task`;
It'll take a while to automate what I do. Hopefully yahoo will simplify or give some guidance on it if they really intend for people to use it.
answered 14 hours ago
http://stackoverflow.com/users/2654202/george-statham
George Statham http://stackoverflow.com/users/2654202/george-statham
Randy,
I found this on the internet.
I hope this helps?
rr76012
Yahoo finance URL not working http://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working
http://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working http://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working
I'm in the same boat. Getting there slowly. The download link on the historical prices page still works. So I added the export cookies extension to firefox, logged in to yahoo, dumped the cookies. Used the crumb value from interactive session and I was able to retrieve values.
Here's part of a test perl script that worked.
$p1= timelocal(0,0,0,1,0,114);
$p2= timelocal(0,0,0,31,11,117);
$symbol = 'AAPL';
$task = "wget --load-cookies cookies.txt --no-check-certificate -T 30 -O $symbol.csv \"https://query1.finance.yahoo.com/v7/finance/download/$symbol?period1=$p1&period2=$p2&interval=1d&events=history&crumb=7WhHVu5N4e3\" ";
print $task;
`$task`;
It'll take a while to automate what I do. Hopefully yahoo will simplify or give some guidance on it if they really intend for people to use it.
answered 14 hours ago
http://stackoverflow.com/users/2654202/george-statham
George Statham http://stackoverflow.com/users/2654202/george-statham
Thu May 18, 2017 8:20 am (PDT) . Posted by:
"Eric Stats" ehstats
I use smfPricesByDates. Will that formula still work after Yahoo makes
some adjustments or what service do you recommend to use to get historical
quotes? Thanks, Eric
On Wed, May 17, 2017 at 6:44 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> 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.
>>
>
>
--
Eric Stats, CFA
801-552-6631
some adjustments or what service do you recommend to use to get historical
quotes? Thanks, Eric
On Wed, May 17, 2017 at 6:44 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> 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","
>
>
>
>> 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"
> low":237.
> volume"
> {"amount"
> {"date"
> 1999969482422,
> 47999572753906,
>
> 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.
>>
>
>
--
Eric Stats, CFA
801-552-6631
Thu May 18, 2017 9:19 am (PDT) . Posted by:
john_hoel
Eric,
I use Marketwatch, like this: =RCHGetTableCell("http://bigcharts.marketwatch.com/historical/default.asp?symb="&F$7&"&closeDate="&TEXT(E9,"mm/dd/yyyy"),1,"Closing Price:")
I use Marketwatch, like this: =RCHGetTableCell("http://bigcharts.marketwatch.com/historical/default.asp?symb="&F$7&"
Thu May 18, 2017 9:34 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
smfPricesByDates() will return -- it will just use the new function to get
the data and extract as it always did.
On Thu, May 18, 2017 at 8:20 AM, Eric Stats ericstats@gmail.com [smf_addin]
<smf_addin@yahoogroups.com> wrote:
>
> I use smfPricesByDates. Will that formula still work after Yahoo makes
> some adjustments or what service do you recommend to use to get historical
> quotes? Thanks, Eric
>
the data and extract as it always did.
On Thu, May 18, 2017 at 8:20 AM, Eric Stats ericstats@gmail.com [smf_addin]
<smf_addin@yahoogroups.com> wrote:
>
> I use smfPricesByDates. Will that formula still work after Yahoo makes
> some adjustments or what service do you recommend to use to get historical
> quotes? Thanks, Eric
>
Thu May 18, 2017 9:47 am (PDT) . Posted by:
tonyestep
So if I'm understanding this correctly: The get history function is gone never to return, but given a column of valid trading dates, we'll be able to use smfPricesByDates() to fill in a column of adjusted closes. Is this correct? If so, is there a function to get the valid trading dates?
Thanks,
Tony
Thanks,
Tony
Thu May 18, 2017 9:59 am (PDT) . Posted by:
"Robbie Geary" rgearyiii
As I can see, Randy is right that the page source code contains historical
prices. What isn't obvious to me (without being able to decipher Unix
timestamps in my head) is how far back the quotes go in the source code
generated by a first hit at a symbol's page. Does the source code contain
the complete history, or just the last 100 quotes? In other words, does it
take a "scroll" to dynamically generate source code with more quotes, or is
it all in there from the outset?
On Fri, May 19, 2017 at 2:47 AM, tonyestep@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> So if I'm understanding this correctly: The get history function is gone
> never to return, but given a column of valid trading dates, we'll be able
> to use smfPricesByDates() to fill in a column of adjusted closes. Is this
> correct? If so, is there a function to get the valid trading dates?
>
> Thanks,
> Tony
>
>
>
--
Robbie Geary
prices. What isn't obvious to me (without being able to decipher Unix
timestamps in my head) is how far back the quotes go in the source code
generated by a first hit at a symbol's page. Does the source code contain
the complete history, or just the last 100 quotes? In other words, does it
take a "scroll" to dynamically generate source code with more quotes, or is
it all in there from the outset?
On Fri, May 19, 2017 at 2:47 AM, tonyestep@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> So if I'm understanding this correctly: The get history function is gone
> never to return, but given a column of valid trading dates, we'll be able
> to use smfPricesByDates(
> correct? If so, is there a function to get the valid trading dates?
>
> Thanks,
> Tony
>
>
>
--
Robbie Geary
Thu May 18, 2017 12:55 pm (PDT) . Posted by:
"Howard B" howardbandy
Greetings --
An employee of Yahoo posting on the Yahoo Help Site confirms that Yahoo has
removed support for price quotes. I have pasted a portion of the forum
exchange below.
Best regards, Howard
------------
Re: Is Yahoo! Finance API broken?
Hi All - This feature was discontinued by the Finance team and they will
not be reintroducing that functionality.
The Yahoo Finance Feedback Forum is the place where you can make product
suggestions and provide feedback. We're always trying to improve our
products and use your feedback to inform changes. Here's the url:
https://yahoo.uservoice.com/forums/382977
On Thu, May 18, 2017 at 9:47 AM, tonyestep@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> So if I'm understanding this correctly: The get history function is gone
> never to return, but given a column of valid trading dates, we'll be able
> to use smfPricesByDates() to fill in a column of adjusted closes. Is this
> correct? If so, is there a function to get the valid trading dates?
>
> Thanks,
> Tony
>
>
>
An employee of Yahoo posting on the Yahoo Help Site confirms that Yahoo has
removed support for price quotes. I have pasted a portion of the forum
exchange below.
Best regards, Howard
------------
Re: Is Yahoo! Finance API broken?
Hi All - This feature was discontinued by the Finance team and they will
not be reintroducing that functionality.
The Yahoo Finance Feedback Forum is the place where you can make product
suggestions and provide feedback. We're always trying to improve our
products and use your feedback to inform changes. Here's the url:
https://yahoo.uservoice.com/forums/382977
On Thu, May 18, 2017 at 9:47 AM, tonyestep@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> So if I'm understanding this correctly: The get history function is gone
> never to return, but given a column of valid trading dates, we'll be able
> to use smfPricesByDates(
> correct? If so, is there a function to get the valid trading dates?
>
> Thanks,
> Tony
>
>
>
Thu May 18, 2017 1:12 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You are pretty much correct:
1. I believe RCHGetYahooHistory() is done with. There is no longer a CSV
file to process.
2. I will be creating a new smfGetYahooHistory() function, to extract the
JSON data.
3. smfPricesByDates() will extract requested prices from the new
smfGetYahooHistory() function output
Withe smfPricesByDates(), valid trading dates are irrelevant. It gets you
the adjusted closing price as of any given date. They are carried forward
for non-trading days.
On Thu, May 18, 2017 at 9:47 AM, tonyestep@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> So if I'm understanding this correctly: The get history function is gone
> never to return, but given a column of valid trading dates, we'll be able
> to use smfPricesByDates() to fill in a column of adjusted closes. Is this
> correct? If so, is there a function to get the valid trading dates?
>
>
1. I believe RCHGetYahooHistory() is done with. There is no longer a CSV
file to process.
2. I will be creating a new smfGetYahooHistory() function, to extract the
JSON data.
3. smfPricesByDates() will extract requested prices from the new
smfGetYahooHistory() function output
Withe smfPricesByDates(), valid trading dates are irrelevant. It gets you
the adjusted closing price as of any given date. They are carried forward
for non-trading days.
On Thu, May 18, 2017 at 9:47 AM, tonyestep@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> So if I'm understanding this correctly: The get history function is gone
> never to return, but given a column of valid trading dates, we'll be able
> to use smfPricesByDates(
> correct? If so, is there a function to get the valid trading dates?
>
>
Thu May 18, 2017 1:14 pm (PDT) . Posted by:
rr76012
Randy,
You are an angel....:+))
I hope the link I sent you earlier helped, even if only a little bit.
rr76012
You are an angel....:+)
I hope the link I sent you earlier helped, even if only a little bit.
rr76012
Thu May 18, 2017 1:16 pm (PDT) . Posted by:
rr76012
Randy,
For historical download stock prices.
Do you have an example spreadsheet of how to use smfPricesByDates()?
thanks,
rr76012
For historical download stock prices.
Do you have an example spreadsheet of how to use smfPricesByDates(
thanks,
rr76012
Thu May 18, 2017 10:42 am (PDT) . Posted by:
joe_mo37
Randy,
Based on a recommendation you made last week, I'm using an array-entered formula to retrieve historical data from Google. This formula is working well for stocks and ETFs:
"=smfGetCSVFile(""https://www.google.com/finance/historical?q=""&$B$1&""&startdate=""&TEXT($B$2,""mmm+dd,+yyyy"")&""&enddate=""&TEXT($B$3,""mmm+dd,+yyyy"")&""&output=csv";")"
where B1 is ticker symbol, B2 is start date, and B3 is end date.
Is there a version of this that will work for mutual funds? I know that the Google site returns only date and close price for mutual funds, but I have not been successful trying to modify the formula above to return data for mutual funds.
Based on a recommendation you made last week, I'm using an array-entered formula to retrieve historical data from Google. This formula is working well for stocks and ETFs:
"=smfGetCSVFile(""https://www.google.com/finance/historical?q=""
where B1 is ticker symbol, B2 is start date, and B3 is end date.
Is there a version of this that will work for mutual funds? I know that the Google site returns only date and close price for mutual funds, but I have not been successful trying to modify the formula above to return data for mutual funds.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar