Thursday, September 01, 2005

Use MS Office to Track Your Stocks

A simple way to achieve this is to use Excel and Access in MS Office 2002 or Office 2003.

Excel's Web Query feature allows a spreadsheet to be automatically updated by data from the Web each time you open the file. You can set up a Web query in a spreadsheet that will give you the latest stock prices from a web site:
  1. Open a blank worksheet and click in cell A1.
  2. From the Menubar click Data Import External Data New Web Query. Excel will then prompt you for an Internet connection if you're not already connected.
  3. In the Address box, enter the URL of the Web page that contains the stock statistics and then click Go. For example the live stock statistics on the freerealtime.com site are easily linked.
  4. Click the yellow boxes with arrows next to the blocks of text you want to import. When clicked, the yellow boxes turn into green boxes with check marks.
  5. Click the Import button.
To have the Web query update the spreadsheet every time the file is opened:
  1. Right-click a blank area of the main toolbar and select External Data.
  2. Click the Data Range Properties button.
  3. In the Refresh Control section, select Refresh Data On File Open. Click OK.
  4. Add column headings (ie. field names) to each data column as needed.
Now the spreadsheet will automatically update the web data each time the file is opened, or accessed by another Office application such as Access.

If you need to take a snapshot of the data in Excel:
  1. Select the data you want to copy. From the Menubar click Edit Copy.
  2. Click on the cell where you want the snapshot of the data to appear.
  3. From the Menubar click Edit Paste Special.
  4. Click the Values And Number Formats checkboxes, and then click OK.
  5. From the Menubar click Edit Paste Special.
  6. Click the Column Widths checkbox and then click OK.
Finally you can use this date in Access by creating a new database in Access and then:
  1. From the Menubar click File Get External Data Link Tables...
  2. In the Link dialog, select Microsoft Excel (*.xls) from the Files of type combobox, and click Link.
  3. Follow the the Link Wizard to finish, and you will have a table linked directly to your spreadsheet.

No comments: