Excel Macro using Yahoo Finance- not working

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
jasc15
Posts: 327
Joined: Wed Dec 19, 2012 1:36 pm

Excel Macro using Yahoo Finance- not working

Postby jasc15 » Fri May 12, 2017 3:30 pm

The Python thread reminded me. About a year ago I found an excel macro on the web that creates an function to grab stock prices from Yahoo Finance. The last few weeks, I've noticed that the macro no longer grabs those prices. Anyone else use this and notice that it isn't working?

penumbra
Posts: 240
Joined: Thu Mar 29, 2007 2:42 am

Re: Excel Macro using Yahoo Finance- not working

Postby penumbra » Fri May 12, 2017 3:40 pm

Yes, exactly the same experience. I had been using a sheet modified from Gummy's material, back when it was freely available. Stopped working around the first of the year. I'm not slick enough to troubleshoot the problem.

CantPassAgain
Posts: 238
Joined: Fri Mar 15, 2013 8:49 pm

Re: Excel Macro using Yahoo Finance- not working

Postby CantPassAgain » Fri May 12, 2017 4:10 pm

jasc15 wrote:The Python thread reminded me. About a year ago I found an excel macro on the web that creates an function to grab stock prices from Yahoo Finance. The last few weeks, I've noticed that the macro no longer grabs those prices. Anyone else use this and notice that it isn't working?


There's a data connection function that will pull stock prices from MSN. Click on Data>Connections>Add.

There should be a selection for "MSN MoneyCentral Investor Stock Quotes" all the way at the bottom. Click on it then click "open"

Go to properties and check enable background refresh and choose how often you want it to refresh, also if you want to refresh data when opening the file. Click OK and then close.

Then go to Data>Existing Connections and click "MSN MoneyCentral Investor Stock Quotes" all the way at the bottom and open.

The import data window will open, click ok, enter the ticker you want, then OK again and voila, there you go.

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

Re: Excel Macro using Yahoo Finance- not working

Postby Oicuryy » Fri May 12, 2017 7:40 pm

The macro in the first spreadsheet on this gummy-stuff page still works.

http://www.financialwisdomforum.org/gum ... o-data.htm

Ron
Money is fungible | Abbreviations and Acronyms

User avatar
whaleknives
Posts: 1115
Joined: Sun Jun 24, 2012 7:19 pm

Re: Excel Macro using Yahoo Finance- not working

Postby whaleknives » Fri May 12, 2017 7:43 pm

This is an argument for not trying to do your own aggregating, but to download CSV spreadsheets from providers instead.
"I'm an indexer. I own the market. And I'm happy." (John Bogle, "BusinessWeek", 8/17/07) ☕ Maritime signal flag W - Whiskey: "I require medical assistance."

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

Re: Excel Macro using Yahoo Finance- not working

Postby bertilak » Sat May 13, 2017 12:40 pm

Oicuryy wrote:The macro in the first spreadsheet on this gummy-stuff page still works.

http://www.financialwisdomforum.org/gum ... o-data.htm

Ron

I use Yahoo as well but recently found that the link given in the above-referenced URL no longer works.

I now use http://download.finance.yahoo.com/d/quotes?s= which does work.
Last edited by bertilak on Sun May 14, 2017 3:20 pm, edited 1 time in total.
I have a strong moral sense - by my standards. | -- Rex Stout

User avatar
dratkinson
Posts: 3792
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Macro using Yahoo Finance- not working

Postby dratkinson » Sun May 14, 2017 3:12 pm

Thought it was just me. As it seems to be affecting many, suspect yahoo has done something on their end. Why?

Thought I broke the macro so went looking for the original and found a letter from a yahoo lawyer telling gummy to "stop".
See original gummy page replaced by lawyer's letter: http://www.gummy-stuff.org/Yahoo-data.htm



Believe I may have found a PTF (permanent temporary fix). What?
--Copy/paste/submit marco's constructed URL (cell C1) in a browser window, and open returned CSV file in Excel (opens in new spreadsheet).
--Copy/paste extracted Excel data into our original spreadsheet.

Try it (right-click, open in new tab, then open with Excel as CSV):
http://quote.yahoo.com/d/quotes.csv?s=N ... c1p2d1t1jk

Which implies we could bookmark a webpage if our constructed URL is static.



Curiouser and curiouser. The results from the submitted URL is prevented from being opened by the Excel macro, but does work when submitted from a browser. How do yahoo know? Does Excel include a header identifying itself as the submitter?
Last edited by dratkinson on Sun May 14, 2017 3:23 pm, edited 1 time in total.
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

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

Re: Excel Macro using Yahoo Finance- not working

