[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.
User avatar
Leif
Posts: 2189
Joined: Wed Sep 19, 2007 4:15 pm

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

Post by Leif » Thu Nov 09, 2017 7:12 pm

Pigeye Brewster wrote:
Thu Nov 09, 2017 3:38 pm
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.
For me I need this to be super simply, or I will not do it. No copy/paste for me. If I cannot get a workaround like I had with the old Yahoo process (just press a button to update my quotes) I'll just live with the day old quotes from Google.
Investors should diversify across many asset-classes so that whatever happens, we will not have all our investments in underperforming asset classes and thereby fail to meet our goals-Taylor Larimore

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

How to Get Stock Quotes Into Excel

Post by Leesbro63 » Fri Nov 10, 2017 12:43 pm

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

What's happeneing with the Yahoo stock quotes into Excel thing. I've used the following command that's worked well. But it's been dead for a few weeks now. What's the EASIEST way to get end of day quotes into my Excel spreadsheet?

=NUMBERVALUE(WEBSERVICE("https://download.finance.yahoo.com/d/qu ... s=VTI&f=l1"))

lt1948
Posts: 12
Joined: Mon Sep 18, 2017 9:33 am
Location: Madison, WI
Contact:

Re: How to Get Stock Quotes Into Excel

Post by lt1948 » Fri Nov 10, 2017 12:48 pm

Here is a uTube video on how to get the quotes in xls - https://goo.gl/SyeJao

TravelforFun
Posts: 847
Joined: Tue Dec 04, 2012 11:05 pm

Re: How to Get Stock Quotes Into Excel

Post by TravelforFun » Fri Nov 10, 2017 12:57 pm

It died on 11/1. I now get my stock quotes like this:

- set up a portfolio of all my stocks in Yahoo Finance and bookmark it

- every time I open the bookmarked page, I see a list of my stocks, prices, volumes, etc.

- click download to excel, an excel page opens with all my stock quotes

- copy and paste onto my spreadsheet.

Not very easy or convenient but it beats manually entering the quotes.

TravelforFun

np81
Posts: 144
Joined: Thu Dec 26, 2013 3:11 pm

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

Post by np81 » Fri Nov 10, 2017 1:24 pm

If you don't want to deal with macros, and are just looking for a basic ticker lookup (in my case, the current price), you only need two things:
  • An API key from AlphaVantage
  • This Excel query:

    Code: Select all

    =VALUE(MID(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))+1,FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),10))-FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))-1))
A1 being the cell that contains your ticker symbol, and YourKeyGoesHere being your AphaVantage API key

Is it ugly? Yep. Does it work? Yep.

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

Re: How to Get Stock Quotes Into Excel

Post by Leesbro63 » Fri Nov 10, 2017 1:25 pm

lt1948 wrote:
Fri Nov 10, 2017 12:48 pm
Here is a uTube video on how to get the quotes in xls - https://goo.gl/SyeJao
Sadly, this relies on YAHOO which, apparently, shut down quotes on Nov 1.

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

Re: How to Get Stock Quotes Into Excel

Post by Leesbro63 » Fri Nov 10, 2017 1:25 pm

TravelforFun wrote:
Fri Nov 10, 2017 12:57 pm
It died on 11/1. I now get my stock quotes like this:

- set up a portfolio of all my stocks in Yahoo Finance and bookmark it

- every time I open the bookmarked page, I see a list of my stocks, prices, volumes, etc.

- click download to excel, an excel page opens with all my stock quotes

- copy and paste onto my spreadsheet.

Not very easy or convenient but it beats manually entering the quotes.

TravelforFun
This is actually a good work around, at least until I can find a free source of quotes directly into Excel. Thank you so much!

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

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

Post by Leesbro63 » Fri Nov 10, 2017 1:29 pm

np81 wrote:
Fri Nov 10, 2017 1:24 pm
If you don't want to deal with macros, and are just looking for a basic ticker lookup (in my case, the current price), you only need two things:
  • An API key from AlphaVantage
  • This Excel query:

    Code: Select all

    =VALUE(MID(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))+1,FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),10))-FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))-1))
A1 being the cell that contains your ticker symbol, and YourKeyGoesHere being your AphaVantage API key

Is it ugly? Yep. Does it work? Yep.
What's an API key and how does one get one?

np81
Posts: 144
Joined: Thu Dec 26, 2013 3:11 pm

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

Post by np81 » Fri Nov 10, 2017 1:33 pm

Leesbro63 wrote:
Fri Nov 10, 2017 1:29 pm
What's an API key and how does one get one?
It's a free access key that allows you to query AlphaVantage's web service. You can sign up here: https://www.alphavantage.co/support/#api-key

Pigeye Brewster
Posts: 33
Joined: Thu Oct 05, 2017 7:33 pm

Re: How to Get Stock Quotes Into Excel

Post by Pigeye Brewster » Fri Nov 10, 2017 1:38 pm

Leesbro63 wrote:
Fri Nov 10, 2017 1:25 pm
TravelforFun wrote:
Fri Nov 10, 2017 12:57 pm
It died on 11/1. I now get my stock quotes like this:

- set up a portfolio of all my stocks in Yahoo Finance and bookmark it

- every time I open the bookmarked page, I see a list of my stocks, prices, volumes, etc.

- click download to excel, an excel page opens with all my stock quotes

- copy and paste onto my spreadsheet.

Not very easy or convenient but it beats manually entering the quotes.

TravelforFun
This is actually a good work around, at least until I can find a free source of quotes directly into Excel. Thank you so much!
I'm doing this as well. I also tried Google Finance, but Yahoo Finance has mutual fund NAVs much sooner.

Nate79
Posts: 1358
Joined: Thu Aug 11, 2016 6:24 pm
Location: Portland, OR

Re: How to Get Stock Quotes Into Excel

Post by Nate79 » Fri Nov 10, 2017 1:44 pm

Why not use google sheets? It's super easy to get stock quotes in google sheets.

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

Re: How to Get Stock Quotes Into Excel

Post by Leesbro63 » Fri Nov 10, 2017 1:48 pm

Nate79 wrote:
Fri Nov 10, 2017 1:44 pm
Why not use google sheets? It's super easy to get stock quotes in google sheets.
Because, truthfully, I don't understand Google Sheets. Do I have to use Google Docs? It sounds cumbersome!

