How to Insert Refreshable Stock Quotes Into Excel

November 15, 2006

Warning: this is a really long post!

There’s a cool little tool available for Microsoft Excel users that will allow them to insert refreshable stock quotes into their spreadsheets. Here’s a step-by-step tutorial on how to do it:

Before we start, you must have Excel 2003 as this feature is not available on older versions of Excel.

1. Download and install the Office 2003 Add-in: Stock Actions for the Research Task Pane.

2. Once the add-in is installed, open Excel and go open the Tools menu on the main menu bar and click on “Research.”

Excel Stock Quote Add-in

This will open a pane on the side of your spreadsheet that look like this:

Excel Stock Quote Add-in 2

3. Enter the ticker symbol of the stock you want to look up and then click on the down arrow beside the box that contains the words “All Reference Books” and scroll down and click on “MSN Money Stock Quotes” and hit the return key.

Excel Stock Quote Add-in 3

4. Now you should be looking at something like this:

Excel Stock Quote Add-in 4

5. Find the grey box that says “Insert Price” and click on the down arrow, scroll down and choose “Insert Refreshable Stock Data.”

NOTE: Make sure you have selected the cell where you want to insert the information.

Excel Stock Quote Add-in 5

6. Now your spreadsheet should look something like this:

Excel Stock Quote Add-in 6

CONGRATULATIONS! You have just inserted your first refreshable stock quote!

As you probably noticed, the quote isn’t in the most useable form. I usually title one of my worksheets “Downloads” and then reference that sheet when I need a refreshable quote. If you don’t know what I’m talking about, let me know and I’ll try to write up a tutorial on that too.

One last thing…

In order to easily update your refreshable stock quotes, you’ll want to make the “External Data” toolbar viewable. To do this simply go to the View menu and click on Tools and then select “External Data.”

Excel Stock Quote Add-in 7

It should insert a little toolbar that looks like this:

Excel Stock Quote Add-in 8

Then, whenever you want to refresh your quotes, you simply click on the icon with the red exclamation point in the External Data toolbar and your numbers should refresh!

There you have it. Now you know how to insert REFRESHABLE stock quotes into your very own Excel spreadsheets. Was this helpful?

