Newsletter
Guest Post of the Month for April 2003
Did you miss some articles?
Don't worry, we saved them for you.
Archives
How to Pull Stock Quotes Into Microsoft Excel
C. Jane Bachman, CPA
Here is a tool that will save you time and track your portfolio as your investments climb back up in market value. If you audit clients with large portfolios, such as what is found in pension plan audits this tool can also serve as an additional verification of fair market value. The best part is that, if you own Microsoft excel, you already own the tool!
Instructions:
After you start Excel, enter your stock symbols beginning at A5 and go down.
Click Data on the menu bar, point to Get External Data, and then click Run Saved Query.
Click Microsoft Investor Stock Quotes, then click the Get Data Button (or click twice)
Click the Parameters button.
Click Get the value from the following cell option button Now you will click on the collapse dialog button & select the cell range containing the ticker symbols. I just pass my mouse over the symbols but you can type an “=” sign, then A5:A50.
When it asks you where you want to place the results, click on cell B2 in the same sheet. (Every time I tried to place it into a new worksheet, I lost data.)
Click OK and wait while it pulls the data in.
Take out whatever columns you don’t want. Now make columns for location, purchase date, cost per share, number of shares purchased, total cost, and, if you want, paper gain or loss. (I took out fairly many columns) If you leave the news column, it takes you to the business news on MSN Money. If you leave the company name it’s a link to the web page of that company.
You might have to get out your Microsoft Office disk and load in some other functions if this feature wasn’t originally installed on your PC. If it asks you to, place your disk in and it will automatically load what you need.
These stock prices are 20 minutes old, in case that interests you. You can design your spreadsheet so that every time you open the page the values update or that they update automatically even while you are analyzing them. If you are using the spreadsheet as an audit tool you will have to remember to copy the information to disk so that it doesn’t update out of your year-end values.
Here is a tool that will save you time and track your portfolio as your investments climb back up in market value. If you audit clients with large portfolios, such as what is found in pension plan audits this tool can also serve as an additional verification of fair market value. The best part is that, if you own Microsoft excel, you already own the tool!
Instructions:
After you start Excel, enter your stock symbols beginning at A5 and go down.
Click Data on the menu bar, point to Get External Data, and then click Run Saved Query.
Click Microsoft Investor Stock Quotes, then click the Get Data Button (or click twice)
Click the Parameters button.
Click Get the value from the following cell option button Now you will click on the collapse dialog button & select the cell range containing the ticker symbols. I just pass my mouse over the symbols but you can type an “=” sign, then A5:A50.
When it asks you where you want to place the results, click on cell B2 in the same sheet. (Every time I tried to place it into a new worksheet, I lost data.)
Click OK and wait while it pulls the data in.
Take out whatever columns you don’t want. Now make columns for location, purchase date, cost per share, number of shares purchased, total cost, and, if you want, paper gain or loss. (I took out fairly many columns) If you leave the news column, it takes you to the business news on MSN Money. If you leave the company name it’s a link to the web page of that company.
You might have to get out your Microsoft Office disk and load in some other functions if this feature wasn’t originally installed on your PC. If it asks you to, place your disk in and it will automatically load what you need.
These stock prices are 20 minutes old, in case that interests you. You can design your spreadsheet so that every time you open the page the values update or that they update automatically even while you are analyzing them. If you are using the spreadsheet as an audit tool you will have to remember to copy the information to disk so that it doesn’t update out of your year-end values.
Disclaimer 