bradpitch92
Posts: 2
Joined: Fri Nov 10, 2017 1:50 pm

Re: How to Get Stock Quotes Into Excel

Post by bradpitch92 » Fri Nov 10, 2017 1:52 pm

How do you get stock quotes into google sheets? I love google sheets

pshonore
Posts: 5663
Joined: Sun Jun 28, 2009 2:21 pm

Re: How to Get Stock Quotes Into Excel

Post by pshonore » Fri Nov 10, 2017 1:53 pm

I created a Google sheet with two columns, symbols for my holdings in one column, and the Google Price lookup function (using the adjacent symbol) in the other.

I then select the "Price" column, copy it and paste into my Excel sheet in the column where Yahoo prices were. You can do that anytime throughout the day or when the market closes

Anyone know how often Google updates prices or if it can be forced?

ocrtech
Posts: 2
Joined: Sat Jul 21, 2012 2:18 pm

Re: How to Get Stock Quotes Into Excel

Post by ocrtech » Fri Nov 10, 2017 2:13 pm

I created\adopted some VBA code to collect the last price, yield, and expenses information from Morningstar.

Assuming you have the developer tab enabled in excel, you can add the below code to the default module for the spreadsheet. Once you have it copied, you use it in your spreadsheet by selecting the cell where you want the price to appear and enter:

=MorningstarPrice("IBM")

where IBM is the stock , etf, or mutual fund symbol you want the price for.

If you have auto calculate turned on, it will update the price every time you open your spreadsheet.

Public IE As New InternetExplorer
Public Function MorningstarPrice(szSymbol As String) As Single
MorningstarPrice = MorningstarData(szSymbol, "NAV")
End Function
Public Function MorningstarYield(szSymbol As String) As Single
MorningstarYield = MorningstarData(szSymbol, "12-Mo. Yield")
End Function
Public Function MorningstarExpense(szSymbol As String) As Single
MorningstarExpense = MorningstarData(szSymbol, "Expenses")
End Function

Private Function MorningstarData(szSymbol As String, szDataID As String) As Single
'Dim IE As New InternetExplorer
Dim doc As HTMLDocument
Dim finalout As Variant
Dim finalobj As Object

On Error GoTo ReportError ' set the error handler

If IE Is Nothing Then
Set IE = CreateObject("InternetExplorer.Application")
End If
IE.navigate "etfs.morningstar.com/quote-banner?&t=" & szSymbol, 4
Application.Wait DateAdd("s", WaitTime, Now())
Do While IE.Busy: DoEvents: Loop
Do While IE.readyState <> READYSTATE_COMPLETE: DoEvents: Loop
Application.Wait DateAdd("s", WaitTime, Now())

Set doc = IE.document 'grab the document object from the browser object

' if we are dealing with price
If szDataID = "NAV" Then
'Set finalobj = doc.getElementById("IIV_lastPrice") ' Every 15 minute updated NAV value for ETF
Set finalobj = doc.getElementById("lastPrice") ' Last trade price
If Not finalobj Is Nothing Then
finalout = finalobj.innerText
End If
End If

If finalobj Is Nothing Then
Set valElements = doc.getElementsByTagName("td")
For Each oElement In valElements
If oElement.Children.Length >= 2 Then
If oElement.Children(0).innerText = szDataID Then
finalout = oElement.Children(1).innerText
Exit For
End If
End If
Next
End If
MorningstarData = CSng(Replace(finalout, "%", "")) ' set the price return value
On Error GoTo 0 'reset the error handler
Exit Function ' return to the spreadsheet with the value

ReportError:
On Error GoTo 0 'reset the error handler

End Function

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

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

Post by Leesbro63 » Fri Nov 10, 2017 2:14 pm

Leesbro63 wrote:
Fri Nov 10, 2017 1:29 pm
np81 wrote:
Fri Nov 10, 2017 1:24 pm
If you don't want to deal with macros, and are just looking for a basic ticker lookup (in my case, the current price), you only need two things:
  • An API key from AlphaVantage
  • This Excel query:

    Code: Select all

    =VALUE(MID(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))+1,FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),10))-FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))-1))
A1 being the cell that contains your ticker symbol, and YourKeyGoesHere being your AphaVantage API key

Is it ugly? Yep. Does it work? Yep.
What's an API key and how does one get one?
I'm FAIRLY adept at Excel and computer stuff. But I'm really not sure what to do, above. Does my key (I did get one) go in there 4 times? I get that cell A1 is where to put your security symbol. Is this for ONE current price? Or this this multiple different types of prices? I just want the "right now" price.

Milo
Posts: 223
Joined: Thu Dec 27, 2007 7:24 pm

Re: How to Get Stock Quotes Into Excel

Post by Milo » Fri Nov 10, 2017 2:40 pm

I use an app that I got from the Office Store called "Stock Connector". It is currently down because of the Yahoo Finance thing. But the author has left a note saying he's found an alternate source and will incorporate it into his app shortly.

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

Re: How to Get Stock Quotes Into Excel

Post by Leesbro63 » Fri Nov 10, 2017 2:40 pm

Milo wrote:
Fri Nov 10, 2017 2:40 pm
I use an app that I got from the Office Store called "Stock Connector". It is currently down because of the Yahoo Finance thing. But the author has left a note saying he's found an alternate source and will incorporate it into his app shortly.
I saw that. I hope "shortly" isn't "longly" ;)

bobandsherry
Posts: 23
Joined: Sun Feb 05, 2017 10:07 pm

Re: How to Get Stock Quotes Into Excel

Post by bobandsherry » Fri Nov 10, 2017 3:01 pm

Leesbro63 wrote:
Fri Nov 10, 2017 1:48 pm
Nate79 wrote:
Fri Nov 10, 2017 1:44 pm
Why not use google sheets? It's super easy to get stock quotes in google sheets.
Because, truthfully, I don't understand Google Sheets. Do I have to use Google Docs? It sounds cumbersome!
If you can use Excel you shouldn't have any problem using Google Sheets. You'll need a Google account, but after that it's pretty simple.

Nate79
Posts: 1358
Joined: Thu Aug 11, 2016 6:24 pm
Location: Portland, OR

Re: How to Get Stock Quotes Into Excel