27 responses to How to Insert Refreshable Stock Quotes Into Excel

  1. Great tip. If only you could choose the format. Thanks.

  2. Thanks! I’ve always wondered about how to include updateable stock and mutual fund prices in a spreadsheet, but never bothered to investigate further.

  3. JLP thanks a lot! You’ve cut my research time down like 45 min. Great tip!

  4. This is good.Is there a way you can reference the the stock Price from another excel file where I track my portfolio.That would nice for tracking current P&L as I wont need to update the price frequently.

  5. Bob,

    If I’m understanding your question, yes, you can interlink different spreadsheets. You have to have both spreadsheets open and then it’s just a matter or referencing one cell to the cell in the other spreadsheet. Keep in mind that in order for the data to refresh, both spreadsheets have to be open.

  6. Thanks JLP.What if I link from a different worksheet within the same file.If that works then I guess I only need to refresh the sheet where the links reside.Anyway I am still on excel 2000 so I need to upgrade soon.Will definitely save me a lot of time as I manually update now.

  7. Bob,

    Of course you can link to another cell in another worksheet inside the same file. Here’s how:

    1. in the destination cell type: =

    2. then click on the cell in the worksheet that you want to link to and hit the enter key

    3. you should be linked.

  8. hey JLP. Do you know if you could add a passed stock amount and the new one so it would calculate return?

  9. Cool stuff. I had no idea you could do this. Now I just need to find some stocks to watch 😉

  10. here’s some food stocks that i’ve bought into Domino’s(DPZ) and CMG (Chipotle Mexican Grill). Ive gotten some incredible returns with them this year.

  11. Great tip

  12. There is a way that generates a table that is both updatable and in a more usable format. Here are the steps:
    -creat a new tab in your spreadsheet
    -Go to Data>Import External Data>New Web Query
    -At the top of the query box, copy this address in “,LIFC,CHC” (minus the quotes)
    -change the tickers to whatever you want quotes for and click the Go button
    -There will be a series of arrows on the web page. Click on the arrow right next to the ticker table. The arrow will turn green.
    -click the Import button at the bottom.
    -Import the data into a new or existing worksheet.
    -The table you selected will appear in your worksheet
    -You can reference the individual prices on this query to tie to your portfolio or other research
    -To refresh, simply right click the upper left most cell and select the red exclamation mark to refresh the data.

    There you have it. Enjoy.

  13. I download financial data into the 97 Excel version. I’ve tried two methods, both seem to work.

    (1) This webpage will provide you with the program 101quote. 101quote is a stand-alone program and DDE (dynamic data exchange) compatible with 97 Excel. Load your stock/bond/fund symbols into 101quote and Excel will see the returned financial data.

    (2) The webpage will provide you with an Excel macro that will download Yahoo financial data directly into 97 Excel.

    Thanks for the information on how to compute my personal rate of return.


  14. I did the same thing with Yahoo! Finance. The difference is you can create your own view and then use that URL in your web query. In that view, you can call for dividend data such as ex-date, pay date, amount and yield.

    For example:

    1) Go to:,XOM,C&d=s

    Use your own tickers.

    2) You will see a row at the top like this:

    View: Summary | Basic | Performance | [New View]

    Click New View.

    3) Once there, create a view using the drop down menus. Don’t forget to name your view or your setup will be lost!

    4) Enter your tickers into your new view and copy that URL.

    5) Follow the steps above using the Yahoo! URL and select the appropriate arrow.

    6) Enjoy your data!

  15. Do you know if it is possible to get the quoite information just by typing the ticker in a excel cell?

    For instance, I would type “MSFT” in one cell and the program would automatically get the stock price for me, without having to create a new query each time.

  16. Great Information!

    Statement below, I can I reference my “download” sheet to my reference sheet when I need a refreshable quote?


    I usually title one of my worksheets “Downloads” and then reference that sheet when I need a refreshable quote. If you don’t know what I’m talking about, let me know and I’ll try to write up a tutorial on that too.

  17. Try this: where you’ll find a free real time data server for Excel that get updating quotes from yahoo for worldwide stocks and options.

  18. you can also use Financial Link for Excel to load updating stock quotes, financial statements, estimates, price histories into Excel. Easy-to-use formulas to load the data and also wizards to help you creating the formulas.

    More detailed information here:

  19. Hi,

    It looks to be great and simple. But I live in INDIA and invest in stocks here. How to customize it for getting F&O ( FUTURES) QUOTES from National Stock Exchange of INDIA, site
    Can somebody help?

  20. Awesome tip! It works with Excel 2007 as well, although things are in slightly different places. Took me maybe 10 more seconds, but in the end it is all the same.

  21. Great tip but boy this blows…

  22. JLP, thanks for helping me with this tip.
    Can Option price quotes also be inserted in excel?

Trackbacks and Pingbacks:

  1. » Weekly Blog Round-Up on Consumerism Commentary: A Personal Finance Blog - November 17, 2006

    […] Free Money Finance wonders when people will learn that you can’t beat the market over time. Five Cent Nickel rounds up a number of rewawrd credit cards. AllFinancialMatters has an informative guide to inserting refreshable stock quotes into Excel. […]

  2. Generation X Finance » November 17th Friday Five - November 17, 2006

    […] How to Insert Refreshable Stock Quotes in Microsoft Excel –  AllFinancialMatters has come through with a great how-to on using Excel to insert and update stock quotes. […]

  3. - Are You Riled Up? - » Blog Archive » Comment on How To Insert Refreshable Stock Quotes Into Excel By Gary - October 8, 2008

    […] I usually title one of my worksheets “Downloads” and then reference that sheet when I need a refreshable quote . If you don’t know what I’m talking about, let me know and I’ll try to write up a tutorial on that too.[Continue Reading] […]