SEARCH

How-To Geek

Download and Keep Track of Stocks in Excel 2007

This article was written by MysticGeek, a tech blogger at the How-To Geek Blogs.

Microsoft Excel is used by companies and individuals to keep track of and compute virtually anything that requires numbers. If you are computing financial symbols, did you know there’s a way to have Excel automatically update those values online?

You can use smart tags in Excel to keep track of your stock quotes.  (Keep in mind you do need an Internet connection for this).  With this feature Excel will connect to MSN Money Central to download the information.

First we need to enable the smart cell feature by clicking the Office Button \ Excel Options \ Proofing \ AutoCorrect Options and click on the Smart Tags tab. Make sure and place a check next to Financial Symbol.  Click OK twice to get back to your worksheet.

1

Now that smart tags are enabled all we need to do is enter in our favorite stock symbol using all capital letters (in this instance I am using Microsoft).  Now hover the mouse over the lower left corner of the cell until the smart tag appears and click.  Select Insert refreshable stock price.

1

Now select where you would like the starting cell or you can also choose a new work sheet.  Click OK.

1 

Excel will go retrieve the information regarding the stock from MSN Money Central.  This will also include hyperlinks to additional information which will open in a separate browser.

1

Is this a feature you’d actually use?

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 01/2/08

Comments (18)

  1. d steele

    I’d use it if:
    1. I could limit the amount of information which is returned (eg to just the current stock price)
    2. There was some way to force it to acquire a smart tag for a ticker symbol that it doesn’t recognize.

  2. Steve Kalman

    I love it. However, I had the same questions as the previous commenter, so I did some research and some experimentation. Here’s the results.

    I created a multi-sheet workbook. The front sheet is called holdings. The other sheets were created using this tool. I put in a stock then chose the smart tag as above, but selected “on a new sheet” rather than entering a cell reference. Then I renamed that sheet with the stock symbol.

    To insert the current price into the holdings sheet, select the price from the new sheet (Cell E4) and then click on copy. Go back to the main sheet (Holdings) rt-click in the destination cell and choose paste-special. Then just click OK to accept the defaults.

    For symbols that aren’t automatically recognized, pick one of the sheets created with these instructions, then right click and choose “rename or copy” From the window, choose to make a copy after the last. It will name it, for example, MSFT(2), if you were copying the MSFT worksheet. Right click and rename it to the stock you want. Then right click on the name of the stock (cell A4) and choose to edit query. That will open a new dialog box.

    Click anywhere in the address and scroll all the way to the right (or just hit END) and then edit the symbol name to the one you want. Click on GO, then on import. Your new page will now reflect the stock of your choice.

    Hope this helps.

  3. mysticgeek

    Steve,

    Thank you for the extra info!

  4. Steve Kalman

    OK, I have a little more to add. When I tried it this morning I found that it didn’t update. Here’s the fix.

    Instead of pasting the cell from one worksheet to another, a cell reference is required. (Excel calls it a “3D reference” in case you want to read more)

    Assume the main sheet is called holdings and the stock data sheet is called MSFT. In the target cell in the holdings sheet use this formula =MSFT!e4 (That’s an equal sign, the stock symbol, an exclamation point and then the cell reference.)

    That will reference cell e4 (previous close) in the holdings sheet. When you start up on any day, use CTL-ALT-F5 to refresh all sheets. Depending on your security settings, you might need to give permission to access an external source.

    If you want intraday trading, use cell d4 instead (last price, delayed 20 mins)

    Hope this helps.

    Steve

  5. Steve Kalman

    Moderator: please delete the 8:59 am reference and use the following instead. I found two edits that might cause confusion.

    Sorry for the extra work, but thanks for the underlying tip!

  6. Steve Kalman

    OK, I have a little more to add. When I tried it this morning I found that it didn’t update. Here’s the fix.

    Instead of pasting the cell from one worksheet to another, a cell reference is required. (Excel calls it a “3D reference” in case you want to read more)

    Assume the main sheet is called holdings and the stock data sheet is called MSFT. In the target cell in the holdings sheet use this formula =MSFT!e4 (That’s an equal sign, the source sheet name, an exclamation point and then the cell reference.)

    That will reference cell e4 (previous close) in the source sheet. When you start up on any day, use CTL-ALT-F5 to refresh all sheets. Depending on your security settings, you might need to give permission to access an external source.

    If you want intraday trading, use cell d4 instead (last trade, delayed 20 mins)

    Hope this helps.

    Steve

  7. RSK

    Yes I’d use it, but how do you do this in EXCEL2002

  8. Ludo

    it doesn’t seem to work with other symbols like APPL or GOOG ! (only MSFT ??)
    I tried also currencies USDEUR but no reaction from the smart tag option!

  9. mysticgeek

    Ludo,

    This is a great point. I have also entered in various stocks that did not work. I am not one to start claiming “conspiracy” … however … this does seem odd.

    The Geek and I will look into this and see if we can get a better explanation. Thanks for your comment!

  10. James

    Quote by Ludo: “it doesn’t seem to work with other symbols like APPL or GOOG !”

    Really it doesn’t work with APPL, too bad Apple’s symbol is AAPL (not APPL) and it does work stupid.

  11. Nigel Beynon

    Hi there – having problems getting UK prices to work, such as GB:VOD – any thoughts?

  12. Kevin B

    I would use this A LOT if you would help me with a “how-to” for Mac Excel 2004.
    Can’t seem to find the same tools in the Mac version of Excel…
    THANKS!

  13. sameer

    can i download from yahoo finance site if possible, kindly suggest, coz i need indian stocks to be downloaded.

  14. excell stock add in 2003,07

    I wanted to limit the information retrieved by office. I used an add-in for 2003 using office 2003 which allowed me to limit it just to stock price (and other info if desired). http://www.microsoft.com/downloads/details.aspx?FamilyID=485FCCD8-9305-4535-B939-3BF0A740A9B1&displaylang=en

    This add in worked great for me running windows xp, office 2003 and saving the file as an .xls format.

    I tried viewing the worksheet using office 2007 on a different machine and it didnt work. I looked up 2007 solutions and did not find anything that was as easy to used etc….

    I tried the install of the addin from the above link in office 2k7 w/ XP pro and it worked exactly as needed. the contents in the cell are as follows =MSNStockQuote(A25,”Last Price”,”US”)
    A25 is a change i made in my work sheet to cell reference to the ticker need, the actual ticker can be placed in there aka msft.

    When i installed the add-in i had my work book open. I had to close excell and reopen for the addin tab to be added to the top of the ribbon format. Once i opened excell it was available in 2007 as it was in 2003.

    If you view the document on different machines, you will have to install the add-on to what ever computer you are working with. Not sure if it matters between user profiles or not.

    I currently have no mac solution. Not sure about indian stocks or using other sites such as yahoo.

    Hope this helps and dont forget to develop the exit plan of your investment strategy before investing.

  15. Steve

    It works great, but seems to cut out after a few auto-refreshes. I can’t say it’s my connection, because everything else seems to run great. Any other query I have on auto-refresh works fine as well. As a matter of academia, I tried to create a table from the refreshed data by linking it in 20 minute intervals to consecutive cells. Since I keep using the “IF” function and the data changes over time, I can’t keep a “log” of the data that’s passed throughout the day. I’d ultimately just end up with the final quote. This has prompted me to attempt and fail miserably at VBA code. Any suggestions?

  16. Rochel

    Hi guys,

    I have a serious problem! in Auto correct forum, on the smart tags tab, I have 2 smart tags only (date & name-email)
    I tried to locate the smart tag’s folder in “Documents and Settings\username\Application Data\Microsoft\Smart Tags\Exceptions” (Microsoft help center) but the folder ‘smart tag’ doesn’t exist on my computer.
    I really need the financial symbol’s smart tag!
    any thoughts?
    Thank!
    Rochel

  17. Cynthia

    No more Smart Tags in MSOffice

  18. Dennis Mickey

    I also would like to know how to do this in Excel 2002.

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!