Postby bertilak » Sun May 14, 2017 3:17 pm

dratkinson wrote:As it seems to be affecting many, suspect yahoo has done something on their end. Why?

What they have done on their end is change the URL. Use the one I intended to post just above. I made a mistake and posted an old one which stopped working. Try http://download.finance.yahoo.com/d/quotes?s=. I corrected my above post.

Why did they do it? Perhaps they just liked it better that way.
I have a strong moral sense - by my standards. | -- Rex Stout

User avatar
dratkinson
Posts: 3792
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Macro using Yahoo Finance- not working

Postby dratkinson » Sun May 14, 2017 3:53 pm

bertilak wrote:
dratkinson wrote:As it seems to be affecting many, suspect yahoo has done something on their end. Why?

What they have done on their end is change the URL. Use the one I intended to post just above. I made a mistake and posted an old one which stopped working. Try http://download.finance.yahoo.com/d/quotes?s=. I corrected my above post.

Why did they do it? Perhaps they just liked it better that way.


Thanks, that worked. Now I can go back and replace all of the formatting overwritten when I copied/pasted the extracted CSV data.



Recall from gummy's posts that yahoo does occasionally change their URLs.

Do you have a link to the yahoo webpage in which they describe their format du jour.
--The link would be handy to include in a commented cell telling me where to check the next time gummy's macro stops working.
--Or maybe a better idea is to include the link in a cell that only becomes visible when yahoo's change blows away all requested data. :)
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

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

Re: Excel Macro using Yahoo Finance- not working

Postby Gene S » Tue May 16, 2017 8:09 pm

dratkinson's post gave a yahoo finance hyperlink that returned a cvs file of the current stock price for a bunch of stocks. Is it possible to get the end of day stock price for a specific day?

User avatar
dratkinson
Posts: 3792
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Macro using Yahoo Finance- not working

Postby dratkinson » Tue May 16, 2017 8:59 pm

Gene S wrote:dratkinson's post gave a yahoo finance hyperlink that returned a cvs file of the current stock price for a bunch of stocks. Is it possible to get the end of day stock price for a specific day?


The link is the way gummy's macro constructed it. It's purpose is to get the day's closing prices. It's all I've ever needed.

However, I do recall seeing (while searching for what I might have done wrong that broke the macro) references to an Excel macro that could do more. You'll have to search for it. Could try searching for something like:
this: http://www.google.com/search?q=excel+ma ... tock+price
or this: http://www.google.com/search?q=excel+ma ... rice+range
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

User avatar
ThereAreNoGurus
Posts: 59
Joined: Fri Jan 24, 2014 11:41 pm

Re: Excel Macro using Yahoo Finance- not working

Postby ThereAreNoGurus » Tue May 16, 2017 9:09 pm

Just curious why you folks use Yahoo! Finance and an Excel spreadsheet when you can use Morningstar's free portfolio manager. You don't have to spend time downloading prices. Also when there are dividends or cap gains distributions they are very easy to post to your portfolio. Do these Excel macros have the same functionality or do you have to manually enter the info for each distribution?

JohnFiscal
Posts: 376
Joined: Mon Jan 06, 2014 4:28 pm
Location: USA

Re: Excel Macro using Yahoo Finance- not working

Postby JohnFiscal » Tue May 16, 2017 9:31 pm

ThereAreNoGurus wrote:Just curious why you folks use Yahoo! Finance and an Excel spreadsheet when you can use Morningstar's free portfolio manager. You don't have to spend time downloading prices. Also when there are dividends or cap gains distributions they are very easy to post to your portfolio. Do these Excel macros have the same functionality or do you have to manually enter the info for each distribution?


I have a large Excel workbook that integrates all my personal finances. I tried MS Money and Quicken in the 1990's but settled on Excel as my preference...still is.

I have made it easy on myself and have a Google spreadsheet that updates fund prices whenever it's opened, then I copy/paste into my Excel workbook; takes a few seconds. I have used various routines to work directly from Excel but I have never been pleased with the results, the data source, or the robustness.

My "piece de resistance" is a longish VBA routine that...assuming I have an open CSV format file open in Excel, as downloaded from Vanguard, with that month's (or other term) transactions... earning, dividends, etc, will read that file and copy all data to the corresponding account sheets in my workbook, update the IRR, do some other work, publish that month's Net Worth statement to pdf (and even email to my wife), and...importantly...has some check sums to verify that all the data is accurate. It all takes only about 2 minutes or less to run.