Post by Nate79 » Fri Nov 10, 2017 3:05 pm

bradpitch92 wrote:
Fri Nov 10, 2017 1:52 pm
How do you get stock quotes into google sheets? I love google sheets
Here is an example for what to put in google sheet formula:

=googlefinance("VTSAX", "price")

EHEngineer
Posts: 613
Joined: Sat Feb 28, 2015 4:35 pm

Re: How to Get Stock Quotes Into Excel

Post by EHEngineer » Fri Nov 10, 2017 3:21 pm

TravelforFun wrote:
Fri Nov 10, 2017 12:57 pm
It died on 11/1. I now get my stock quotes like this:

- set up a portfolio of all my stocks in Yahoo Finance and bookmark it

- every time I open the bookmarked page, I see a list of my stocks, prices, volumes, etc.

- click download to excel, an excel page opens with all my stock quotes

- copy and paste onto my spreadsheet.

Not very easy or convenient but it beats manually entering the quotes.

TravelforFun
If you use google sheets, you can put a link to the quotes in your excel spreadsheet, then your 4 steps become 2.

1)click link
2)copy/paste into spreadsheet

Here's a sample I made for another thread.
https://docs.google.com/spreadsheets/d/ ... g-/pubhtml
Or, you can ... decline to let me, a stranger on the Internet, egg you on to an exercise in time-wasting, and you could say "I'm probably OK and I don't care about it that much." -Nisiprius

User avatar
midareff
Posts: 5044
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: How to Get Stock Quotes Into Excel

Post by midareff » Fri Nov 10, 2017 3:24 pm

Leesbro63 wrote:
Fri Nov 10, 2017 12:43 pm
What's happeneing with the Yahoo stock quotes into Excel thing. I've used the following command that's worked well. But it's been dead for a few weeks now. What's the EASIEST way to get end of day quotes into my Excel spreadsheet?

=NUMBERVALUE(WEBSERVICE("https://download.finance.yahoo.com/d/qu ... s=VTI&f=l1"))
Set up your stocks in Morningstar and then export it. Tie the prices on your excel sheet to the exported excel and that's it, open and update.

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

Re: How to Get Stock Quotes Into Excel

Post by Leesbro63 » Fri Nov 10, 2017 3:30 pm

EHEngineer wrote:
Fri Nov 10, 2017 3:21 pm
TravelforFun wrote:
Fri Nov 10, 2017 12:57 pm
It died on 11/1. I now get my stock quotes like this:

- set up a portfolio of all my stocks in Yahoo Finance and bookmark it

- every time I open the bookmarked page, I see a list of my stocks, prices, volumes, etc.

- click download to excel, an excel page opens with all my stock quotes

- copy and paste onto my spreadsheet.

Not very easy or convenient but it beats manually entering the quotes.

TravelforFun
If you use google sheets, you can put a link to the quotes in your excel spreadsheet, then your 4 steps become 2.

1)click link
2)copy/paste into spreadsheet

Here's a sample I made for another thread.
https://docs.google.com/spreadsheets/d/ ... g-/pubhtml
This was EXTREMELY useful to me! Works even better than the old Yahoo Finance thing! Thank you for solving my issue!

np81
Posts: 144
Joined: Thu Dec 26, 2013 3:11 pm

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

Post by np81 » Fri Nov 10, 2017 3:32 pm

Yes, you're putting your key in there 4 times, on a single symbol. It's basically counting the commas to get to the ticker price. I could not get the API to work using TIME_SERIES_INTRADAY, as it repeatedly threw errors, so I resorted to TIME_SERIES_DAILY, which returns the previous days' quote. For me, this is perfectly acceptable.

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

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

Post by Leesbro63 » Fri Nov 10, 2017 3:45 pm

np81 wrote:
Fri Nov 10, 2017 3:32 pm
Yes, you're putting your key in there 4 times, on a single symbol. It's basically counting the commas to get to the ticker price. I could not get the API to work using TIME_SERIES_INTRADAY, as it repeatedly threw errors, so I resorted to TIME_SERIES_DAILY, which returns the previous days' quote. For me, this is perfectly acceptable.
Thank you so much for trying. This is over my head. I was able to make this work with Google Sheets.

EHEngineer
Posts: 613
Joined: Sat Feb 28, 2015 4:35 pm

Re: How to Get Stock Quotes Into Excel

Post by EHEngineer » Sat Nov 11, 2017 11:28 am

Glad it helped. For posterity, here's the other thread.
viewtopic.php?f=10&t=203373

edit: That thread is now this thread as noted by LadyGeek in the post below.
Last edited by EHEngineer on Sun Nov 12, 2017 11:16 am, edited 1 time in total.
Or, you can ... decline to let me, a stranger on the Internet, egg you on to an exercise in time-wasting, and you could say "I'm probably OK and I don't care about it that much." -Nisiprius

User avatar
LadyGeek
Site Admin
Posts: 41129
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Download quotes to excel spreadsheet

Post by LadyGeek » Sat Nov 11, 2017 8:03 pm

^^^ The link no longer exists.

I merged bertilak's 2016 thread into here, which is a similar discussion. The software sorts by time, so it appears first in the thread.

Update: Leesbro63's thread is also merged into here. The combined thread is in the Investing - Theory, News & General forum.
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.

DistractedByData
Posts: 2
Joined: Thu Nov 02, 2017 9:57 pm

Re: Excel Macro using Yahoo Finance- not working

Post by DistractedByData » Sun Nov 12, 2017 11:06 am

libralibra wrote:
Sat Nov 04, 2017 4:40 pm
DistractedByData wrote:
Thu Nov 02, 2017 10:47 pm
This simple URL will retrieve multiple stock symbols, last price, last order size, and a time stamp:
https://api.iextrading.com/1.0/tops/las ... format=csv
(just substitute your own stocks instead of VTI, VXUS, or BND)
...
One limitation -- IEX doesn't seem to have any mutual fund tickers, and these are IEX prices. But there is a ton of useful stock data available.
This link works just like the download.yahoo link. But the prices are slightly off from the real closing prices. Why wouldn't IEX data be the same as the official exchanges?
Sorry, I was traveling and this thread kind of forked in all sorts of different ways while I wasn't paying attention.

IEX is a different exchange so the prices may be slightly different. In theory, IEX is supposed to be more transparent and investor-friendly than the other exchanges. I thought it was cool how IEX let you look at all their market data for free with a public API. Just out of curiosity, I'll poke around and see how much the IEX closing prices vary from the "official" ones.

This thread got complicated fast. Might be nice if we could summarize the available methods that work. For now, I settled on the Google Sheets / Google Finance technique and I copy stock prices from a Google sheet document into my local master Excel spreadsheet. The Google sheet uses the formula =GOOGLEFINANCE(A1, "price") where A1 is the location of the desired ticker symbol. The Google doc is bookmarked and I open it in a web browser while I work on the Excel sheet. Simple copy & paste works for me because I don't have many prices that need updating and I want to avoid macros and embedded code. The whole thing takes 15 seconds. YMMV.

P.S. I am playing around with the IEX API and the Yahoo json approaches using some R scripting, but that's just for fun. I keep my real records simple and understandable by non-experts. Someday I may even achieve three-fund nirvana...

User avatar
Bylo Selhi
Posts: 1020
Joined: Mon Feb 19, 2007 10:40 pm
Location: www.bylo.org in the Great White North
Contact:

Re: Excel Macro using Yahoo Finance- not working

Post by Bylo Selhi » Sun Nov 12, 2017 1:22 pm

DistractedByData wrote:
Sun Nov 12, 2017 11:06 am
This thread got complicated fast. Might be nice if we could summarize the available methods that work. For now, I settled on the Google Sheets / Google Finance technique and I copy stock prices from a Google sheet document into my local master Excel spreadsheet. The Google sheet uses the formula =GOOGLEFINANCE(A1, "price") where A1 is the location of the desired ticker symbol. The Google doc is bookmarked and I open it in a web browser while I work on the Excel sheet. Simple copy & paste works for me because I don't have many prices that need updating and I want to avoid macros and embedded code. The whole thing takes 15 seconds. YMMV.
I summarized a similar approach using Google Sheets and Google Finance but linking into a LibreOffice spreadsheet upthread. The way I'm doing it, the link to Google from LO gets refreshed periodically. So during trading hours stock and FX values get updated automatically. There's the usual end-of-day delay for mutual fund prices. This works faster and more smoothly than the GETYAHOO mechanism it replaces.

It took a bit of experimentation to get everything to work. So to spare others from that experience, the link provides step-by-step instructions.

Individual securities prices, stocks and ETFs, are identical to Quicken's. End-of-day bottom line portfolio values do differ after five significant digits. I attribute that to software rounding errors. My conclusion is that Quicken uses the same data source as Google Finance.

LK2012
Posts: 169
Joined: Mon Apr 30, 2012 4:42 pm

Re: How to Get Stock Quotes Into Excel

Post by LK2012 » Sun Nov 12, 2017 2:40 pm

TravelforFun wrote:
Fri Nov 10, 2017 12:57 pm
It died on 11/1. I now get my stock quotes like this:

- set up a portfolio of all my stocks in Yahoo Finance and bookmark it

- every time I open the bookmarked page, I see a list of my stocks, prices, volumes, etc.

- click download to excel, an excel page opens with all my stock quotes

- copy and paste onto my spreadsheet.

Not very easy or convenient but it beats manually entering the quotes.

TravelforFun
Thank you! I just switched over to this method. It took me a few hours to link all the pages of my Excel spreadsheet to a Master worksheet that I added to hold the exported Yahoo Quotes from my newly constructed Watchlist (of tickers in my spreadsheet).

But it is now working wonderfully!

I've already been through the change from MSN Money to Yahoo SMF Add-in, and now this ... very nice to have a workaround so quickly.

One issue I ran into occurred when I was deleting the previous Yahoo SMF Add-in "Array" areas, so I could plug in new formulas linking to my Master quote list. Once in a while the "array" from the SMF Add-in was not matching my highlighted area and I was getting the dreaded "you can't change an Array" message. Then I re-discovered the method of clicking a cell in the Array, then hitting Ctrl +/ and that allowed me to see the arrays and remove them.

Then I just plugged in new formulas to link to the Master List, and voila!

Yes, there are the extra steps of exporting the .cvs file and then copy/paste into my Master worksheet, but for end-of-the-day or less frequent check-ins, this should work just fine.

If I want to check things more often, I'll look at Morningstar portfolios.

Thanks again!

User avatar
jhfenton
Posts: 2184
Joined: Sat Feb 07, 2015 11:17 am
Location: Ohio

Re: How to Get Stock Quotes Into Excel

Post by jhfenton » Sun Nov 12, 2017 2:49 pm

LK2012 wrote:
Sun Nov 12, 2017 2:40 pm
I've already been through the change from MSN Money to Yahoo SMF Add-in, and now this ... very nice to have a workaround so quickly.
I just switched TO the SMF Add-in. I had never used it until a week ago. The smfGetYahooPortfolioView still works to load prices and price changes (and ~90 other fields if you want), and works much faster than the ticker by ticker Excel webservice to Yahoo functions that I used to use.

The quirks of Excel and add-ins meant that I had to add a VB script that literally unloads and loads the add-in upon opening a spreadsheet and whenever I click the refresh button, but everything loads quickly.

And the Yahoo data is still more complete and up-to-date than any other source I've seen. I briefly used the Google Sheets/Google Finance work-around when the straight-up Yahoo Finance API disappeared, but the data was incomplete and delayed (especially mutual fund quotes).

User avatar
dratkinson
Posts: 3990
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 » Sun Nov 12, 2017 6:37 pm

Sidebar. Recall the creation of the IEX exchange (to get around the front running witnessed by other exchange dark pools) being described in the book "Flash Boys". Interesting read.
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

LK2012
Posts: 169
Joined: Mon Apr 30, 2012 4:42 pm

Re: How to Get Stock Quotes Into Excel

Post by LK2012 » Sun Nov 12, 2017 9:25 pm

jhfenton wrote:
Sun Nov 12, 2017 2:49 pm
LK2012 wrote:
Sun Nov 12, 2017 2:40 pm
I've already been through the change from MSN Money to Yahoo SMF Add-in, and now this ... very nice to have a workaround so quickly.
I just switched TO the SMF Add-in. I had never used it until a week ago. The smfGetYahooPortfolioView still works to load prices and price changes (and ~90 other fields if you want), and works much faster than the ticker by ticker Excel webservice to Yahoo functions that I used to use.

The quirks of Excel and add-ins meant that I had to add a VB script that literally unloads and loads the add-in upon opening a spreadsheet and whenever I click the refresh button, but everything loads quickly.

And the Yahoo data is still more complete and up-to-date than any other source I've seen. I briefly used the Google Sheets/Google Finance work-around when the straight-up Yahoo Finance API disappeared, but the data was incomplete and delayed (especially mutual fund quotes).
Okay, you convinced me to revisit the SMF Add-In and try to figure out the smfGetYahooPortfolioView. I feel a bit behind because I took a day off and so much has happened in that time. Do you basically use the smfGetYahooPortfolioView to make a Master list to draw quotes from into your spreadsheet? I'm going to look at it tomorrow, and I'll be thrilled if it loads quickly and I can skip the copy/paste stuff from Yahoo.

(I don't know how to add a VB script but sounds intriguing - is that a necessary step? )

User avatar
jhfenton
Posts: 2184
Joined: Sat Feb 07, 2015 11:17 am
Location: Ohio

Re: How to Get Stock Quotes Into Excel

Post by jhfenton » Mon Nov 13, 2017 8:43 am

LK2012 wrote:
Sun Nov 12, 2017 9:25 pm
jhfenton wrote:
Sun Nov 12, 2017 2:49 pm
I just switched TO the SMF Add-in. I had never used it until a week ago. The smfGetYahooPortfolioView still works to load prices and price changes (and ~90 other fields if you want), and works much faster than the ticker by ticker Excel webservice to Yahoo functions that I used to use.

The quirks of Excel and add-ins meant that I had to add a VB script that literally unloads and loads the add-in upon opening a spreadsheet and whenever I click the refresh button, but everything loads quickly.

And the Yahoo data is still more complete and up-to-date than any other source I've seen. I briefly used the Google Sheets/Google Finance work-around when the straight-up Yahoo Finance API disappeared, but the data was incomplete and delayed (especially mutual fund quotes).
Okay, you convinced me to revisit the SMF Add-In and try to figure out the smfGetYahooPortfolioView. I feel a bit behind because I took a day off and so much has happened in that time. Do you basically use the smfGetYahooPortfolioView to make a Master list to draw quotes from into your spreadsheet? I'm going to look at it tomorrow, and I'll be thrilled if it loads quickly and I can skip the copy/paste stuff from Yahoo.

(I don't know how to add a VB script but sounds intriguing - is that a necessary step? )
Yes, to your question. That is exactly how I use it.

You can use the SMF author's example spreadsheet to see how smfGetYahooPortfolioView works as an array formula. I added an extra Quotes sheet with my list of desired ETFs and mutual fund tickers down the left side (similar to his example). smfGetYahooPortfolioView pulls in the data. Then on my Holdings summary sheet, I use VLOOKUP to pull the data from the desired column for the desired ticker from the Quotes sheet. (I pull last price and daily change.) My Holdings sheet is the same as it used to be except that I previously used direct Yahoo API calls using the Excel WEBSERVICE function instead of VLOOKUP. Once everything was set up, I hid the Quotes sheet. I only need to unhide it to add new tickers.

Once I had it working, I had two remaining problems: I couldn't get it to refresh without unloading and reloading the add-in, and the add-in wouldn't run properly on start-up until I unloaded and reloaded it.

Fix: Add code to my existing RefreshPage Macro (tied to "Refresh" buttons in a couple of places) to uninstall and reinstall the add-in. And add the same code to the Workbook_Open function which gets run automatically when you open a workbook.

Now when I hit the refresh button everything flashes to #NAME? for a second and then back to updated numbers. It is literally about a second from click to update, much faster than 30+ separate WEBSERVICE calls.

Code: Select all

Sub RefreshPage()
'
' RefreshPage Macro
'
    AddIns("Stock Market Functions Add-In").Installed = False
    AddIns("Stock Market Functions Add-In").Installed = True
    ActiveWorkbook.RefreshAll
    Application.CalculateFull
End Sub

Code: Select all

Private Sub Workbook_Open()
    AddIns("Stock Market Functions Add-In").Installed = False
    AddIns("Stock Market Functions Add-In").Installed = True
    ActiveWorkbook.RefreshAll
    Application.CalculateFull
End Sub

LK2012
Posts: 169
Joined: Mon Apr 30, 2012 4:42 pm

Re: How to Get Stock Quotes Into Excel

Post by LK2012 » Mon Nov 13, 2017 11:01 pm

jhfenton wrote:
Mon Nov 13, 2017 8:43 am
LK2012 wrote:
Sun Nov 12, 2017 9:25 pm
jhfenton wrote:
Sun Nov 12, 2017 2:49 pm
I just switched TO the SMF Add-in. I had never used it until a week ago. The smfGetYahooPortfolioView still works to load prices and price changes (and ~90 other fields if you want), and works much faster than the ticker by ticker Excel webservice to Yahoo functions that I used to use.

The quirks of Excel and add-ins meant that I had to add a VB script that literally unloads and loads the add-in upon opening a spreadsheet and whenever I click the refresh button, but everything loads quickly.

And the Yahoo data is still more complete and up-to-date than any other source I've seen. I briefly used the Google Sheets/Google Finance work-around when the straight-up Yahoo Finance API disappeared, but the data was incomplete and delayed (especially mutual fund quotes).
Okay, you convinced me to revisit the SMF Add-In and try to figure out the smfGetYahooPortfolioView. I feel a bit behind because I took a day off and so much has happened in that time. Do you basically use the smfGetYahooPortfolioView to make a Master list to draw quotes from into your spreadsheet? I'm going to look at it tomorrow, and I'll be thrilled if it loads quickly and I can skip the copy/paste stuff from Yahoo.

(I don't know how to add a VB script but sounds intriguing - is that a necessary step? )
Yes, to your question. That is exactly how I use it.

You can use the SMF author's example spreadsheet to see how smfGetYahooPortfolioView works as an array formula. I added an extra Quotes sheet with my list of desired ETFs and mutual fund tickers down the left side (similar to his example). smfGetYahooPortfolioView pulls in the data. Then on my Holdings summary sheet, I use VLOOKUP to pull the data from the desired column for the desired ticker from the Quotes sheet. (I pull last price and daily change.) My Holdings sheet is the same as it used to be except that I previously used direct Yahoo API calls using the Excel WEBSERVICE function instead of VLOOKUP. Once everything was set up, I hid the Quotes sheet. I only need to unhide it to add new tickers.

Once I had it working, I had two remaining problems: I couldn't get it to refresh without unloading and reloading the add-in, and the add-in wouldn't run properly on start-up until I unloaded and reloaded it.

Fix: Add code to my existing RefreshPage Macro (tied to "Refresh" buttons in a couple of places) to uninstall and reinstall the add-in. And add the same code to the Workbook_Open function which gets run automatically when you open a workbook.

Now when I hit the refresh button everything flashes to #NAME? for a second and then back to updated numbers. It is literally about a second from click to update, much faster than 30+ separate WEBSERVICE calls.

Code: Select all

Sub RefreshPage()
'
' RefreshPage Macro
'
    AddIns("Stock Market Functions Add-In").Installed = False
    AddIns("Stock Market Functions Add-In").Installed = True
    ActiveWorkbook.RefreshAll
    Application.CalculateFull
End Sub

Code: Select all

Private Sub Workbook_Open()
    AddIns("Stock Market Functions Add-In").Installed = False
    AddIns("Stock Market Functions Add-In").Installed = True
    ActiveWorkbook.RefreshAll
    Application.CalculateFull
End Sub
I am pretty darn excited because I was able to get my Master list working with the new smfGetYahooPortfolioView method!

Thank you so much, I was getting frustrated, not sure I wanted to invest a lot of time if the end result was going to be a bust, but once you said it worked, I dove in. I always make a ridiculously stupid mistake at some point just setting up the Array for the first time, but got help from the great Randy H. at Yahoo Groups, and it is a beautiful thing to see those fields all fill in again!

Next, I will have to see if I have the unload/load problem with refreshing. I didn't have that problem with the RCH version. (If so, my next step will be learning how to create a macro).

Anyway, thanks again. I'm thrilled to get this working!

User avatar
jhfenton
Posts: 2184
Joined: Sat Feb 07, 2015 11:17 am
Location: Ohio

Re: How to Get Stock Quotes Into Excel

Post by jhfenton » Tue Nov 14, 2017 7:49 am

LK2012 wrote:
Mon Nov 13, 2017 11:01 pm
Next, I will have to see if I have the unload/load problem with refreshing. I didn't have that problem with the RCH version. (If so, my next step will be learning how to create a macro).

Anyway, thanks again. I'm thrilled to get this working!
:beer

Congrats. The other advantage I've found with smfGetYahooPortfolioView is that there is no artificial 15-minute delay on the main price tickers, unlike the old individual quote API. The quotes are updated in near real time (the same time as prices on the Yahoo Finance website). (Bid-ask quotes and IV tickers are 15-minutes delayed, just as they are on the website.)

The easiest way to get started with macros is to record (menu "Record Macro") one doing what you want and then see what it looks like. Then once you have a macro, you can assign it to a button.

And if you Google "Excel workbook_open", you can find sources telling you how to have a macro execute on open. (You can copy your recorded macro language to workbook_open.

LK2012
Posts: 169
Joined: Mon Apr 30, 2012 4:42 pm

Re: How to Get Stock Quotes Into Excel

Post by LK2012 » Tue Nov 14, 2017 5:55 pm

jhfenton wrote:
Tue Nov 14, 2017 7:49 am
LK2012 wrote:
Mon Nov 13, 2017 11:01 pm
Next, I will have to see if I have the unload/load problem with refreshing. I didn't have that problem with the RCH version. (If so, my next step will be learning how to create a macro).

Anyway, thanks again. I'm thrilled to get this working!
:beer

Congrats. The other advantage I've found with smfGetYahooPortfolioView is that there is no artificial 15-minute delay on the main price tickers, unlike the old individual quote API. The quotes are updated in near real time (the same time as prices on the Yahoo Finance website). (Bid-ask quotes and IV tickers are 15-minutes delayed, just as they are on the website.)

The easiest way to get started with macros is to record (menu "Record Macro") one doing what you want and then see what it looks like. Then once you have a macro, you can assign it to a button.

And if you Google "Excel workbook_open", you can find sources telling you how to have a macro execute on open. (You can copy your recorded macro language to workbook_open.
Thank you, I'm going to check it out!

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

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

Post by Leesbro63 » Wed Nov 15, 2017 8:37 am

Well, I was all happy to figure out GOOGLE SHEETS and it works well. Except...it doesn't update mutual fund prices until sometime much later than I'd like (about 6:30PM Eastern). Anyone else notice this? Any good workaround?

jsorhaug
Posts: 1
Joined: Wed Nov 15, 2017 10:20 am

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

Post by jsorhaug » Wed Nov 15, 2017 10:27 am

np81 wrote:
Fri Nov 10, 2017 1:24 pm
If you don't want to deal with macros, and are just looking for a basic ticker lookup (in my case, the current price), you only need two things:
  • An API key from AlphaVantage
  • This Excel query:

    Code: Select all

    =VALUE(MID(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))+1,FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),10))-FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))-1))
