Live Stock Quotes in Excel

Get live financial data, streamed from the web, in Excel with this VBA function.

You can insert the live market price, EPS, PE, 52-week high, book value, or any other of over 80 quotes into a cell with this VBA function. The data updates to reflect current market values when you re-open or re-calculate your spreadsheet.

You can use the function to develop your own stock screeners, identify undervalued shares, keep track of your portfolio value, and more.

UPDATE: If you want a professional Excel add-in at a low price for live stock quotes, option prices and more, then investigate MarketXLS. It’s more stable than this VBA function and offers lifetime support and updates.

The VBA function use the Yahoo Finance API, and the code is given below (a spreadsheet also implements the function and gives a few examples of its use).

The function takes the form YahooFinance(ticker As String, item As String).

The Yahoo Finance API offers over programmatic access to over 80 types of financial data. The spreadsheet contains a full list of the types of financial data, and the corresponding value of the second argument.

Let’s say you wanted the EPS estimate of The Goldman Sachs Group for the next quarter. You’d simply enter the following into a cell.

=YahooFinance(“GS”,”epsestimatenextquarter”)

and hit Enter. The VBA will request the data from Yahoo Finance and place it in your cell.

EPS Estimate for the Goldman Sachs Group Inc in a spreadsheet

If you recalculate or re-open the spreadsheet, the data automatically updates with the most recent market value.

Because you’re importing live quotes with a function call, you have the flexibility to create your own customized financial applications. You could, for example, build a stock comparison table that updates every time you open the spreadsheet or change ticker symbols (the spreadsheet contains an example).

Stock comparison table for major integrated oil and gas firms

Employing Excel’s conditional formating tools, you could highlight the cell that contains the lowest PE for a group of stocks, or filter out companies with a low dividend yield.

The VBA code is given below, and is implemented in the spreadsheet available at the bottom of this article.

