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.
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.
Now select where you would like the starting cell or you can also choose a new work sheet. Click OK.
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.
Is this a feature you’d actually use?
Daily Email Updates
You can get our how-to articles in your inbox each day for free. Just enter your name and email below:
| Similar Articles | Featured Wiki Articles |
| Latest Software Reviews | Quick Linux Tips |
| Geek Arcade | Popular Forum Threads |
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.
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.
Steve,
Thank you for the extra info!
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
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!
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
Yes I’d use it, but how do you do this in EXCEL2002
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!
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!
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.
Hi there – having problems getting UK prices to work, such as GB:VOD – any thoughts?
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!