A1 being the cell that contains your ticker symbol, and YourKeyGoesHere being your AphaVantage API key

Is it ugly? Yep. Does it work? Yep.
Hi there - thanks for this query. I tried it in Excel 2010. I have the AV Key and inserted it into the Excel query in the four places. Tried AAPL in cell A1 and the query in B1. Excel returns #Name? What am I doing wrong? Thanks so much for your help.

Jan

Pigeye Brewster
Posts: 33
Joined: Thu Oct 05, 2017 7:33 pm

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

Post by Pigeye Brewster » Wed Nov 15, 2017 12:06 pm

Leesbro63 wrote:
Wed Nov 15, 2017 8:37 am
Well, I was all happy to figure out GOOGLE SHEETS and it works well. Except...it doesn't update mutual fund prices until sometime much later than I'd like (about 6:30PM Eastern). Anyone else notice this? Any good workaround?
It's not just you. I had the same experience. Got Google Finance set up and good to go, then noticed the fund NAVs hadn't updated yet during the time window when I typically deal with financial matters. The prices in Yahoo Finance appear to update much earlier. So I changed to setting up a Yahoo Finance watchlist, then click on "export" which opens up a .csv file in Excel and I copy/past those prices into the existing "prices" tab I have in my main Excel spreadsheet.

