Get Stock Quotes in Excel

Spreadsheet template with simple custom-built formulas to get stock quotes from Yahoo Finance - no more fiddling with VBA code.

Supported versions: Excel 2007, Excel 2010, and Excel 2013. Last Tested: 3/12/2015.

Description

Monitor and analyze your stock portfolio right from this Excel spreadsheet. This spreadsheet provides you the formulas and a template with graphs and charts to get stock quotes from Yahoo finance. No tinkering with VBA code, and no complicated installation process required. The formulas just work. Have your stock symbols ready, download the spreadsheet, enable the marcos, and the spreadsheet simply just works right out of the box.

How to Use the Spreadsheet

A Note About the Data Source

The delayed stock prices are downloaded from Yahoo Finance's API, which is a free service and could be temporarily unavailable from time to time for various reasons. In this case, try refreshing the stock prices again at another time. If the issue persists for a few days, possibly due to a permanent change in the API's web address, please come back to this page and download the latest file.

Formulas Documentation

Below are the custom-built formulas, in alphabetical order, that perform the magic of delivering stock price data from Yahoo Finance right into the cells of the stock quote spreadsheet. Use the custom formulas just like any built-in formulas. If you are not sure how to get started, the spreadsheet contains examples of all the formulas documented below.

StockAsk
=StockAsk(stock_symbol)
Returns the ask price.
Example: =StockAsk("CAT")

StockBid
=StockBid(stock_symbol)
Returns the bid price.
Example: =StockBid("UTX")

StockChange
=StockChange(stock_symbol)
Returns the dollar value change in stock price since it last closed.
Example: =StockChange("MSFT")

StockChangeInPercent
=StockChangeInPercent(stock_symbol)
Returns the change in stock price as a percent.
Example: =StockChangeInPercent("NFLX")

StockDaysHigh
=StockDaysHigh(stock_symbol)
Returns the highest trade price for the day.
Example: =StockDaysHigh("FB")

StockDaysLow
=StockDaysLow(stock_symbol)
Returns the lowest trade price for the day.
Example: =StockDaysLow("IBM")

StockDividendPerShare
=StockDividendPerShare(stock_symbol)
Returns the dividend per share.
Example: =StockDividendPerShare("TXN")

StockDividendYield
=StockDividendYield(stock_symbol)
Returns the dividend yield.
Example: =StockDividendYield("CSCO")

StockEBITDA
=StockEBITDA(stock_symbol)
Returns the earnings before interest, taxes, depreciation, and amortization.
Example: =StockEBITDA("PAA")

StockEPS
=StockEPS(stock_symbol)
Returns the earnings per share.
Example: =StockEPS("CCE")

StockMarketCapitalization
=StockMarketCapitalization(stock_symbol)
Returns the market capitalization.
Example: =StockMarketCapitalization("MSI")

StockName
=StockName(stock_symbol)
Returns the name of the company, limited to a maximum length of 17 characters due to the API.
Example: =StockName("C")

StockOpen
=StockOpen(stock_symbol)
Returns the open price.
Example: =StockOpen("QCOM")

StockPERatio
=StockPERatio(stock_symbol)
Returns the price to earnings ratio.
Example: =StockPERatio("BA")

StockPreviousClose
=StockPreviousClose(stock_symbol)
Returns the closing price on the previous trading day.
Example: =StockPreviousClose("WTW")

StockQuote
=StockQuote(stock_symbol)
Returns delayed stock quote from Yahoo finance.
Example: =StockQuote("AMZN")

StockVolume
=StockVolume(stock_symbol)
Returns the number of shares traded for the day.
Example: =StockVolume("RHT")

StockYearHigh
=StockYearHigh(stock_symbol)
Returns the 52-week high.
Example: =StockYearHigh("INTU")

StockYearLow
=StockYearLow(stock_symbol)
Returns the 52-week low.
Exampe: =StockYearLow("NVDA")

Further Customization

This stock quote spreadsheet comes with charts to help you stay on top of your stock investments. You also have the flexibility to put together your own stock portfolio tool using the available custom-built formulas within the file. And if you need further customization or more advanced functionalities such as getting financial information for publicly traded companies in Excel, don't hesitate to contact me.

Feedback

I really enjoy putting together simple yet practical spreadsheet tools - from coding the core functionalities to designing an intuitive template - that people would actually use regularly. Any feedback or suggestion to help improve this spreadsheet is sincerely appreciated.

