Stock quotes from Yahoo Finance into Excel (VBA) - Macro included

UPDATE 02/2020: Macro works again. It can be found in the first comment below.
Here is a little video on how to get a macro into Excel which downloads the current stock price of any ticker symbol available on Yahoo Finance. For American stocks only the ticker symbol is needed (e.g. AAPL for Apple). For foreign stocks the market has to be defined as well (e.g. FRA:BMW would be the price of BMW at Frankfurt stock exchange). Just check the Yahoo Finance website for the right symbol.

[rene]

Dim TimeToRun

Sub auto_open()
Call ScheduleUpdateAll
End Sub

Sub ScheduleUpdateAll()
TimeToRun = Now + TimeValue("00:05:00") 'Set the update interval
Application.OnTime TimeToRun, "UpdateAll"
End Sub

Sub UpdateAll()
Application.CalculateFull
Call ScheduleUpdateAll
End Sub

Sub auto_close()
On Error Resume Next
Application.OnTime TimeToRun, "UpdateAll", , False
End Sub

Function StockQuote(strTicker As String)

Dim strURL As String, strCSV As String


Set http =
http.Open "GET", strURL, False
http.Send

strJSON = http.responsetext
'Debug.Print strJSON

Dim CutPos As Integer
CutPos = InStr(1, strJSON, "regularMarketPrice") 'Search for first occurence of string "regularMarketPrice". Result is number of characters until string.

CutPos = CutPos + 20 'Add 20 to CutPos in order to go to the very first character of balance
StockQuote = Val(Mid(strJSON, CutPos)) 'Mid: Remove the first x characters from downloaded JSON. Val: Extract number from new String

Set http = Nothing

End Function

[FreedomFreedom-styl]

I have been looking for such a scipt, using MS Excel 2013 and it is working perfectly, thanks so much !!

[SanisaI]

Working perfectly.. thank you very much..👍

[FreedomFreedom-styl]

Hi, Is there a way to automatically get the function to run to update all my stock quotes when I open the spreadsheet ? thanks

[Bill_Woo]

Thanks. This does work to pull Yahoo Finance data. It uses OnTime to kick recalc, and recalc kicks the UDF, and the UDF grabs the content to a string, and the rest is mechanical. It has the advantage over another method I've seen in that this hardwires the CreateObject call avoiding the need for tools/reference to Microsoft WinHTTP Services. (However if you do use the reference, then you don't have to do CreateObject; rather, you'd go Dim http as New WinHttpRequest)

It is also fairly trivial for experienced users to build a compound strTicker with commas, e.g. "GLD, SLV" instead of "GLD" and parse it appropriately into sheet rows.

As to the flags after &f, for a full description of what's available, google
"here's a neat way to download stock data"
(Yahoo may change them over time. Hopefully the main field flags won't change.)

With compound tickers and more fields with &f, I didn't use dbClose, I used Split() logic to pull the elements and assigned them into cells (so I didn't use the UDF. I coerced the UDF logic in the timer logic.)

I think I have a somewhat strong handle on this and might be able to field intermediate to advanced questions on this if you reply here; but this is not novice material, and I probably won't have time to help beginners, sorry for that.

One technical note on this: firewalls are designed to snag this very type of operation, so be aware that you may need to satisfy your firewall in order for the data to arrive.

[crownroyal]

Thanks for the share! I've been trying to figure this one out for a while now. Can we pull Revenue and EPS numbers from Yahoo as well? 

[nareshgovindan]

It is not working for the Indian stocks listed on BSE and NSE! Do I have to change the URL? If so please suggest :)

[nickt]

Great video, very easy to follow.  It'd be great if you did a video where you added code for things like (marketcap, High/low for the day, price change for the day, and volume)...Thanks!

[typittman]

Hi,
At random times my excel worksheet will replace the true price value with 0's for some symbols and sometimes all the symbols. Is there a reason for this or a way to refresh the code?

[VN-lvjd]

I've tried all three codes on this page and keep getting "compile error: syntax error". I feel like it's so close but no cigar. Yet.

[dhavidcooper]

Hello Rene, does Micheal Whitaker's code refresh the stock prices automatically every 5 minutes the way your original code did? New stock symbol prices are immediately displayed when entered - but it seems the refresh does not automatically occur, or at least not in my spread sheet. It could be a user input error on my part but thought I'd ask. Thanks!

[BlueSky-gorb]

Hi Michael and Rene, the code does not get price for mutual fund like flpsx. Could you help on that too? Thanks!

[reyesduran]

Is there anyway to get the price quotes for the stocks options?

[michaelwhitaker]

Although this no longer works for Yahoo! I've spliced the code to run off of google finance for now. Full code below.

Function StockQuote(strTicker As String, Optional dtDate As Variant)
' Date is optional - if omitted, use today. If value is not a date, throw error.
If IsMissing(dtDate) Then
dtDate = Date
Else
If Not (IsDate(dtDate)) Then
StockQuote = CVErr(xlErrNum)
End If
End If

Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
Dim dbClose As Double

sDate = dtDate - 3
sMonth = MonthName(Month(sDate), True)
sDay = Day(sDate)
sYear = Year(sDate)

EDate = dtDate
eMonth = MonthName(Month(EDate), True)
eDay = Day(EDate)
eYear = Year(EDate)


' Compile the request URL with start date and end date
"historical?q=" & strTicker & _
"&startdate=" & sMonth & _
"+" & sDay & _
", +" & sYear & _
"&enddate=" & eMonth & _
"+" & eDay & _
", +" & eYear & _


Debug.Print strURL

Set http =
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText

' Debug.Print strCSV

' The most recent information is in row 2, just below the table headings.
' The price close is the 5th entry
strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
strColumns = Split(strRows(1), ", ") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
dbClose = Val(strColumns(4)) ' 4 means: 5th position, starting at index 0

' Debug.Print vbLf
' Debug.Print strRows(1)
' Debug.Print "dbClose: " & dbClose

StockQuote = dbClose

Set http = Nothing
End Function

[dhavidcooper]

Hello Rene, I have been using your formula with great success for the last two years. A few days ago it stopped working, and I noticed it had been removed from my Excel file. I tried replacing it with the code you provided a month ago - but still no success. Has there been any update to Windows 10 that could have caused this - or is there a revised formula? Thank you for this exceptional bit of code that has been so useful.

[pratikumehta]

Hi rene im trying to use indian shares with yahoo ticker names such as CIPLA.NS CIPLA.BO but they return a 0 value . sorry im a noob to macros or coding but do i type these yahoo names in the =stockquotes ('CIPLA.NS') i cant get what the last step is .

[Joe-ehhc]

The code stopped working today. Any fix to it?

[deschamps]

Get this error with the URL: We're sorry...
... but your computer or network may be sending automated queries. To protect our users, we can't process your request right now

[Bill_Woo]

Test. Jerkoff YT is ghosting my helpful comments, ghosting them for no purpose except to de-productive-ize the world.

A long and very beneficial post is supposed to follow this one.

[pdiazduran]

The code stopped working suddenly. Any fix please?