Not automated, not elegant, not slick. But it takes just a minute or two each time I do it and took minimal effort. All it does is work. :beer

User avatar
Leesbro63
Posts: 4880
Joined: Mon Nov 08, 2010 4:36 pm

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

Post by Leesbro63 » Wed Nov 15, 2017 2:37 pm

Pigeye Brewster wrote:
Wed Nov 15, 2017 12:06 pm
Leesbro63 wrote:
Wed Nov 15, 2017 8:37 am
Well, I was all happy to figure out GOOGLE SHEETS and it works well. Except...it doesn't update mutual fund prices until sometime much later than I'd like (about 6:30PM Eastern). Anyone else notice this? Any good workaround?
It's not just you. I had the same experience. Got Google Finance set up and good to go, then noticed the fund NAVs hadn't updated yet during the time window when I typically deal with financial matters. The prices in Yahoo Finance appear to update much earlier. So I changed to setting up a Yahoo Finance watchlist, then click on "export" which opens up a .csv file in Excel and I copy/past those prices into the existing "prices" tab I have in my main Excel spreadsheet.

Not automated, not elegant, not slick. But it takes just a minute or two each time I do it and took minimal effort. All it does is work. :beer
Not what I hoped to hear, but thanks. Yeah.

User avatar
jhfenton
Posts: 2184
Joined: Sat Feb 07, 2015 11:17 am
Location: Ohio

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

Post by jhfenton » Wed Nov 15, 2017 3:19 pm

Leesbro63 wrote:
Wed Nov 15, 2017 8:37 am
Well, I was all happy to figure out GOOGLE SHEETS and it works well. Except...it doesn't update mutual fund prices until sometime much later than I'd like (about 6:30PM Eastern). Anyone else notice this? Any good workaround?
Nope. They're not updated until sometime long after I've gone to bed.

That's why I quickly abandoned the Google Sheets method and found the smfGetYahooPortfolioView method using the SMF add-in. (Someone mentioned it a couple of weeks ago in this thread.) Mutual fund prices are updated close to 6 PM ET, and ETF/stock quotes are near realtime during the day.

np81
Posts: 144
Joined: Thu Dec 26, 2013 3:11 pm

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

Post by np81 » Thu Nov 16, 2017 9:09 am

jsorhaug wrote:
Wed Nov 15, 2017 10:27 am
np81 wrote:
Fri Nov 10, 2017 1:24 pm
If you don't want to deal with macros, and are just looking for a basic ticker lookup (in my case, the current price), you only need two things:
  • An API key from AlphaVantage
  • This Excel query:

    Code: Select all

    =VALUE(MID(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))+1,FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),10))-FIND(CHAR(160),SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol="&A1&"&interval=1min&apikey=YourKeyGoesHere&datatype=csv"),",",CHAR(160),9))-1))
A1 being the cell that contains your ticker symbol, and YourKeyGoesHere being your AphaVantage API key

Is it ugly? Yep. Does it work? Yep.
Hi there - thanks for this query. I tried it in Excel 2010. I have the AV Key and inserted it into the Excel query in the four places. Tried AAPL in cell A1 and the query in B1. Excel returns #Name? What am I doing wrong? Thanks so much for your help.

Jan
The WEBSERVICE function is only available in Excel 2013 and later, so that's your issue.

User avatar
Leif
Posts: 2189
Joined: Wed Sep 19, 2007 4:15 pm

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

Post by Leif » Tue Nov 21, 2017 12:28 pm

np81 wrote:
Thu Nov 16, 2017 9:09 am
The WEBSERVICE function is only available in Excel 2013 and later, so that's your issue.
Thanks for that note. I have Excel 2011 (Mac Version) and I was having the same issue so I gave up on that path. I've reprogramed to use Google Sheets. That works fine, but a bit annoying, to have a one day delay for my fund quotes
Investors should diversify across many asset-classes so that whatever happens, we will not have all our investments in underperforming asset classes and thereby fail to meet our goals-Taylor Larimore

TravelforFun
Posts: 847
Joined: Tue Dec 04, 2012 11:05 pm

Re: How to Get Stock Quotes Into Excel

Post by TravelforFun » Tue Nov 21, 2017 3:11 pm

LK2012 wrote:
Sun Nov 12, 2017 2:40 pm
TravelforFun wrote:
Fri Nov 10, 2017 12:57 pm
It died on 11/1. I now get my stock quotes like this:

- set up a portfolio of all my stocks in Yahoo Finance and bookmark it

- every time I open the bookmarked page, I see a list of my stocks, prices, volumes, etc.

- click download to excel, an excel page opens with all my stock quotes

- copy and paste onto my spreadsheet.

Not very easy or convenient but it beats manually entering the quotes.

TravelforFun
Thank you! I just switched over to this method. It took me a few hours to link all the pages of my Excel spreadsheet to a Master worksheet that I added to hold the exported Yahoo Quotes from my newly constructed Watchlist (of tickers in my spreadsheet).

But it is now working wonderfully!

I've already been through the change from MSN Money to Yahoo SMF Add-in, and now this ... very nice to have a workaround so quickly.

One issue I ran into occurred when I was deleting the previous Yahoo SMF Add-in "Array" areas, so I could plug in new formulas linking to my Master quote list. Once in a while the "array" from the SMF Add-in was not matching my highlighted area and I was getting the dreaded "you can't change an Array" message. Then I re-discovered the method of clicking a cell in the Array, then hitting Ctrl +/ and that allowed me to see the arrays and remove them. H

Then I just plugged in new formulas to link to the Master List, and voila!

Yes, there are the extra steps of exporting the .cvs file and then copy/paste into my Master worksheet, but for end-of-the-day or less frequent check-ins, this should work just fine.

If I want to check things more often, I'll look at Morningstar portfolios.

Thanks again!
You’re welcome. It takes me 15 seconds to export the quotes and paste them onto my spreadsheet.

TravelforFun

TravelforFun
Posts: 847
Joined: Tue Dec 04, 2012 11:05 pm

Re: How to Get Stock Quotes Into Excel

Post by TravelforFun » Tue Nov 21, 2017 3:12 pm

LK2012 wrote:
Sun Nov 12, 2017 2:40 pm
TravelforFun wrote:
Fri Nov 10, 2017 12:57 pm
It died on 11/1. I now get my stock quotes like this:

- set up a portfolio of all my stocks in Yahoo Finance and bookmark it

- every time I open the bookmarked page, I see a list of my stocks, prices, volumes, etc.

- click download to excel, an excel page opens with all my stock quotes

- copy and paste onto my spreadsheet.

Not very easy or convenient but it beats manually entering the quotes.

TravelforFun
Thank you! I just switched over to this method. It took me a few hours to link all the pages of my Excel spreadsheet to a Master worksheet that I added to hold the exported Yahoo Quotes from my newly constructed Watchlist (of tickers in my spreadsheet).

But it is now working wonderfully!

I've already been through the change from MSN Money to Yahoo SMF Add-in, and now this ... very nice to have a workaround so quickly.

One issue I ran into occurred when I was deleting the previous Yahoo SMF Add-in "Array" areas, so I could plug in new formulas linking to my Master quote list. Once in a while the "array" from the SMF Add-in was not matching my highlighted area and I was getting the dreaded "you can't change an Array" message. Then I re-discovered the method of clicking a cell in the Array, then hitting Ctrl +/ and that allowed me to see the arrays and remove them. H

Then I just plugged in new formulas to link to the Master List, and voila!

Yes, there are the extra steps of exporting the .cvs file and then copy/paste into my Master worksheet, but for end-of-the-day or less frequent check-ins, this should work just fine.

If I want to check things more often, I'll look at Morningstar portfolios.

Thanks again!
You’re welcome. It takes me 15 seconds to export the quotes and paste them onto my spreadsheet when I want to check my portfolio.

TravelforFun

EHEngineer
Posts: 613
Joined: Sat Feb 28, 2015 4:35 pm

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

Post by EHEngineer » Tue Nov 21, 2017 7:53 pm

Leesbro63 wrote:
Wed Nov 15, 2017 8:37 am
Well, I was all happy to figure out GOOGLE SHEETS and it works well. Except...it doesn't update mutual fund prices until sometime much later than I'd like (about 6:30PM Eastern). Anyone else notice this? Any good workaround?
For me it is fine. But, I set up a portfolio monitor for a friend who likes to check during the day. For them, I made the spreadsheet apply the ETF percentage change to the mutual fund price until the mutual fund updates, then it switches to the mutual fund price.

This approach help if you are trying to rebalance on a day with larger movements.

Edit to add: Days like today are a pain. It's after 8pm eastern and google is still quoting yesterday's prices for at least a few funds of mine. eg VIIIX, VTINX.
Or, you can ... decline to let me, a stranger on the Internet, egg you on to an exercise in time-wasting, and you could say "I'm probably OK and I don't care about it that much." -Nisiprius

User avatar
RobertH
Posts: 219
Joined: Thu Mar 01, 2007 11:35 pm
Location: Bay Area

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

Post by RobertH » Wed Nov 22, 2017 9:46 pm

My first post in a LONG time. :)