Function YahooFinance(ticker As String, item As String)
 
    Dim strURL As String, strCSV As Double, itemFound As Integer, tag As String
 
    itemFound = 0
    If item = "ask" Then
        tag = "a"
        itemFound = 1
    ElseIf item = "bid" Then
        tag = "b"
        itemFound = 1
    ElseIf item = "bookvalue" Then
        tag = "b4"
        itemFound = 1
    ElseIf item = "change" Then
        tag = "c1"
        itemFound = 1
    ElseIf item = "afterhourschangerealtime" Then
        tag = "c8"
        itemFound = 1
    ElseIf item = "tradedate" Then
        tag = "d2"
        itemFound = 1
    ElseIf item = "epsestimatecurrentyear" Then
        tag = "e7"
        itemFound = 1
    ElseIf item = "floatshares" Then
        tag = "f6"
        itemFound = 1
    ElseIf item = "52weeklow" Then
        tag = "j"
        itemFound = 1
    ElseIf item = "annualizedgain" Then
        tag = "g3"
        itemFound = 1
    ElseIf item = "holdingsgainrealtime" Then
        tag = "g3"
        itemFound = 1
    ElseIf item = "marketcapitalization" Then
        tag = "j1"
        itemFound = 1
    ElseIf item = "percentchangefrom52weekhigh" Then
        tag = "k5"
        itemFound = 1
    ElseIf item = "daysrangerealtime" Then
        tag = "m2"
        itemFound = 1
    ElseIf item = "changefrom200daymovingaverage" Then
        tag = "m5"
        itemFound = 1
    ElseIf item = "percentchangefrom50daymovingaverage" Then
        tag = "m8"
        itemFound = 1
    ElseIf item = "open" Then
        tag = "o"
        itemFound = 1
    ElseIf item = "changeinpercent" Then
        tag = "p2"
        itemFound = 1
    ElseIf item = "exdividenddate" Then
        tag = " q"
        itemFound = 1
    ElseIf item = "peratiorealtime" Then
        tag = "r2"
        itemFound = 1
    ElseIf item = "priceepsestimatenextyear" Then
        tag = "r7"
        itemFound = 1
    ElseIf item = "shortratio" Then
        tag = "s7"
        itemFound = 1
    ElseIf item = "tickertrend" Then
        tag = "t7"
        itemFound = 1
    ElseIf item = "holdingsvalue" Then
        tag = "v1"
        itemFound = 1
    ElseIf item = "daysvaluechange" Then
        tag = "w1"
        itemFound = 1
    ElseIf item = "dividendyield" Then
        tag = "y"
        itemFound = 1
    ElseIf item = "averagedailyvolume" Then
        tag = "a2"
        itemFound = 1
    ElseIf item = "askrealtime" Then
        tag = "b2"
        itemFound = 1
    ElseIf item = "bidsize" Then
        tag = "b6"
        itemFound = 1
    ElseIf item = "commision" Then
        tag = "c3"
        itemFound = 1
    ElseIf item = "dividendshare" Then
        tag = "d"
        itemFound = 1
    ElseIf item = "earningspershare" Then
        tag = "e"
        itemFound = 1
    ElseIf item = "epsestimatenextyear" Then
        tag = "e8"
        itemFound = 1
    ElseIf item = "days low" Then
        tag = "g"
        itemFound = 1
    ElseIf item = "52weekhigh" Then
        tag = "k"
        itemFound = 1
    ElseIf item = "holdsingain" Then
        tag = "g4"
        itemFound = 1
    ElseIf item = "moreinfo" Then
        tag = "i"
        itemFound = 1
    ElseIf item = "marketcaprealtime" Then
        tag = "j3"
        itemFound = 1
    ElseIf item = "percentchangefrom52weeklow" Then
        tag = "j6"
        itemFound = 1
    ElseIf item = "lasttradesize" Then
        tag = "k3"
        itemFound = 1
    ElseIf item = "lasttradewithtime" Then
        tag = "l"
        itemFound = 1
    ElseIf item = "lowlimit" Then
        tag = "l3"
        itemFound = 1
    ElseIf item = "50movingaverage" Then
        tag = "m3"
        itemFound = 1
    ElseIf item = "percentchangefrom200daymovingaverage" Then
        tag = "m6"
        itemFound = 1
    ElseIf item = "name" Then
        tag = "n"
        itemFound = 1
    ElseIf item = "previousclose" Then
        tag = "p"
        itemFound = 1
    ElseIf item = "pricesales" Then
        tag = "p5"
        itemFound = 1
    ElseIf item = "peratio" Then
        tag = "r"
        itemFound = 1
    ElseIf item = "pegratio" Then
        tag = "r5"
        itemFound = 1
    ElseIf item = "symbol" Then
        tag = "s"
        itemFound = 1
    ElseIf item = "lasttradetime" Then
        tag = "t1"
        itemFound = 1
    ElseIf item = "1yeartargetprice" Then
        tag = "t8"
        itemFound = 1
    ElseIf item = "holdingsvaluerealtime" Then
        tag = "v7"
        itemFound = 1
    ElseIf item = "daysvaluechangerealtime" Then
        tag = "w4"
        itemFound = 1
    ElseIf item = "asksize" Then
        tag = "a5"
        itemFound = 1
    ElseIf item = "bidrealtime" Then
        tag = "b3"
        itemFound = 1
    ElseIf item = "change&percentchange" Then
        tag = "c"
        itemFound = 1
    ElseIf item = "changerealtime" Then
        tag = "c6"
        itemFound = 1
    ElseIf item = "lasttradedate" Then
        tag = "d1"
        itemFound = 1
    ElseIf item = "errorindication" Then
        tag = "e1"
        itemFound = 1
    ElseIf item = "epsestimatenextquarter" Then
        tag = "e9"
        itemFound = 1
    ElseIf item = "dayshigh" Then
        tag = "h"
        itemFound = 1
    ElseIf item = "holdingsgainpercent" Then
        tag = "g1"
        itemFound = 1
    ElseIf item = "holdsingsgainpercentrealtime" Then
        tag = "g5"
        itemFound = 1
    ElseIf item = "orderbookrealtime" Then
        tag = "i5"
        itemFound = 1
    ElseIf item = "ebitda" Then
        tag = "j4"
        itemFound = 1
    ElseIf item = "lasttraderealtimewithtime" Then
        tag = "k1"
        itemFound = 1
    ElseIf item = "changefrom52weekhigh" Then
        tag = "k4"
        itemFound = 1
    ElseIf item = "lasttradepriceonly" Then
        tag = "l1"
        itemFound = 1
    ElseIf item = "daysrange" Then
        tag = "m"
        itemFound = 1
    ElseIf item = "200daymovingaverage" Then
        tag = "m4"
        itemFound = 1
    ElseIf item = "notes" Then
        tag = "n4"
        itemFound = 1
    ElseIf item = "pricepaid" Then
        tag = "p1"
        itemFound = 1
    ElseIf item = "pricebook" Then
        tag = "p6"
        itemFound = 1
    ElseIf item = "dividendpaydate" Then
        tag = "r1"
        itemFound = 1
    ElseIf item = "priceepsestimatecurrentyear" Then
        tag = "r6"
        itemFound = 1
    ElseIf item = "sharesowned" Then
        tag = "s1"
        itemFound = 1
    ElseIf item = "tradelinks" Then
        tag = "t6"
        itemFound = 1
    ElseIf item = "volume" Then
        tag = "v"
        itemFound = 1
    ElseIf item = "52weekrange" Then
        tag = "w"
        itemFound = 1
    ElseIf item = "stockexchange" Then
        tag = "x"
        itemFound = 1
    ElseIf item = "changepercentrealtime" Then
        tag = "k2"
        itemFound = 1
    ElseIf item = "changefrom52weeklow" Then
        tag = "j5"
        itemFound = 1
    End If
 
    If itemFound = 1 Then
        strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=" & tag
        Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
        XMLHTTP.Open "GET", strURL, False
        XMLHTTP.send
        YahooFinance = XMLHTTP.responseText
        Set XMLHTTP = Nothing       
    Else  
        YahooFinance = "Item Not Found"  
    End If
 
