This tool uses the "External Data" option, a feature available in both Calc and Excel.
1. create "n + 1" number of sheets, where "n" are the number of stocks you wish to add.
2. rename the sheets as shown in the picture.
3. To add your first stock select cell "A1" of "Sheet1"
4. From the main menu select Data > Import External Data > New Web Query...
In OpenOffice Calc select Insert > Link to External Data...
5. A "New Web Query" window will pop up as shown here.
6. In the "Address" field type the address of the page to get the stock quote from and hit the "Go" button.
A good and reliable example is the quotes page on http://finance.yahoo.com
http://finance.yahoo.com/q?s=AU.L
The text in red above represents the stock that you wish to add to your portfolio.
You can look up the symbol for your stock at http://finance.yahoo.com/lookup
7. When the quote page loads up, click the arrows and turn them in to "tick marks"
as shown in the picture above.
In OpenOffice Calc pick the table/range containing the appropriate data. To refresh
quotes automatically, check the "Update every" box and pick refresh frequency from the drop down.
8. Press the "Import" button to load stock quote data in the current sheet at cell "A1".
9. Now, select the "Portfolio" sheet and create a set of headers, you wish to follow, on the first
(or next available) row. For example
Company, Quantity, Average Price, Invested Amount, Current Value, Percentage Gain, Last Trade,
Last Trade Time, Previous Close, Bid, Ask, Volume
10. Finally, connect each cell under each header to its respective data on "Sheet1".
This picture here shows the formula to connect the selected cell to pick "Last Trade" data from "Sheet1"
The formula for cell G2
is =Sheet1!D3
Follow steps 3 - 10 to add the rest of your stocks.
Add some color to your portfolio
To add colored indicators for "Current Value" and "Last Trade" cells follow these steps for each cell.
1. Select the "Conditional Formatting" feature from the "Format" menu. Format > Conditional Formatting...
2. A "Conditional Formatting" window will pop up as shown.
3. Compare cell values such as "Last Trade" with "Previous Close"
4. Click the "Format..." button to display the "Format Cells" window and pick a color for each condition.
Refresh quotes with a single click
Select the "External Data" toolbar from the main menu. View > Toolbars > External Data
The pound (!) button on this toolbar when clicked from the "Portfolio" sheet will refresh all stock quotes from yahoo.
Next > Successful investment strategy