This automated system was found desirable when I ended up one year with I think it was 19 different transactions (or maybe it was 19 different accounts, I think the former). The large number occurs regularly, when there are LT cap gains, ST cap gains, and then dividends. And includes account registrations as one inherited IRA, two Roth IRA accounts (two different funds in one), rollover IRA, regular IRA, and then taxable joint accounts. Even though the same "fund" may be present in a number of account types/registration they are all a different entity and are kept track of that way. So a simple, interesting chore became over time a very tedious frustrating exercise. Now resolved with my VBA utilities.

I suppose at this point in time (the age of checking accounts on-line) there is really little-to no need for me to maintain my own books on a number of these accounts. But I am old school...

User avatar
dratkinson
Posts: 3792
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Macro using Yahoo Finance- not working

Postby dratkinson » Tue May 16, 2017 9:58 pm

+1. M* portfolio manager doesn't do all I want.

I've built in the ability to display: when any IPS management action is required/cleared, AA is out of balance, TLH opportunity available, test the effect of selling by share lots to include producing a trial Sch D, time-based reminders (set/check withholding taxes, first of month required actions, end of quarter required actions,...), and other stuff I've forgotten.

Basically, if there is something I need to do to manage my investments/IPS, I've found a way to build it (and associated conditional formatting to highlight results of tests or reminders) into Excel.

So after close of business and gummy's macro runs, a year's worth of checking is done, and any required action is highlighted. It lowers my stress level knowing Excel locally (not dependent upon online services* that could be changed/withdrawn) is doing automatically everything I must remember to do.

* If the Yahoo price service goes away, I can manually update the closing prices of the few funds I use. So the Yahoo price macro is a very nice feature, but it's not a necessity.
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

User avatar
ThereAreNoGurus
Posts: 59
Joined: Fri Jan 24, 2014 11:41 pm

Re: Excel Macro using Yahoo Finance- not working

Postby ThereAreNoGurus » Tue May 16, 2017 10:38 pm

Thanks for the responses!

JohnFiscal
Posts: 376
Joined: Mon Jan 06, 2014 4:28 pm
Location: USA

Re: Excel Macro using Yahoo Finance- not working

Postby JohnFiscal » Wed May 17, 2017 7:10 am

I am familiar with "gummy" from the Canadian forum, and I figured I would check out the link to the Yahoo data download. Apparently Yahoo sent a 'cease and desist' email and this information has been taken down, I don't know if this was recently or if this has been longstanding. http://www.gummy-stuff.org/Yahoo-data.htm

Yahoo's mail says that the use of the macro is "hacking" their system. I wonder if this is really and truly the case. I suppose they lose money by not selling you to their advertisers. But still I wonder.

User avatar
dratkinson
Posts: 3792
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Macro using Yahoo Finance- not working

Postby dratkinson » Wed May 17, 2017 8:03 am

JohnFiscal wrote:I am familiar with "gummy" from the Canadian forum, and I figured I would check out the link to the Yahoo data download.


If you want gummy's macro, send me your email by PM. You'll need to make the above URL change to get it to work, but that's simple enough.
Last edited by dratkinson on Thu May 18, 2017 3:57 am, edited 1 time in total.
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

ValueInvestor99
Posts: 37
Joined: Thu Dec 23, 2010 2:25 pm

Re: Excel Macro using Yahoo Finance- not working

Postby ValueInvestor99 » Thu May 18, 2017 1:10 am

Apple Numbers software allows pulling of stock and option prices into spreadsheets.

paper200
Posts: 251
Joined: Sat Feb 02, 2008 11:40 am

Re: Excel Macro using Yahoo Finance- not working

Postby paper200 » Fri May 19, 2017 3:41 pm

The following will download stock price:
https://download.finance.yahoo.com/d/qu ... ,goog,aapl &f=sl1

General code for macro:
Https://download.finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=sl1

A month back i did not have the "download" nor "https" and it had stopped working. I download the price to excel. yahoo finance has explanation for the stuff between quotes. Please google "btnrefresh" and copy and paste the macro. P.S: I am not a software expert barely understand what the macro does.
Having freedom, food and roof is being 90% lucky in life and so is index investing. So, don't let the remaining 10% bother you.

User avatar
Electron
Posts: 1625
Joined: Sat Mar 10, 2007 8:46 pm

Re: Excel Macro using Yahoo Finance- not working

Postby Electron » Fri May 19, 2017 4:03 pm

I also noticed the problem with Yahoo Finance very recently.

The Excel macro I use for individual quotes still works, but another macro that previously downloaded Yahoo Finance Historical Data no longer works. Both macros use Excel QueryTables. I even tried doing a Web Query manually with the correct link for historical data and it fails as well.
Electron


Return to “Investing - Theory, News & General”

Who is online

Users browsing this forum: bigskyguy, cusetownusa, iamlucky13, nukewerker and 37 guests