Fixing Yahoo Finance Macro - HELP

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
gkfische
Posts: 1
Joined: Fri Jun 02, 2017 7:48 pm

Fixing Yahoo Finance Macro - HELP

Post by gkfische » Fri Jun 02, 2017 8:05 pm

Okay Boggleheads,

I figure this would be the place to turn for some technical assistance. Some other threads have already talked about macros for Yahoo Finance not working since mid-May due to changes in their URL. I'm run into the same problem with my trend following macro that I created about two years ago. I've read a few threads trying to troubleshoot my issue and fix it (switching http://table.finance.yahoo.com/table.csv? to http://download.finance.yahoo.com/d/quotes?s=, etc.) but to no avail. I think I may be a little rusty in VBR language, which isn't exactly helping me at the present moment.

So, I'm turning to the general community for some help. I uploaded the spreadsheet to a Google Drive account so someone can easily download the archive and hopefully solve my conundrum: https://drive.google.com/open?id=0BxDAJ ... UdHQW9WQ00

User avatar
jimb_fromATL
Posts: 2236
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: Fixing Yahoo Finance Macro - HELP

Post by jimb_fromATL » Fri Jun 02, 2017 10:07 pm

gkfische wrote:Okay Boggleheads,

I figure this would be the place to turn for some technical assistance. Some other threads have already talked about macros for Yahoo Finance not working since mid-May due to changes in their URL. I'm run into the same problem with my trend following macro that I created about two years ago. I've read a few threads trying to troubleshoot my issue and fix it (switching http://table.finance.yahoo.com/table.csv? to http://download.finance.yahoo.com/d/quotes?s=, etc.) but to no avail. I think I may be a little rusty in VBR language, which isn't exactly helping me at the present moment.

So, I'm turning to the general community for some help. I uploaded the spreadsheet to a Google Drive account so someone can easily download the archive and hopefully solve my conundrum: https://drive.google.com/open?id=0BxDAJ ... UdHQW9WQ00
Recent thread

Some things may still work, but a lot of vba macros from several sources no longer work. Gummy's site that described how to use the interface had recently posted a copy of a cease-and-desist letter from Yahoo's lawyer stating that reverse-engineering the interface was a violation of their TOS, and very recently, gummy's site about the interface has been closed completely.

It's not likely that anybody can help you make the back-door approaches work for long, since Yahoo intentionally disabled it. If you can figure out a work-around, chances are they'll close that backdoor as soon as they realize it.

Even if you can get it to work, Yahoo has stopped calculating the historical closing data adjusted for dividends and splits, which makes it virtually useless for analyzing any fund for any length of time or for comparing funds to each other.

jimb

User avatar
Oicuryy
Posts: 1231
Joined: Thu Feb 22, 2007 10:29 pm

Re: Fixing Yahoo Finance Macro - HELP

Post by Oicuryy » Fri Jun 02, 2017 11:01 pm

I have not tried it, but this workaround using PostText looks promising.

https://www.mrexcel.com/forum/excel-que ... ost4832496

Ron
Money is fungible | Abbreviations and Acronyms

User avatar
jimb_fromATL
Posts: 2236
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: Fixing Yahoo Finance Macro - HELP

Post by jimb_fromATL » Fri Jun 02, 2017 11:11 pm

Oicuryy wrote:I have not tried it, but this workaround using PostText looks promising.

https://www.mrexcel.com/forum/excel-que ... ost4832496

Ron
The "crumb" in that command line is apparently a token or cookie of some sort that has a time limit. Wonder if it's a "cookie crumb"?

You can get the command line by going through their cumbersome data screens to download historical data, then copying the URL of the download data. That can then be used in an Excel macro or to build a clickable hyperlink in a cell.

But my observation has been --so far-- that it will only work for a few hours or perhaps a day before it stops. Next time you go through the menus, the "crumb" will be different. So ... as far as I can tell, it can't really be used as a permanent replacement in code.

jimb

User avatar
Oicuryy
Posts: 1231
Joined: Thu Feb 22, 2007 10:29 pm

Re: Fixing Yahoo Finance Macro - HELP

Post by Oicuryy » Fri Jun 02, 2017 11:47 pm

The crumb does seem to be needed. But the post by kloc in this thread suggests that the crumb is not necessary if the url is sent with an http post request instead of a get request.

http://www.nuclearphynance.com/Show%20P ... Key=184566

Ron
Money is fungible | Abbreviations and Acronyms

User avatar
Oicuryy
Posts: 1231
Joined: Thu Feb 22, 2007 10:29 pm

Re: Fixing Yahoo Finance Macro - HELP

Post by Oicuryy » Sat Jun 03, 2017 12:41 am

This querytable worked for me in excel 2003 on windows 7.

Code: Select all

Sub yahoohistory()

   With ActiveSheet.QueryTables.Add("URL;", Range("A1"))
      .Connection = "URL;" & "https://query1.finance.yahoo.com/v7/finance/download/IBM?period1=1493782307&period2=1496460707&interval=1d&events=history"
      .BackgroundQuery = False
      .AdjustColumnWidth = False
      .PostText = " "
      .Refresh
      .SaveData = True
   End With

End Sub
Money is fungible | Abbreviations and Acronyms

User avatar
bertilak
Posts: 5950
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Fixing Yahoo Finance Macro - HELP

Post by bertilak » Sat Jun 03, 2017 7:58 pm

All the add-ons and macros mentioned all over the internet simply boil down to the following URL:
where "xxxxx" comes from here: http://www.jarloo.com/yahoo_finance. The Yahoo URL given on that page is out of date. It needs to have "download" added to it as I have shown above. There are a bunch of other web pages describing the same thing and pretty much all of them are out of date in the same way. This is a recent change by Yahoo.

In Excel you do not need ANY of the macros or add-ons offered in various places. Using those simply puts you at the mercy of the authors, depending on them to maintain their complex systems. Just do it yourself. Of course you are STILL at the mercy of Yahoo but you have cut out a middleman.

Here is an actual example. Click on this to download a file containing (as of today) the text 61.08: To drop that value into an Excel cell put the folowing formula into the cell:
May neither drought nor rain nor blizzard disturb the joy juice in your gizzard. -- Squire Omar Barker, the Cowboy Poet

User avatar
jimb_fromATL
Posts: 2236
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: Fixing Yahoo Finance Macro - HELP

Post by jimb_fromATL » Sat Jun 03, 2017 9:43 pm

Even if it's possible to come with work-arounds to get the historical data, it's practically useless unless Yahoo also resumes calculating the adjusted closing for dividends.

User avatar
jimb_fromATL
Posts: 2236
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

duplicate post deleted

Post by jimb_fromATL » Sat Jun 03, 2017 9:44 pm

duplicate deleted

GLState
Posts: 127
Joined: Wed Feb 15, 2017 10:38 am

Re: Fixing Yahoo Finance Macro - HELP

Post by GLState » Sun Jun 04, 2017 7:01 am

jimb_fromATL wrote:Even if it's possible to come with work-arounds to get the historical data, it's practically useless unless Yahoo also resumes calculating the adjusted closing for dividends.
Yahoo also provides dividend data. We'll have to use this dividend data to calculate our own adjusted close prices to calculate total return. But as of now, yahoo data has a lot of problems...one example, VXUS has many "null" values for 2014 and 2015.

Gene S
Posts: 25
Joined: Mon Jul 27, 2015 12:27 pm

Re: Fixing Yahoo Finance Macro - HELP

Post by Gene S » Tue Jun 06, 2017 1:31 pm

GLState wrote:
jimb_fromATL wrote:Even if it's possible to come with work-arounds to get the historical data, it's practically useless unless Yahoo also resumes calculating the adjusted closing for dividends.
Yahoo also provides dividend data. We'll have to use this dividend data to calculate our own adjusted close prices to calculate total return. But as of now, yahoo data has a lot of problems...one example, VXUS has many "null" values for 2014 and 2015.
Not only is Yahoo Finance historical price download missing the 2014 and 2015 price data for the Vanguard Total International Stock Index VXUS, it is missing the earlier price data from 2011 to 2014. I noticed the SPXPG was missing 5 months of data in 2015.

Post Reply