[Yahoo Finance no longer works - Alternatives to download securities quotes]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
bberris
Posts: 1567
Joined: Sun Feb 20, 2011 9:44 am

Re: Anyone else lose ability to download stock prices recently?

Post by bberris »

Yes, Yahoo smf no longer work. There's another thread.

I converted to Google Sheets, and use the =GoogleFinance(A1, "price") function. Unfortunately, there is no GoogleFinance call for dividends for stocks that works consistently, so I had to enter those manually (they don't change all that frequently), but it is a mild pain. However, apparently the dividend call works for mutual funds (but probably not ETFs?).

Details on GoogleFinance:

https://support.google.com/docs/answer/3093281?hl=en
earlyout
Posts: 1434
Joined: Tue Feb 20, 2007 5:24 pm

Re: Excel Macro using Yahoo Finance- not working

Post by earlyout »

I've used Excel to pull prices of my portfolio holdings for several years. When Yahoo changed their calling sequence last spring I modified my portfolio program based on Gummy's calling sequence to use the Excel routines for Yahoo data provided by XLAutomation. (Please read past the first page since the short positing about it being broken refers to the problems earlier this year.)

I tested my program this morning by retrieving portfolio values on different dates and got the same values as before. The values retrieved just now also agree with my brokerage statements.

https://www.xlautomation.com.au/free-sp ... ce-extract.
rutrow2015
Posts: 68
Joined: Thu Jan 07, 2016 8:04 am

Re: Anyone else lose ability to download stock prices recently?

Post by rutrow2015 »

@bberris

Forgive my ignorance. If I'm interpreting your response correctly the solution you highlighted will mean that I download the prices into a spreadsheet and then save that spreadsheet as a csv file and then import that csv file into my financial software?

That's doable but I just want to make sure I'm understanding the process correctly.

Thanks for your help.
bberris
Posts: 1567
Joined: Sun Feb 20, 2011 9:44 am

Re: Anyone else lose ability to download stock prices recently?

Post by bberris »

I don't use any special financial software other than spreadsheets so I can't help you there. Maybe downloading to a csv would work. I just use Google Sheets to calculate what I need. Mainly I am controlling my income for ACA.

But you could use Google sheets for whatever you need. Ladygeek posted a rebalancing sheet, for example. And it's free.
Danielle67251
Posts: 2
Joined: Mon Oct 23, 2017 1:21 pm

Re: GETYAHOO broken? [Spreadsheet extension]

Post by Danielle67251 »

[Thread merged into here, see below. --admin LadyGeek]

GetYahoo was working for me one week ago. For the past few days it returns the text "Forbidden". Everyone getting the same response now? I suspect this is a message from the host.
Tarkus
Posts: 67
Joined: Sun Aug 25, 2013 9:43 pm

Re: GETYAHOO broken? [Spreadsheet extension]

Post by Tarkus »

Yahoo shut down their finance API. :(

I stopped getting data from it starting on November 1.
User avatar
oldcomputerguy
Moderator
Posts: 9441
Joined: Sun Nov 22, 2015 6:50 am
Location: In the middle of five acres of woods in East Tennessee

Re: GETYAHOO broken? [Spreadsheet extension]

Post by oldcomputerguy »

madbrain wrote: Sat Jul 15, 2017 6:24 pm
oldcomputerguy wrote:
Hal Branns wrote:Are you still out there and is GETYAHOO still (not)/(working) for you? GETYAHOO quit working for me on approx. 5/21/17. I'm on WIN10 and recently updated to LibreCalc 5.2.7.2 I'm getting so disgusted and would love a simple solution as I auto update 29 stocks several times a week to track some securities we own. Any help out there, here or anywhere?
OP here, and yes, it seems miraculously that GETYAHOO does now work. I'm on LibreOffice 5.0.3.2 under Linux Mint 17.3, and it seems all works.
That's an old version of LibreOffice. Newer ones broke GETYAHOO.
For the record, yes, newer versions of LibreOffice did break the older SMF plugin. However, there is a newer version of the plugin (download link is here, I'm running version 0.8.6) that was working here for me on LibreOffice 5.1.6.2 under Mint 18.2 (at least it was up until a few days ago when Yahoo shut down the API :x ).
"I’ve come around to this: If you’re dumb, surround yourself with smart people; and if you’re smart, surround yourself with smart people who disagree with you." (Aaron Sorkin)
User avatar
Bylo Selhi
Posts: 1179
Joined: Mon Feb 19, 2007 10:40 pm
Location: www.bylo.org in the Great White North
Contact:

Re: Yahoo! Quote Not Working

Post by Bylo Selhi »

Bylo Selhi wrote: Fri Nov 03, 2017 8:52 am
oldcomputerguy wrote: Fri Nov 03, 2017 8:20 am Thanks for the tip. It was in fact a bit easier even that described, I just had to share the published spreadsheet out read-only, then "link to external data" in LibreOffice to a separate tab; this brought the Google sheet over entirely, at which point I just linked between sheets. Voila!
:D
And thanks for the tip re LibreOffice. I'll do that later today. That sounds much easier than downloading quotes manually from Yahoo in CSV, etc. which is what I'd been doing the last couple of days :sharebeer
Some clarification to jhfenton's and oldcomputerguy's posts for anyone who wants to get quotes from GoogleFinance/GoogleSheets into LibreOffice.

Create a new Google Sheet spreadsheet...
• set up the spreadsheet with cells like =GOOGLEFINANCE("NYSEARCA:VTI","price")
• highlight the cells you want to link into LibreOffice
• File -> Publish to the Web...
• select Link, Sheet1 and Webpage [not CSV]
• copy the URL to your clipboard

Now in your LibreOffice spreadsheet...
1. select the target cell
• paste published URL from Google Sheets
• delete all the text starting from "?" so that the URL ends in "pubhtml"
• press Enter
2. Sheet->Link to External Data
• paste the same published URL into the first box, Enter
• use default Import Options, i.e. Automatic, OK
• select HTML_all
• check Update every 60 seconds
• OK

That should paste the Google Sheets cells into your LibreOffice spreadsheet. It should also update the values periodically.

Incidentally if you want to update an existing link to Google Sheets, in LibreOffice, use Edit -> Links... -> Modify, etc.
Last edited by Bylo Selhi on Wed Jan 24, 2018 11:20 am, edited 1 time in total.
User avatar
Bylo Selhi
Posts: 1179
Joined: Mon Feb 19, 2007 10:40 pm
Location: www.bylo.org in the Great White North
Contact:

Re: GETYAHOO broken? [Spreadsheet extension]

Post by Bylo Selhi »

For people who are looking for a replacement for GETYAHOO or the SMF add-in now that Yahoo has killed their interface:

How to get quotes from GoogleFinance/GoogleSheets into LibreOffice.

I imagine something similar will work with Excel. I don't have a copy of that product to test with.
Mudpuppy
Posts: 6450
Joined: Sat Aug 27, 2011 2:26 am
Location: Sunny California

Re: GETYAHOO broken? [Spreadsheet extension]

Post by Mudpuppy »

Here are two other threads on Yahoo's recent and sudden deactivation of the service:

Stand-alone thread (this topic only): viewtopic.php?f=2&t=231355
Restart of another thread (link should take you to this topic): viewtopic.php?f=10&t=218779#p3599921
jasg
Posts: 96
Joined: Wed May 08, 2013 7:10 pm

Re: Yahoo! Quote Not Working

Post by jasg »

goGators wrote: Thu Nov 02, 2017 5:20 pm I tried
http://download.finance.yahoo.com/d/quotes.csv?s=VZ
and got
It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com. :(
Some time ago, GoogleFinance mutual fund daily updates were delayed until 2AM EST (10 hours after market close) and I started using Yahoo. Now, in GoogleSheets, I use this to get prices @8PM EST.

=Index(ImportHtml(Concatenate("http://www.nasdaq.com/symbol/", A1), "table", 0), 2 , 4)

Where A1 is a symbol.

No easy way to get Changes because of the webpage layout at Nasdaq.com. This will get changes as the string : ".18▲0.28%"

=Index(ImportHtml(Concatenate("http://www.nasdaq.com/symbol/",A1), "table", 0),2,5)
bberris
Posts: 1567
Joined: Sun Feb 20, 2011 9:44 am

Re: Anyone else lose ability to download stock prices recently?

Post by bberris »

This doesn't get the dividend, but it does get annual yield so it's just as good:

=IFERROR(INDEX(SPLIT(INDEX(IMPORTHTML("https://www.google.com/finance?q="&A4,"table",3),1,2), "/"),2))

Where A4 references the ticker
azurekep
Posts: 1179
Joined: Tue Jun 16, 2015 7:16 pm

Re: GETYAHOO broken? [Spreadsheet extension]

Post by azurekep »

Some people switched to the Nasdaq website when Yahoo first started fooling around with quotes. (They cover all stocks, not just the ones traded on the Nasdaq exchange.)

This is the link for historical quotes. They can be downloaded as Excel format (and maybe work with Libre Office as well):

http://www.nasdaq.com/symbol/ndaq/historical

For daily quotes, one can use their flash quotes option. Input your stock symbols, separated by commas, and get the updated quotes throughout the day displayed on one page. (I haven't paid enough attention to see if they're delayed by 20 minutes, but there is a real-time option listed as well.)

http://www.nasdaq.com/aspx/flashquotes. ... ected=NDAQ
User avatar
Topic Author
bertilak
Posts: 7915
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

Post by bertilak »

I gave up on YAHOO. My replacement follows.

I rewrote my spreadsheet to use CSV data downloaded directly from Vanguard's web page. This is fine if all, or nearly all, of your investments are in Vanguard, or some other institution that allows a similar download.

Turns out the modification to my spreadsheet was easy since I already had a main page that consolidated things so that the rest of the spreadsheet didn't need to go to YAHOO directly. Things just needed to be consolidated a little differently. The Vanguard data included one extra piece of useful information: The number of shares in each Vanguard account. This was data I had to add and maintain manually when using Yahoo, which only got me quotes.

The one DIFFICULT part was getting the Vanguard data consolidated as it required some fancy formulas I didn't know how to do. What was tricky is that Excel query functions only work on a single key but I needed to match TWO keys (both in the downloaded file): Ticker and Account number. Only the ticker is used to query Yahoo because all it supplies is stock info, not your personal account info.

THIS COMPLEXITY IS ONLY BECAUSE I DECIDED TO DO BETTER THAN YAHOO AND GET MORE THAN JUST THE QUOTE! (shares in each account)

Asking on an Excel forum got me the answer. My Question (greatly edited here to be more specific):
I download a CSV file from vanguard.

I put the table in rows 3 through 100 with 6 columns.
  • Column A is account number
    Column B is asset name
    Column C is ticker
    Column D is number of asset shares in the account
    Column E is quote
    Column F is total value of the asset in the account
Getting a match on a single key is easy. To get the quote of an asset with the ticker matching the value in A1:
  • LOOKUP(A1,C3:C100,E3:E100)
    If A1='IBM' I get the quote (price) for IBM stock. This is all that is needed to replace what YAHOO supplies.
My problem is that each row has TWO key fields (Ticker and Account) and I need to match both. What formula could I use in this multi-key case?
The Answer:
Try this formula to match what is entered in cells A1 and B1 as an array formula by pressing Ctrl+Shift+Enter instead of just Enter when done:
  • =INDEX(D3:D100,MATCH(A1&B1,C3:C100&E3:E100,0))
    This gets me the number of shares in a given account, which the YAHOO API does not supply.
Note that pressing Ctrl+Shift+Enter will wrap the above formula in curl braces to indicate it as an array formula.
The Vanguard CSV data needs to be sorted on two fields Ticker and Account. This is required by the various Excel query functions used above
Last edited by bertilak on Sun Nov 05, 2017 5:27 pm, edited 1 time in total.
May neither drought nor rain nor blizzard disturb the joy juice in your gizzard. -- Squire Omar Barker (aka S.O.B.), the Cowboy Poet
goGators
Posts: 55
Joined: Thu Apr 04, 2013 10:01 pm

Re: Yahoo! Quote Not Working

Post by goGators »

Has anyone tried Alpha Vantage's API?
https://www.alphavantage.co/documentation/
User avatar
jhfenton
Posts: 4665
Joined: Sat Feb 07, 2015 11:17 am
Location: Ohio

Re: Yahoo! Quote Not Working

Post by jhfenton »

goGators wrote: Sun Nov 05, 2017 3:29 pm Has anyone tried Alpha Vantage's API?
https://www.alphavantage.co/documentation/
A couple of us did. It was discussed a few days ago in the other/older Yahoo Finance thread.

It worked, but I had issues with the quality of the data on some securities. VSS (Vanguard FTSE All-World Ex-US Small Cap) in particular had very spotty data. Late morning, it was showing a price 90 minutes old as the last trade. The current price was 15-20 cents lower. By comparison VWO (Vanguard FTSE Emerging Markets), had minute-by-minute updates.

Also, mutual funds aren't updated until the following day.

So I ditched it.
User avatar
runner9
Posts: 2134
Joined: Tue Aug 02, 2011 8:49 pm
Location: Ohio

Re: Anyone else lose ability to download stock prices recently?

Post by runner9 »

bberris wrote: Sun Nov 05, 2017 1:34 pm This doesn't get the dividend, but it does get annual yield so it's just as good:

=IFERROR(INDEX(SPLIT(INDEX(IMPORTHTML("https://www.google.com/finance?q="&A4,"table",3),1,2), "/"),2))

Where A4 references the ticker
Can you please explain, step by step, how this works? I can find IFERROR as a function but can't plug in everything you have after that without getting an error.
goGators
Posts: 55
Joined: Thu Apr 04, 2013 10:01 pm

Re: Yahoo! Quote Not Working

Post by goGators »

A couple of us did. It was discussed a few days ago in the other/older Yahoo Finance thread.

It worked, but I had issues with the quality of the data on some securities. VSS (Vanguard FTSE All-World Ex-US Small Cap) in particular had very spotty data. Late morning, it was showing a price 90 minutes old as the last trade. The current price was 15-20 cents lower. By comparison VWO (Vanguard FTSE Emerging Markets), had minute-by-minute updates.

Also, mutual funds aren't updated until the following day.

So I ditched it.
Thank you for saving me time.
User avatar
LadyGeek
Site Admin
Posts: 67134
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Excel Macro using Yahoo Finance- not working

Post by LadyGeek »

I merged goGators' and rutrow2015's threads into here, which is a similar discussion.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
LadyGeek
Site Admin
Posts: 67134
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Excel Macro using Yahoo Finance- not working

Post by LadyGeek »

Danielle67251's thread is now merged into here. The combined thread is in the Investing - Theory, News & General forum (general question).
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Mudpuppy
Posts: 6450
Joined: Sat Aug 27, 2011 2:26 am
Location: Sunny California

Re: Anyone else lose ability to download stock prices recently?

Post by Mudpuppy »

runner9 wrote: Sun Nov 05, 2017 4:02 pm
bberris wrote: Sun Nov 05, 2017 1:34 pm This doesn't get the dividend, but it does get annual yield so it's just as good:

=IFERROR(INDEX(SPLIT(INDEX(IMPORTHTML("https://www.google.com/finance?q="&A4,"table",3),1,2), "/"),2))

Where A4 references the ticker
Can you please explain, step by step, how this works? I can find IFERROR as a function but can't plug in everything you have after that without getting an error.
Which program are you using? Support varies by program and by version.

But here's a breakdown of the functions, starting from the inside out:

IMPORTHTML(link, "table", 3) - Retrieves a webpage and imports it into an array. Link is built using the ticker symbol in cell A4, as already noted, "table" means the link formats the data in an HTML table, 3 means to select fourth table from the link (the first table is 0, second table is 1, and so on)

INDEX(array, 1, 2) - Selects a value from an array. Array is the result from IMPORTHTML, 1 is the row number, 2 is the column number

SPLIT(value, "/") - Splits up a string based on a delimiter and returns an array. Value is the result from INDEX, "/" is the delimiter

INDEX(array, 2) - Still selects a value from an array. This time, array is the result from split, 2 selects the item out of the array

IFERROR(result) - Only print out the result from above if there is no error
bberris
Posts: 1567
Joined: Sun Feb 20, 2011 9:44 am

Re: Anyone else lose ability to download stock prices recently?

Post by bberris »

Mudpuppy wrote: Sun Nov 05, 2017 5:31 pm
runner9 wrote: Sun Nov 05, 2017 4:02 pm
bberris wrote: Sun Nov 05, 2017 1:34 pm This doesn't get the dividend, but it does get annual yield so it's just as good:

=IFERROR(INDEX(SPLIT(INDEX(IMPORTHTML("https://www.google.com/finance?q="&A4,"table",3),1,2), "/"),2))

Where A4 references the ticker
Can you please explain, step by step, how this works? I can find IFERROR as a function but can't plug in everything you have after that without getting an error.
Which program are you using? Support varies by program and by version.

But here's a breakdown of the functions, starting from the inside out:

IMPORTHTML(link, "table", 3) - Retrieves a webpage and imports it into an array. Link is built using the ticker symbol in cell A4, as already noted, "table" means the link formats the data in an HTML table, 3 means to select fourth table from the link (the first table is 0, second table is 1, and so on)

INDEX(array, 1, 2) - Selects a value from an array. Array is the result from IMPORTHTML, 1 is the row number, 2 is the column number

SPLIT(value, "/") - Splits up a string based on a delimiter and returns an array. Value is the result from INDEX, "/" is the delimiter

INDEX(array, 2) - Still selects a value from an array. This time, array is the result from split, 2 selects the item out of the array

IFERROR(result) - Only print out the result from above if there is no error
I'm using Google Sheets. I just stole the entry from someone. Thanks for the explanation because I didn't have a clue.
So you can get the dividend by changing it to:
=IFERROR(INDEX(SPLIT(INDEX(IMPORTHTML("https://www.google.com/finance?q="&A4,"table",3),1,2), "/"),1))
User avatar
jhfenton
Posts: 4665
Joined: Sat Feb 07, 2015 11:17 am
Location: Ohio

Re: Excel Macro using Yahoo Finance- not working

Post by jhfenton »

The SMF Add-in still works, but you need to use the smfGetYahooPortfolioView function. I added a separate SMF sheet, use smfGetYahooPortfolioView as an array function to pull in data on a list of securities. Then I use vlookup elsewhere in the spreadsheet to pull the info I need from the SMF sheet. He has an example spreadsheet that shows how to use it.

It works very quickly and efficiently and provides the Yahoo Finance data we know and love, including IV tickers for ETFs.

The Google Sheets/Google Finance approach works too. I used that for a day before discovering the SMF Add-in and the smfGetYahooPortfolioView function. The data just wasn't as good as Yahoo's. Mutual funds aren't updated until after 10 PM. And it doesn't include IV tickers for ETFs.
User avatar
Leif
Posts: 3060
Joined: Wed Sep 19, 2007 4:15 pm

Re: Excel Macro using Yahoo Finance- not working

Post by Leif »

jhfenton wrote: Sun Nov 05, 2017 8:15 pm The Google Sheets/Google Finance approach works too. I used that for a day before discovering the SMF Add-in and the smfGetYahooPortfolioView function. The data just wasn't as good as Yahoo's. Mutual funds aren't updated until after 10 PM. And it doesn't include IV tickers for ETFs.
I've updated my spreadsheet for Google Finance. Is that 10 PM Eastern Time for funds update? That would be 7 PM Pacific. That would be OK. I never really knew when Yahoo updated funds, but I found 5 PM Pacific seemed to work.
Last edited by Leif on Mon Nov 06, 2017 12:29 am, edited 1 time in total.
User avatar
tetractys
Posts: 4831
Joined: Sat Mar 17, 2007 3:30 pm
Location: Along the Salish Sea

Re: Excel Macro using Yahoo Finance- not working

Post by tetractys »

I am comfortable relaxing until tomorrow or the next day when all or most of the financial programs will have updated and reinforced the quotes channels. — Tet
User avatar
jhfenton
Posts: 4665
Joined: Sat Feb 07, 2015 11:17 am
Location: Ohio

Re: Excel Macro using Yahoo Finance- not working

Post by jhfenton »

Leif wrote: Sun Nov 05, 2017 11:21 pm
jhfenton wrote: Sun Nov 05, 2017 8:15 pm The Google Sheets/Google Finance approach works too. I used that for a day before discovering the SMF Add-in and the smfGetYahooPortfolioView function. The data just wasn't as good as Yahoo's. Mutual funds aren't updated until after 10 PM. And it doesn't include IV tickers for ETFs.
I've updated my spreadsheet for Google Finance. Is that 10 PM Eastern Time for funds update? That would be 7 PM Pacific. That would be OK. I never really knew when Yahoo updated funds, but I found 5 PM Pacific seemed to work.
Yes. It is sometime after 10 PM ET according to information I found online. I only know that it wasn't updated when I went to bed a little before 10 PM ET. I only used that version of the spreadsheet one evening, so I haven't nailed down the time. If it's after 10 PM ET, it might as well be the next day for me. :beer

Yahoo Finance mutual fund data is usually updated fairly soon after 6 PM ET, so pretty much as soon as Vanguard releases it.
rutrow2015
Posts: 68
Joined: Thu Jan 07, 2016 8:04 am

Re: Excel Macro using Yahoo Finance- not working

Post by rutrow2015 »

This is a follow up to my earlier posts. This is not related to issues with getting historical or market information into a spreadsheet - it is specifically for those of us who have lost the ability to automatically download current stock prices into desktop software packages. In my case it is for Kmymoney but it may also apply to others such as MoneyDance, GNUcash, or even quicken.

On 11/2 the ability to update stock prices via Yahoo's API was shut down permanently by Yahoo. I found a solution posted by "someone smarter than me" here : https://forum.kde.org/viewtopic.php?f=6 ... bd9c1023e7

I was able to create a new quotes profile in Kmymoney using Google Finance API and so far it works flawlessly except for VNQ (I'm not sure why).

Here is the gist of the solution and again this is specific to Kmymoney :

First go to Settings > Configure KMyMoney > Online Quotes > New

Change New Quote Source to Google

In the URL box add https://finance.google.com/finance?q=%1

In the Symbol box add %1

In the Price box add "price"\s+content="((\d+|\d{1,3}(?:[,]\d{3})).\d+)

In the Date box add "quoteTime"\s+content="(\d{4}-\d{2}-\d{2})T

In the Date Format add %y-%m-%d

Check the Skip HTML stripping box

Click Update box

Now go to each stock and change the source to "Google" then you be able to update the stock price.
Mudpuppy
Posts: 6450
Joined: Sat Aug 27, 2011 2:26 am
Location: Sunny California

Re: Excel Macro using Yahoo Finance- not working

Post by Mudpuppy »

rutrow2015 wrote: Mon Nov 06, 2017 9:26 am I was able to create a new quotes profile in Kmymoney using Google Finance API and so far it works flawlessly except for VNQ (I'm not sure why).
For some reason, VNQ defaults to returning a search page instead of the fund page. Try the "BMV:VNQ" instead: https://finance.google.com/finance?q=BMV%3AVNQ
User avatar
LadyGeek
Site Admin
Posts: 67134
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by LadyGeek »

FYI - As requested via PM, I have retitled the thread to better reflect the discussion focus.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
libralibra
Posts: 345
Joined: Sat Jul 30, 2011 2:01 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by libralibra »

post removed
Last edited by libralibra on Sun May 26, 2019 9:57 pm, edited 1 time in total.
Seasonal
Posts: 2141
Joined: Sun May 21, 2017 1:49 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Seasonal »

libralibra wrote: Mon Nov 06, 2017 6:02 pm For people who just want to download a .csv file of the latest prices of a list of tickers, it looks like Yahoo's json api still works, e.g.

http://query1.finance.yahoo.com/v7/fina ... arketPrice

which you can chain thru a site like "powered by json-csv.com"

http://json-csv.com/?u=http://query1.fi ... arketPrice

to get a .csv file. You can save that link as a bookmark or use it as an external link in excel to update your sheets.
Would you know how to get change or % change from the previous close or how to get the previous close?

The url ends with fields=regularMarketPrice. Would you know what other fields might be specified?

FWIW, the regularMarketTime field is seconds since the Unix epoch.

I'd guess the json api works mainly because yahoo doesn't realize it's still active.
acanthurus
Posts: 398
Joined: Sun Aug 04, 2013 8:02 am

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by acanthurus »

Removed
Last edited by acanthurus on Mon Jan 01, 2018 5:32 am, edited 1 time in total.
Pigeye Brewster
Posts: 418
Joined: Thu Oct 05, 2017 7:33 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Pigeye Brewster »

My workaround has been to set up a "My Watchlist" for my mutual funds on Yahoo Finance. It doesn't provide much info other than NAV. But it has an "Export" button which opens up a simple .csv file in Excel. I then copy/paste the NAVs into my main Excel spreadsheet. I tried it about 7:30 EST tonight and it already had today's prices.

Other options tried were a Google Finance spreadsheet and a watchlist on Bloomberg.com. On both of those the prices don't appear to update until much later in the evening.
libralibra
Posts: 345
Joined: Sat Jul 30, 2011 2:01 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by libralibra »

post removed
Last edited by libralibra on Sun May 26, 2019 9:57 pm, edited 1 time in total.
Seasonal
Posts: 2141
Joined: Sun May 21, 2017 1:49 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Seasonal »

libralibra wrote: Tue Nov 07, 2017 1:18 am
Seasonal wrote: Mon Nov 06, 2017 7:00 pm Would you know how to get change or % change from the previous close or how to get the previous close?

The url ends with fields=regularMarketPrice. Would you know what other fields might be specified?
You can just delete the last part and it seems to download all columns that it has. The annoying thing is that it changes the column order each time.

http://json-csv.com/?u=http://query1.fi ... ,amzn,msft
Very nice. Thanks.

An advantage of working in Python is that you can parse json and put fields wherever you want them.
emvestments
Posts: 5
Joined: Fri Nov 03, 2017 8:34 am

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by emvestments »

We worked out a page for Google sheets that seems to work fairly well. I'd like to thank the few folks that sent us in that right direction. If anyone wants me to share my google sheet just send me a gmail address and I will share it: emisriding@gmail.com
goGators
Posts: 55
Joined: Thu Apr 04, 2013 10:01 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by goGators »

I had been using Harry Sit's quote.py (https://thefinancebuff.com/security-quo ... money.html) to download Yahoo quotes into MS Money. Like others, his script stopped working after Yahoo removed the service. When I saw libralibra's post on Yahoo json api,
libralibra wrote: Tue Nov 07, 2017 1:18 am ...
You can just delete the last part and it seems to download all columns that it has. The annoying thing is that it changes the column order each time.

http://json-csv.com/?u=http://query1.fi ... ,amzn,msft
I thought I could modify Harry's script to use this api. After hours of googling and youtubing (since I don't know much about Python) I was able to get the script to work again. The only thing I changed was the getQuote() function in his script.
Here is my modification:

Code: Select all

 def getQuote(self):
        """
        Get name, price quote, and the as-of date and time for the price quote from Yahoo! Finance.
        """
        
        print self.ticker + "............."
        # TFB-url = "http://finance.yahoo.com/d/quotes.csv?s=%s&f=nl1d1t1" % self.ticker
        # TFB-csv = urllib2.urlopen(url).read()
        
        # Use Yahoo's json api to download quote
        url = "http://json-csv.com/?u=http://query1.finance.yahoo.com/v7/finance/quote?symbols=%s" % self.ticker
        # Split into 2 rows: header csv[0] and data csv[1]
        csv = urllib2.urlopen(url).read().splitlines()
        # Since column order changes for each download, need to locate columns for Name, Price, and Time
        nameIdx  = csv[0].split(",").index('"result__shortName"')
        priceIdx = csv[0].split(",").index('"result__regularMarketPrice"')
        timeIdx  = csv[0].split(",").index('"result__regularMarketTime"')
        
        # example: "Amazon.com, Inc.",78.46,"9/3/2009","4:00pm"
        # can't simply use split(",") because the security name has an embedded comma
        # TFB-lexer = shlex.shlex(csv)
        lexer = shlex.shlex(csv[1])
        lexer.whitespace = ","
        lexer.whitespace_split = True

        quote = []
        for value in lexer:
            quote.append(value.strip('"'))
        
        # ampersand character (&) is not valid in OFX
        # TFB-self.name = self._removeIllegalChars(quote[0])
        self.name = self._removeIllegalChars(quote[nameIdx])
        if self.name == "":
        	self.name = self.ticker
        # TFB-self.price = quote[1]
        self.price = quote[priceIdx]
        
        # TFB-timeStruct = time.strptime(quote[2] + " " + quote[3], "%m/%d/%Y %I:%M%p")
        # TFB-self.quoteTime = time.strftime("%Y%m%d%H%M", timeStruct) + "00.000[-5:EST]"
        self.quoteTime = time.strftime("%Y%m%d%H%M", time.localtime(float(quote[timeIdx]))) + "00.000[-5:EST]"
        print self.price
Thanks libralibra for pointing out this interface.
FactualFran
Posts: 1168
Joined: Sat Feb 21, 2015 2:29 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by FactualFran »

libralibra wrote: Mon Nov 06, 2017 6:02 pm For people who just want to download a .csv file of the latest prices of a list of tickers, it looks like Yahoo's json api still works, e.g.

http://query1.finance.yahoo.com/v7/fina ... arketPrice

which you can chain thru a site like "powered by json-csv.com"

http://json-csv.com/?u=http://query1.fi ... arketPrice

to get a .csv file. You can save that link as a bookmark or use it as an external link in excel to update your sheets.
I'll add my "thank you" to the ones that others have posted for the information.

I modified the value of the fields parameter and wrote a small program to extract from the json format what I had previously gotten from the now disabled download.finance.yahoo.com.
User avatar
patrick013
Posts: 3028
Joined: Mon Jul 13, 2015 7:49 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by patrick013 »

https://myphotos.mypclinuxos.com/images ... krover.png

Here's a hypothetical portfolio I did on stockrover.com with user made
report. Faster than GoogleSheets and more features. So far so good.
age in bonds, buy-and-hold, 10 year business cycle
Chip
Posts: 3100
Joined: Wed Feb 21, 2007 4:57 am

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Chip »

Pigeye Brewster wrote: Mon Nov 06, 2017 8:58 pm My workaround has been to set up a "My Watchlist" for my mutual funds on Yahoo Finance. It doesn't provide much info other than NAV. But it has an "Export" button which opens up a simple .csv file in Excel. I then copy/paste the NAVs into my main Excel spreadsheet. I tried it about 7:30 EST tonight and it already had today's prices.
I have been doing this exact thing successfully for years. I'm probably missing something, but it seems a lot simpler than what most of the posters in this thread seem to be going through.

The steps I go through are:

1. Navigate to the Yahoo portfolio page.
2. Right click on Export link. Choose "Save Link As..." Confirm filename as "quotes.csv".
3. Open quotes.csv in Excel. Open asset allocation spreadsheet in Excel.
4. Copy quotes.csv data to clipboard.
5. Paste data into quotes tab in asset allocation spreadsheet.
6. VLOOKUP formulas in other tabs of allocation spreadsheet automatically pick up the new values.
7. Save file, quit.

I suppose instead of the copying/pasting I could just set up external links to the .csv file, but this is simple enough.

It took me five times as long to write that as it does to actually execute it. What am I missing?
User avatar
Bylo Selhi
Posts: 1179
Joined: Mon Feb 19, 2007 10:40 pm
Location: www.bylo.org in the Great White North
Contact:

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Bylo Selhi »

Chip wrote: Thu Nov 09, 2017 4:47 amWhat am I missing?
Using Google Finance and Google Sheets you can create a live link to your Excel or LibreOffice spreadsheet. That means that quotes get updated dynamically during the trading day at some interval like every minute. Once set up there's no need to download/import .CSV files every time you want to see new quotes.
Chip
Posts: 3100
Joined: Wed Feb 21, 2007 4:57 am

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Chip »

Bylo Selhi wrote: Thu Nov 09, 2017 7:25 am Using Google Finance and Google Sheets you can create a live link to your Excel or LibreOffice spreadsheet. That means that quotes get updated dynamically during the trading day at some interval like every minute. Once set up there's no need to download/import .CSV files every time you want to see new quotes.
Thanks, that's very helpful. I only update my spreadsheet once a week, hence my confusion. If I want to see what's going on during the day I just navigate to the Yahoo portfolio and take a look.

Different strokes for different folks...
User avatar
Bylo Selhi
Posts: 1179
Joined: Mon Feb 19, 2007 10:40 pm
Location: www.bylo.org in the Great White North
Contact:

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Bylo Selhi »

Chip wrote: Thu Nov 09, 2017 7:48 amI only update my spreadsheet once a week, hence my confusion. If I want to see what's going on during the day I just navigate to the Yahoo portfolio and take a look.

Different strokes for different folks...
Agreed. I don't need to see dynamically updating quotes either. But the geek in me enjoyed setting up the linkage far more than watching the result ;)
Pigeye Brewster
Posts: 418
Joined: Thu Oct 05, 2017 7:33 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Pigeye Brewster »

Chip wrote: Thu Nov 09, 2017 4:47 am
Pigeye Brewster wrote: Mon Nov 06, 2017 8:58 pm My workaround has been to set up a "My Watchlist" for my mutual funds on Yahoo Finance. It doesn't provide much info other than NAV. But it has an "Export" button which opens up a simple .csv file in Excel. I then copy/paste the NAVs into my main Excel spreadsheet. I tried it about 7:30 EST tonight and it already had today's prices.
I have been doing this exact thing successfully for years. I'm probably missing something, but it seems a lot simpler than what most of the posters in this thread seem to be going through.

The steps I go through are:

1. Navigate to the Yahoo portfolio page.
2. Right click on Export link. Choose "Save Link As..." Confirm filename as "quotes.csv".
3. Open quotes.csv in Excel. Open asset allocation spreadsheet in Excel.
4. Copy quotes.csv data to clipboard.
5. Paste data into quotes tab in asset allocation spreadsheet.
6. VLOOKUP formulas in other tabs of allocation spreadsheet automatically pick up the new values.
7. Save file, quit.

I suppose instead of the copying/pasting I could just set up external links to the .csv file, but this is simple enough.

It took me five times as long to write that as it does to actually execute it. What am I missing?
Very similar to my workaround process, except I simply "Open" for the "quotes.csv" file rather than "Save Link As..." It opens the file automatically in Excel, combining your #2 and #3. I also use INDEX/MATCH to pull in the pasted values rather than VLOOKUP, but that's not a substantive difference.

Simple and effective.

What are you missing? The frustration you get when the Yahoo Finance .api quits working and the VBA Excel add-in you spent time on starts giving you error messages. :beer
Chip
Posts: 3100
Joined: Wed Feb 21, 2007 4:57 am

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Chip »

Pigeye Brewster wrote: Thu Nov 09, 2017 9:37 am Very similar to my workaround process, except I simply "Open" for the "quotes.csv" file rather than "Save Link As..." It opens the file automatically in Excel, combining your #2 and #3. I also use INDEX/MATCH to pull in the pasted values rather than VLOOKUP, but that's not a substantive difference.

Simple and effective.

What are you missing? The frustration you get when the Yahoo Finance .api quits working and the VBA Excel add-in you spent time on starts giving you error messages. :beer
I would do what you're doing except I have an additional use for the .csv file. I run it through some code I wrote to produce an input file for an ancient piece of personal finance software. Occasionally Yahoo messes with the precise format of the .csv, breaking my software. So I get to experience at least some of the frustration. :D
Seasonal
Posts: 2141
Joined: Sun May 21, 2017 1:49 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Seasonal »

Chip wrote: Thu Nov 09, 2017 4:47 amWhat am I missing?
I no longer have a download or export link on my Yahoo portfolio page. The link I was using to get a csv file stopped working last week.

What url are you using to view your portfolio?
User avatar
Leif
Posts: 3060
Joined: Wed Sep 19, 2007 4:15 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Leif »

Chip wrote: Thu Nov 09, 2017 4:47 am
The steps I go through are:

1. Navigate to the Yahoo portfolio page.
2. Right click on Export link. Choose "Save Link As..." Confirm filename as "quotes.csv".
3. Open quotes.csv in Excel. Open asset allocation spreadsheet in Excel.
4. Copy quotes.csv data to clipboard.
5. Paste data into quotes tab in asset allocation spreadsheet.
6. VLOOKUP formulas in other tabs of allocation spreadsheet automatically pick up the new values.
7. Save file, quit.

I suppose instead of the copying/pasting I could just set up external links to the .csv file, but this is simple enough.

It took me five times as long to write that as it does to actually execute it. What am I missing?
My steps are:
1. Open the Excel spreadsheet.
2. Click a button.
3. Save file, quit.

But I will say I'm familiar with programming.

Since Yahoo quotes failed I've reprogrammed it to use Google Finance. The drawback is that Google is very late in making their mutual fund quotes available. So I need to wait to the next day to get the previous day's values. A bit annoying I will admit.

If anyone has a programmatic way to get CSV quotes from Yahoo using My Watch List, I hope you can share. All I need to the ticker and the NAV at close. I don't need intraday quotes.
Chip
Posts: 3100
Joined: Wed Feb 21, 2007 4:57 am

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Chip »

Seasonal wrote: Thu Nov 09, 2017 11:28 am I no longer have a download or export link on my Yahoo portfolio page. The link I was using to get a csv file stopped working last week.

What url are you using to view your portfolio?
https://finance.yahoo.com/portfolio/pf_5/view/v1

I think the pf_5 means it's the 5th portfolio on Yahoo I've created. Yours might be pf_1 or something else. The export link is above the portfolio on the right, next to the Settings gear.
Pigeye Brewster
Posts: 418
Joined: Thu Oct 05, 2017 7:33 pm

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by Pigeye Brewster »

Leif wrote: Thu Nov 09, 2017 11:48 am [My steps are:
1. Open the Excel spreadsheet.
2. Click a button.
3. Save file, quit.

But I will say I'm familiar with programming.

Since Yahoo quotes failed I've reprogrammed it to use Google Finance. The drawback is that Google is very late in making their mutual fund quotes available. So I need to wait to the next day to get the previous day's values. A bit annoying I will admit.

If anyone has a programmatic way to get CSV quotes from Yahoo using My Watch List, I hope you can share. All I need to the ticker and the NAV at close. I don't need intraday quotes.
I went the Google Finance route first. They are indeed very late on mutual fund quotes and those are all I need. That's why I went with the Yahoo workaround. I tested it the other night and the current day price was updated as of 5:45 pm EST.
User avatar
dratkinson
Posts: 5077
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: [Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by dratkinson »

Pigeye Brewster wrote: Thu Nov 09, 2017 9:37 am
Chip wrote: Thu Nov 09, 2017 4:47 am
Pigeye Brewster wrote: Mon Nov 06, 2017 8:58 pm My workaround has been to set up a "My Watchlist" for my mutual funds on Yahoo Finance. It doesn't provide much info other than NAV. But it has an "Export" button which opens up a simple .csv file in Excel. I then copy/paste the NAVs into my main Excel spreadsheet. I tried it about 7:30 EST tonight and it already had today's prices.
I have been doing this exact thing successfully for years. I'm probably missing something, but it seems a lot simpler than what most of the posters in this thread seem to be going through.

The steps I go through are:

1. Navigate to the Yahoo portfolio page.
2. Right click on Export link. Choose "Save Link As..." Confirm filename as "quotes.csv".
3. Open quotes.csv in Excel. Open asset allocation spreadsheet in Excel.
4. Copy quotes.csv data to clipboard.
5. Paste data into quotes tab in asset allocation spreadsheet.
6. VLOOKUP formulas in other tabs of allocation spreadsheet automatically pick up the new values.
7. Save file, quit.

I suppose instead of the copying/pasting I could just set up external links to the .csv file, but this is simple enough.

It took me five times as long to write that as it does to actually execute it. What am I missing?
Very similar to my workaround process, except I simply "Open" for the "quotes.csv" file rather than "Save Link As..." It opens the file automatically in Excel, combining your #2 and #3. I also use INDEX/MATCH to pull in the pasted values rather than VLOOKUP, but that's not a substantive difference.

Simple and effective.

What are you missing? The frustration you get when the Yahoo Finance .api quits working and the VBA Excel add-in you spent time on starts giving you error messages. :beer
To avoid the .csv export step, can try---mark range | copy | paste special | text---into Excel. Pasting as text preserves current Excel formatting.

That's what I do (ino.com saved portfolio) to keep my old PC working.

(Added) I did this on my Excel price data sheet to streamline the process. Wrote a 3-step list of instructions.
--Click: "1: Open URL". (Webpage link to ino.com portfolio opens in new browser tab.)
--"2: Copy portfolio data range." (Instruction to remind me I need to do this step manually.)
--Click: "3: Paste portfolio text" (Excel button linked to a recorded macro. Macro position cursor to top-left cell of paste destination, then paste-special-text into Excel.)

Missing Date information. Ino.com does not provide current date information with its price information, and I was using that. To avoid having to manually enter the date (which I was doing), I rerecorded above macro to append these extra steps.
--Copy hidden cell (in column of INO data that I didn't need, so hid) containing Excel function: =today().
--Paste-special-values (today's date) into my visible Date cell.


Bottom line. It's not a terribly intrusive process: click two links and manually mark one data range.

The INO data seems to updated by 6:00 PM EST, same as Gummy's Yahoo data.
Last edited by dratkinson on Thu Nov 09, 2017 8:05 pm, edited 1 time in total.
d.r.a., not dr.a. | I'm a novice investor, you are forewarned.
Post Reply