Search


Subscribe to AFM


Subscribe to AllFinancialMatters
by Email

All Financial Matters

Promote Your Page Too

The American's Creed

Site Sponsors

Books I Recommend


AFM in the Media


Money Magazine May 2008

Real Simple March 2008

Blogroll (Daily Reads)

« | Main | »

How to Insert Refreshable Stock Quotes Into Excel

By JLP | 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?

Topics: Financial Math Basics, How to... | 27 Comments »


27 Responses to “How to Insert Refreshable Stock Quotes Into Excel”

  1. ace Says:
    November 15th, 2006 at 2:21 pm

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

  2. Frugal Frugalson Says:
    November 15th, 2006 at 2:36 pm

    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. efipo.com Says:
    November 15th, 2006 at 3:49 pm

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

  4. Bob Says:
    November 15th, 2006 at 4:14 pm

    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. Binary Dollar Says:
    November 15th, 2006 at 4:25 pm

    JLP is my hero.

  6. JLP Says:
    November 15th, 2006 at 4:40 pm

    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.

  7. Bob Says:
    November 15th, 2006 at 5:21 pm

    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.

  8. JLP Says:
    November 15th, 2006 at 5:24 pm

    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.

  9. efipo.com Says:
    November 15th, 2006 at 10:43 pm

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

  10. bluntmoney Says:
    November 16th, 2006 at 8:57 am

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

  11. efipo.com Says:
    November 16th, 2006 at 1:07 pm

    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.

  12. » Weekly Blog Round-Up on Consumerism Commentary: A Personal Finance Blog Says:
    November 17th, 2006 at 9:44 am

    […] 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. […]

  13. Generation X Finance » November 17th Friday Five Says:
    November 17th, 2006 at 6:34 pm

    […] 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. […]

  14. itconsultant Says:
    January 15th, 2007 at 2:19 am

    Great tip

  15. Josh Says:
    January 30th, 2007 at 8:49 am

    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 “http://money.cnn.com/quote/quote.html?symb=MSFT,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.

  16. David Atkinson Says:
    February 3rd, 2007 at 2:29 am

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

    (1) This webpage http://www.allworldsoft.com/software/7-066-101quote.htm 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 http://www.gummy-stuff.org/Yahoo-data.htm 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.

    /r
    David

  17. Cavemanus Says:
    December 16th, 2007 at 10:35 pm

    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:

    http://finance.yahoo.com/q?s=MSFT,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!

  18. Eric Says:
    February 4th, 2008 at 9:31 am

    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.

  19. Gary Says:
    October 7th, 2008 at 5:25 pm

    Great Information!

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

    Gary

    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.

  20. - Are You Riled Up? - » Blog Archive » Comment on How To Insert Refreshable Stock Quotes Into Excel By Gary Says:
    October 8th, 2008 at 9:35 am

    […] 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] […]

  21. Shan Says:
    December 2nd, 2008 at 9:49 pm

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

  22. John Says:
    January 4th, 2009 at 9:39 am

    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:

    http://www.xlconsulting.net/flink.php

  23. Car Finance Says:
    May 14th, 2009 at 8:36 am

    Very nice…

  24. CHANDRAMOULI Says:
    August 20th, 2009 at 6:21 am

    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 http://WWW.nseindia.com
    Can somebody help?
    Thanks

  25. guaira Says:
    October 18th, 2009 at 3:04 pm

    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.

  26. LOVE_MOSS_NOT Says:
    December 29th, 2009 at 11:00 pm

    Great tip but boy this blows…

  27. steve k Says:
    November 24th, 2010 at 10:48 am

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

Comments