With Yahoo! quotes defunct, my spreadsheet was broken. I needed a simple call that I could invoke from sixty cells in Excel that would allow me to download closing prices for stock and mutual funds once cell at a time, just like the =StockQuote() macro I had been using for many years.

Once I decided on the approach I wanted to take, I got there quickly. I'm posting this to save you the time I spent on research. I have successfully cut over to GoogleFinance data and, now that it's set up, I expect it to be as easy to use going forward as the =StockQuote() call had been. All I need to do to refresh my quotes is click 'DATA>REFRESH ALL' within my Excel workbook.

The bad news is that it's more work to set up. You need a Google account. You need to set up a Google sheet that downloads the data you want to access in Excel (not hard). You need to publish that sheet as a Web page (very easy). You need to import that sheet to a worksheet in your Excel document (easy). And then you need to link to that data within your Excel document (easy). If you change the Google Sheet to add a new row, the next time you refresh the data in your Excel workbook the new row will appear, so be mindful of what that does to your Excel references.

Non-issues: I don't have to import the Google sheet every time I want to update prices - Excel remembers the web page and how to get the data. And the Google sheet that is published on the web is updated every 5 minutes (cool!) so there's no maintenance there, either. Nice!

I found two web pages that describe the steps. This page is the one I worked from. I like it better. It provides a helpful template spreadsheet to work from. Here's the other which imports the data using a PowerQuery add-in to Excel that I didn't care to learn about (and you probably won't either).

Documentation on the data available from the =GoogleFinance() function call can be found at the documentation page. After a little more searching, I learned that I could download the S&P500 index with the symbol '.INX', DJI with '.DJI' and VIX with 'INDEXCBOE:VIX'. This suggests that there are lots of other indices available, too, but that's for another day. The good news in switching to the =GoogleFinance() call for me is that I'm able to download Expense Ratios. I had keyed them in as constants before. I can also get either yesterday's close or the current price, or both.

The page I used to guide me suggested referring to the downloaded data in Excel by using the INDEX/MATCH functions. Me, I like VLOOKUP. Here's a sample from my spreadsheet. =VLOOKUP(E37,A$75:F$131,5,FALSE). The '5' refers to the fifth column in the selection from $A75:F$131. Note that VLOOKUP requires the column you're searching to be collated lexically (and alphabetically), so that's how I ordered the rows in my Google sheet.

I'm familiar with Excel, but not with Google Sheets. Two calls that I needed to know were =TODAY() and =NOW(). Same as Excel. (easy)

Some comments I read during my research said that GoogleFinance data can be spotty, but my needs for this workbook are simple and so far I'm happy. Another reason that I like this approach is that while Alphavantage might be a fine solution, and maybe better than Google, they're a new player whose business model is completely unclear to me and I have more confidence in Google's staying power at a price of free than I do in Alphavantage's.

(Slow updates of closing mutual fund prices generally don't bother me. If they did, I might try playing around with the 'price' vs. 'closeyest' attributes. I would be interested to know if yesterday's closing prices all flip at the same time.)

Post Reply