62 Responses to “Stock Quotes in Excel”

  1. Hi,

    I do financial modeling for private equity and have to say this is elegant work.

    One suggestion… When building something for other people to use, it’s ideal to avoid calculations resulting in errors when the user makes changes. Your formulas in F27:F36 yield a div by zero error when no symbol is entered in column B. A simple IF function would be an easy fix.

    Looking forward to playing with your work more as I built a beefy model myself to manage my own portfolio and your take is inspiring.

    Lawrence

    • Hi Lawrence,

      Happy to hear positive feedback about the stock price tool, especially from someone who’s obviously knowledgeable about Excel! I’ve gone ahead and taken your suggestion and added a simple IF statement in the % Change column (column F) to check if the stock price is 0 before calculating the percentage change. Come back to check for other tools that I’ll be releasing soon.

  2. I was excited to see your new creation — great job! However, it sure would be nice if the rest of us, less talented, could see the VBA macros, but they are password protected. I’m sure we could learn a lot from your work, but if you want to keep it to yourself, I understand. Again, thanks for sharing the cool final product.

    • Hi Roy,

      I’m glad that you liked the stock quote tool! At the moment, I’m planning to keep the source closed. Make sure to come back for more finance tools that I’ll be putting together!

  3. Thank you for sharing “Get Stock Quotes in Excel” model. It is indeed very useful. Can this also update Mutual Fund Prices. I tried but was not able to do so.

    Thanks again,
    Mukesh

    • Hi Mukesh,

      Thrilled to hear that you found the stock quotes tool useful! As Curtis and JB pointed out below, it seems like you can get some mutual fund prices from the free Yahoo Finance API.

      • Works for me.
        AADAX, GTAGX etc….

      • FYI:
        I entered my mutual funds into the tool and it works ok (for me).
        It shows Price, Change, and %Change, the only figures I track. If I chose to enter the Qty, it would probably compute those totals as well.

        I did noticed on the Example tab that the Daily/Yearly/Volume numbers are all zeros. (I don’t use them so that doesn’t bother me).

        Finally, I created a personal portfolio of my mutual funds on myyahoo…don’t know if that made it work or not.

        Hope this helps.

        JB

  4. Thanks for creating a user friendly stock quote excel spreadsheet. I enjoy using it. I think some of the users like myself will find it very helpful if you would consider adding a stock/company name formula as a available function on the spreadsheet.

    Henry

    • Hi Henry,

      You’re welcome! Glad that you’ve found the stock price tool useful. And thank you for your suggestion. I’ve gone ahead and added a new custom formula =StockName(stock_symbol), which returns the name of the company. However the company name returned is limited to a maximum length of 17 characters due to the Yahoo Finance API, so some of the company names may appear truncated.

  5. Very nice spreadsheet. Is there a way to paste link the quote results into another Excel spreadsheet?

    • Hi Don,

      I don’t think you can in a robust way. I tried linking a quote by setting a cell in a new Excel file equal to a cell containing a quote in the stock quote tool. Even though the quote is linked, the quote doesn’t auto refresh if you were trying to monitor the quote from the new Excel file.

      In short, it’s best if you build your tracking/analysis within this tool.

  6. Hi Yi,

    Thank you for the awesome portfolio spreadsheet. Do you know why I am only getting “#VALUE!” in Excel for Mac 2011? It works fine on my friends PC.

    Thank you in advance!

    Lyle

    • Hi Lyle,

      Unfortunately, the stock portfolio spreadsheet only works on PCs at the moment, but I will be looking into ways to get it to work on Macs in the future.

  7. N. Dag Reppen says:

    While I have access to several efficient portfolio tracking programs only one (MSN) that I am aware of allows me to add my own low and high target values to the tables in a convenient form and flag violations — and MSN is now replacing their portfolio tracker with an “improved” version that does not have that feature. Your Excel template saved the day! It took me just a couple of hours to create my own spreadsheet tailored to my needs. It is better than the MSN program I used previously as I can expand my trigger prices etc. beyond the single high and low values,flag near high and low 52 week conditions etc.
    High and low daily and 52 week values are not available for mutual funds, but that is not a big deal for me. I am very pleased with your template and it looks real good too. Thanks for your efforts in putting this together. NDR

  8. Hi Yi,

    This is a great tool! MSN changed the way they do the data connections so I’ve started using this instead. Is there any way to have it quote the DJIA? I’ve tried “DJI, DJIA, ^DJI ^DJIA” but none have appeared to work. I believe yahoo recognizes “^DJI”. Thanks!

    • James,

      You’re right. The ^DJI ticker is not working. Looks like the Yahoo Finance API is not providing that data. I will be looking for a workaround.

    • Hi James,

      The ^DJI workaround has been implemented! I’ve also added a few more functions for getting EPS, PE ratio, market cap, dividend yield, and dividend per share. Make sure you download the updated copy of the stock quote tool.

      Happy New Year!

  9. I’m another one who has used MSN for years in a custom portfolio update and tracking program… and as of this morning it no longer functions. So I discovered this as an alternate option. But the custom formulas will not work without moving them to my spreadsheet somehow. What might be my options short of completely rebuilding years worth of work? I also would need the symbols for the DJIA, NASDAQ, and S&P 500 since I use those also. Great work!

    • Hi Dave,

      The stock quote formulas work only within the tool. You can try moving the sheets in your current spreadsheet into the stock quote tool. Of course, you will then need to adjust some of the formulas in the sheets you moved, but hopefully this way you can minimize the work you need to do.
      The ticker for S&P 500 is ^GSPC and NASDAQ is ^IXIC. It seems like the Yahoo Finance API is not providing the data for DJIA. I will be looking for a workaround.

      • Thanks, Yi! I fairly quickly decided to use your spreadsheet as it is and simply “call” the values into mine. That was the lazy way out, but it works just fine. I will also be watching for some kind of replacement for Yahoo’s ^DJI… I found out also that it’s not working. Great job on this! It’s quite elegant.

  10. Yi,

    Great job with the Excel Quote mod. Just what I was looking for after my MSN Money connection bombed this morning.

    Question:
    Is there a reason you compute the % Change cell when Yahoo has that computed already?
    (When I click refresh, all the numbers match except % Change of ^GSPC only…it is a few percentage points off.)

    Not a big thing…just curious.

    Thanks again and keep up the good work.

    JB

    • JB,

      I’m super excited that more and more people are finding the stock quote tool helpful! I just added the StockChangeInPercent functionality. Cheers!

  11. Yi,

    Thanks for publishing this great tool. It is especially handy now that the MSN Money Central Stock Quotes no longer work. Adding to previous info on this subject; cannot get quotes for ^DJI, ^DJU, or ^DJT, but am able to get quotes for ^W5000, ^GSPC, and ^IXIC. Apparently it is the ^D connection that does not work.

    Best, and Thanks!

  12. Mark Sendelbach says:

    Yi,

    Thank you for providing this free tool. Like everyone else on this blog who used the MS MoneyCentral query tool, we appreciate you giving us an alternative.

    Just a suggestion for an enhancement. It would be great if you could add additional Yahoo tags to download for more columns. For example, Dividend Yield, P/E ratio, EBITDA, are tags that I could use.

    Thanks again for creating this great tool. Look forward to future visits to your website.

    Sincerely,
    Mark

    • Mark,

      Thank you for your suggestion. I have added functionalities to grab the dividend yield, P/E ratio, and EBITDA. Make sure you download the updated stock quote tool.

  13. Yi,

    Great tool! I’ve been looking for something since MSN Moneycentral macros stopped working in Excel, and I like your tool much better. Great job!

    A request for your consideration: I like to keep track of the P/E. Can you add either the TTM EPS or the P/E ratio to the table? Thanks!

    • Dave,

      Thank you for the suggestion. I’ve gone ahead and added both the EPS and P/E ratio. Make sure you download the updated stock quote tool!

  14. Great Tool! Thanks for creating this wonderful spreadsheet. Is there anyway for it to work with stocks like Berkshire Hathaway that have unusual stock symbols (BRK.B)?

    Thanks,

    Andy

    • Hi Andy,

      You’re welcome! Try BRK-B. In general if you’re having trouble finding a stock symbol, try the symbol used in Yahoo Finance as the stock quote tool is pulling all the information from the Yahoo Finance API.

  15. Are Canadian stock quotes available?

    • Roy,

      Try the stock symbol used in yahoo finance. I was able to get the stock quote for Air Canada with the symbol AC.TO.

      • Thanks for the information. However I have managed to get MSNStockQuotes working again so I will stick with what is familiar to me for as long as it keeps working. I assume the .TO refers to the TSE.

        Thanks so very much.

  16. Thank you Yi:

    Most excellent creation. Along with my own portfolio I’m the treasurer of a couple non-profits and responsible for their investments. MSN killing the stock quotes tool ruined my day. I was able to do my own limited workaround using Yahoo Finance, but your design is much more robust and elegant.

    As others mentioned moving your sheets into my book didn’t work, but going the other way works just fine.

    Thanks for sharing. Excel Clout is bookmarked.

  17. Richard Lejeune says:

    Yi,

    Great tool. Eventually (after trying 2 other out of date products that didn’t work) found it after MSN’s tool bombed.

    Any chance of getting corporate bond quotes from FINRA (or elsewhere) into Excel?

    Thanks,
    Richard

  18. Richard Lejeune says:

    Yi,

    One other question. Is there a way to get the bid price of a stock as well as the last price? MSN was able to do that. The bid price is important for many of the illiquid issues I trade since the bid and last prices can vary significantly.

    Thanks again for the great application!
    Richard

  19. Could you add an open column? I would like to transfer the data to an EOD chart program.

    Thanks

  20. Lee Tanner says:

    The stock quotes workbook are very fine. Thank you.

  21. Thanks Yi.

  22. Excellent spreadsheet and incredibly superb updates from suggestions. I was fighting updating multiple spreadsheets previously with MSN Money Query. Now that I have switched to Stock Quotes, seeing no reason to go back.

    Is there a working symbol for CLG15.NYM?http://finance.yahoo.com/q?s=clg15.nym

    Thank you

    • Hi Gregg,

      Make sure you let others know about this awesome stock quote tool! I’ve stress tested the tool to grab stock prices for 500 stock symbols with no problem. The only caveat is try not to add more than 50 stock symbols at a time to avoid Excel hanging for too long. Once all the stock symbols have been added, refreshing the stock prices is fairly smooth.

      I just tried “CLG15.NYM”, and it seems to work.

  23. Impressive work Yi! Very robust. This is a real keeper! :)

  24. Thank you, Thank you.
    This is awesome. After the MSN Stock Quotes add-in stopped working in December, I have been waiting to see if MSN was going to fix it or not. After reading others with same issues, I discovered this. The only thing I had in the MSN add-in was date and time of last trade. Minor loss considering. I simply copied my sheets into your’s and changed my old MSN calls to your’s and presto! Back in Business. I even tweaked your Portfolio tab to add cash account, and linked Symbol and quantiies to my sheets. I wasn;t going to use that sheet but now I like that it is all synched and have a Graphic Summary Page. Great Job!

  25. Wonderful and very useful solid solution. Hats off. Thank you!

  26. Howard Nott says:

    This is absolutely the best stock portfolio manager in existence!! That you share it FREE is very generous! Before retirement I held many positions over 40 years in the IT department of a large corporation and I have a personal hobby of writing visual basic apps for home use – including Excel VBA macros. I intend to use your download directly and also to use some of the functions in other applications. If still working I would try to hire you! If I could, I would invite you for a beer and conversation.

    Thank you!!
    hdn

  27. Roger Schvaneveldt says:

    This is a very nice and useful piece of work. Thank you for making it available.
    On the morning of 2/2/15, the quotes seem to be somewhat out of whack. Of course, the market is volatile at that time so maybe that is the problem. cheers.

  28. Great job with the spreadsheet, the best stock quote tool I’ve seen.

    One thing though, could you, please, add the Average Daily Volume?

    Thank you!

  29. Thanks for the spread sheet, it’s very useful but it does not work with Canadian mutual fund symbols, for example if I type symbol F00000JRZU.TO (TD Canadian Equity Class – I) there is no value.

    Thanks
    Pk

    • Hi Pk,

      It seems like the Yahoo Finance API is not providing data for Canadian mutual funds. What a bummer… If anyone knows any reliable data source for Canadian mutual funds that can be accessed through an API, I’d be happy to look into it.

  30. Sal Vitale says:

    Great Worksheet!
    I downloaded and putting it to good use for our stock club.
    Thank you

  31. Just a note to say that this is a brilliant spreadsheet. Does just what I want – Cheers!

  32. Hi,

    This tool is brilliant and simple to use. I guess stocks trades in BSE & NSE (indian markt) are not feasible in the file. Can you tell me how i can get this data using your excel sheet.

    Regards,
    Shyam.v

  33. Hi there, thank you so much for this!
    MSN Money removed the stock quote function last Dec so this is invaluable.
    Thank you again!

  34. Great tool – Thank you.

    Is there a way to get “Year Low” and “Year High” quotes for mutual funds, or could that be added?

    Thanks again

  35. Yi – This little spreadsheet is fast and really works great! Only thing I would add is a column that had the actual stock name that would look up the symbol or vice versa … but no complaints, you did a wonderful job!

    Alvin

  36. Yi,

    I would like to prevent the spreadsheet from ‘refreshing’ at open. I tried setting the option for calculations to Manual(F9) but it did not work.

    It is not a big deal…just curious.

    Thanks and keep up the good work.

    JB


Leave a Comment

Your email address will not be published. Required fields are marked *

* required