End Function

Now, there are drawbacks to this function. It has to be manually transferred to new spreadsheets,

MarketXLS is a professional-quality product that I now use regularly. I’ve found it to be stable, and well-supported by the developers. Check it out!

Get Excel Spreadsheet with VBA Function for Live Stock Quotes


42 thoughts on “Live Stock Quotes in Excel”

  1. Could you please advise how to convert stock quotes that your program returns from Yahoo into standard Excel data format?

    Thank you,

    Reply
  2. I believe he means if you pull in a stock quote and a prior close then tried subtracting the two cells it would give a #VALUE! error. This is probably being caused by the data being pulled from Yahoo as text with some other invisible characters in the cell. Not sure if a workaround can be made for this.

    Reply
  3. This seems to work great on some versions of Excel, but others report a error that implies bad arguments in the VBA code. Any idea what’s up?

    Reply
      • I use Excel 2011 for the Mac. I also use 2003, 2007, and 2013 for Windows. VBA for the Mac is significantly different from the Windows versions. I’ve been gradually learning VBA for the Mac by watching Cynthia Brown’s excellent tutorials on Youtube (usual disclaimers). One of the biggest obstacles is exactly this issue. I may try to convert Samir’s code just for practice.

        Best,
        Tony Lima

        Reply
  4. I found that the function returns a text value. By adding =(value(clean(YahooFinance())) a usable number is returned. Note the changes are between the = and YahooFinance locations, with added parentheses as needed.

    Hope this helps.

    Reply
    • Insert this code into a VBA module and run startToRefresh. Change the “00:00:10” to reflect your desired update time interval

      ==========================
      Sub startToRefresh
      Timetorun = Now + timevalue(“00:00:10″)
      application.ontime timetorun,”refreshAll”
      End Sub

      Sub refreshAll
      Activeworkbook.Refreshall
      End Sub

      Sub autoClose()
      Application.OnTime timetorun, refreshAll, , False
      End Sub
      ========================

      Reply
      • Thank you, Samir!

        As per your instruction, I’ve inserted the above codes into a module. But when I run it, it gave compilation error message “Expected Function or variable” at the “Application.OnTime timetorun, refreshAll, , False” of Sub autoClose().

        I’ve limited knowledge in programming. Please help.

        Reply
  5. Thank you for these spreadsheets. I’m somewhat new to programming but really enjoy it and I’m always trying to learn so I was looking at the code for this one and I noticed what appears to me to be a couple errors.

    in the elseif structure I don’t see a elseif case for the following:
    changefrom50daymovingaverage
    highlimit
    dividendpayday

    The last one dividendpayday I assume is supposed to be the select case dividendpaydate

    I might be missing something but I thought I’d pass this along.

    Reply
  6. Is “lasttradepriceonly” the price that is shown in Yahoo Finance on top of an ETF page?

    Is there an other way to get this price into Excel apart from buying this sheet? Unfortunately webqueries can only be used for Ask and Bid prices.

    Joop

    Reply
  7. Is anyone able to make this great spreadsheet real-time with the information Samir has given? I’d love to use this but while travelling in Tanzania don’t have the time to do a VB course to make this all work 🙁

    Tnx!

    Joop.

    Reply
  8. Hi
    Thanks for the great tool. One question about cell formatting. For some reason, when I run the function to get the last price, I cannot format the cells to a currency format. Is there anyway to clear the format or set formats for the different tags?

    Thanks

    Reply
  9. Thanks for the awesome function. I have copied the function to VBE. I am running windows 7 with excel 2010, 64 bit. When i run the function i get an error (syntax compiler error). The following line of code is returned in red.
    strURL = “http://download.finance.yahoo.com/d/quotes.csv?s=” & ticker & “&f=” & tag
    The code in my vbe is all on one line. Could you please help repair the error.
    thanks,
    lou

    Reply
  10. Hello,

    Thanks for your awesome work.
    2 problems I have encountered to far is that

    1). The values I got from VBA is a text value, I am not able to modify the data
    2). I cannot use conditional formatting on it.

    Could you please advise? I have no experience with VBA.

    Thank you

    Reply
    • You may want to investigate NUMBERVALUE function of Excel, example:

      =NUMBERVALUE(YahooFinance(“TSLA”,”dayshigh”))

      Here is another formula I use:
      =NUMBERVALUE(SUBSTITUTE(YahooFinance(“TSLA”,”changeinpercent”),CHAR(34),””))

      Good luck

      Reply
  11. Thank you for the excellent tool. Today, I noticed that some names of the stocks are downloading into Excel as “N/A” Examples: TSLA, BIDU, GPRO and many others.
    AAPL is OK. Simple test:

    =YahooFinance(“TSLA”,”name”)
    returns N/A

    Did anyone notice it? And how can we fix it?

    Thanks,

    Reply
    • I am replying to my own question: I think, Yahoo servers fixed the issue. Everything is back to normal now. I experienced these problems on/around the date I posted.

      Reply
  12. Hi,
    When I am trying to update and refresh the spreadsheet data, and click the button on the “stock comparison sheet” that says “download updated data”, or click the “refresh all data” button under the data ribbon, the “data sheet comes back with an error message saying that my browser is no longer supported, and that I need to upgrade my browser. I am running Internet explorer 11, and I have also tried it on a machine where google chrome is the default browser (and is the latest version), and I got the same error message on that machine. Please let me know of any solutions or fixes that you can think of.

    Thanks,
    Trevor

    Reply
  13. I just noticed that instruction
    ElseIf item = “dayslow”
    is missing from the script

    Could you transcript the instruction?

    ElseIf item = “dayslow” Then
    tag=”?”
    Itemfound=1

    Reply
  14. Hi,

    Nice work with the vba code. If you could help I would greatly appreciate:
    Is there a code to refresh quotes every 30 seconds? Also my excel sows formula results as text and not numbers, is there a way to work around it? Tried to substitute the “.” for a “,” but it doesn’t work.
    Excel is in Portugues.
    Thanks,
    Miguel

    Reply
  15. I’ve been trying to modify this spreadsheet to do the following:
    – I copied the list of all USA stock tickers from your All Yahoo stock ticker spreadsheet into column A
    – I changed the call to YahooFinance to ask for the “floatshares” for each stock.
    – I need the number returned to be a number with thousands separators; currently doesn’t do this.
    – I need to be able to add up all the float shares in a separate cell “Total Float”. Current numbers cannot be totaled.

    The overall objective is to generate a chart that shows the total number of float shares in the market on a daily basis. If historical float could be added in that would be a big bonus and make this even more useful because I could see an overall trend.

    Reply
  16. Samir
    I fixed the number formatting issue with =(VALUE(CLEAN(YahooFinance(A14,”floatshares”)))) so that the thousands separator would show.

    Still searching for a way to add the cells to get a total.

    Reply
  17. Samir,
    Just wanted to share that I solved all of my issues with the following changes. Maybe this helps others a bit, or maybe I’m repeating what others have already said : )

    My stock ticker symbol begins at cell A13

    =(VALUE(CLEAN(YahooFinance(A13,”floatshares”))))
    This formula cleans up the integer value and provides a number. The cell can now be formatted to allow for thousand separators.

    =SUM(IF(ISERROR(C13:C5418),0,C13:C5418))
    I then put this formula in another cell to total all the float share values. Since there are many stocks without float share numbers the errors have to be omitted to sum correctly. Just substitute your own range of cell values.

    I’m still working on getting historical float share numbers and graphing it.

    Reply
  18. Hi,
    Is there any way to get sector and subsector for each of the tickers ? I don’t see that in the list of arguments, and I do see those information on the YahooFinance website
    Otherwise, thanks a lot for all this great work, makes me save a looooooot of time

    Reply
  19. Hi

    I have used the following VBA code to and formula (StockQuote(Ticker, date) to find a certain share price on a specified dsate. It uses the Yahoo FInanc tickers and has worked perfectly in half a year or so until last week, Now I just get an error messagem when trying to retrieve share close prices on a certain date of my choice. Van anyone be of assistance? VBA 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 dtPrevDate As Date
    Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
    Dim dbClose As Double

    dtPrevDate = dtDate – 7

    ‘ Compile the request URL with start date and end date
    strURL = “http://ichart.finance.yahoo.com/table.csv?s=” & strTicker & _
    “&a=” & Month(dtPrevDate) – 1 & _
    “&b=” & Day(dtPrevDate) & _
    “&c=” & Year(dtPrevDate) & _
    “&d=” & Month(dtDate) – 1 & _
    “&e=” & Day(dtDate) & _
    “&f=” & Year(dtDate) & _
    “&g=d&ignore=.csv”

    ‘ Debug.Print strURL

    Set http = CreateObject(“MSXML2.XMLHTTP”)
    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 = 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

    Reply
  20. Thanks for that.

    If you replace the line
    YahooFinance = XMLHTTP.responseText
    with
    YahooFinance = CDbl(XMLHTTP.responseText)
    it will return a number instead of text.

    It’s cleaner that way than messing with the formula in the spreadsheet.

    Reply
  21. Has the data feed changed? This spreadsheet has not worked properly for several months. Cells get populated with “VALUE” when I try to update the data. Please advise. This is a great spreadsheet. Hope it can still work!

    Reply

Leave a Comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.