One of the neatest functions of Excel is the ability to 'scrape' share prices from financial websites - for free! Once you have this price in your spreadsheet, you can use it to update the value of your holding, the current yield and your capital loss or gain.
So here is a quick tutorial on how to do this: I've tried to make this accessible to Excel beginners.
I'm using Excel 2010 and Windows 7 but this function is available - in a similar form - in most versions of Excel and previous versions of Windows. (If you have any difficulty using a different version of Excel, try using the built-in 'Help' function.)
1) Find a website with the financial data that you want.
For this example I've chosen BP's data on Yahoo Finance, which is shown above.
Not every website will work easily - some will only copy unhelpfully large portions of the web page.
2) Copy the website address (which should appear at the top of your browser):
http://uk.finance.yahoo.com/q?s=BP&m=L&d= (the shortcut is to 'right click' the mouse and select 'copy' from the menu that appears on the screen)
3) Open a blank Excel worksheet and click on any cell.
4) Click on 'Data' (which should appear along the top of your spreadsheet) and then 'Data from Web'. You should see a box appear containing a website - it will depend on your setup but typically it will display a search engine page. Ignore this.
5) Click on the address line at the top of the browser page and paste in the website address that you copied previously (the shortcut is 'right click' the mouse then select 'paste' from the menu). Your selected web page should now appear in the box.
6) Scrape! The page will look a little different - what you are looking for are little yellow boxes with black arrows. Click on the arrow next to the data you want to 'scrape' - in this case I have chosen the arrow next to 'Previous Close' (or the closing price of BP on the previous day'. The arrow should change to a tick, as shown below (click on the image to see a larger version):
7) Import. Now look for a button on the bottom right of your screen called 'Import' and click on it. This will bring you back to your Excel page, with a little message asking you to confirm where you want the data to appear: click on 'OK'.
8) Watch the Excel magic work. After a bit of electronic jiggery-pokery you should see the data from the website appear on your Excel worksheet, like this
9) Save the Excel worksheet. You can update the data - in this case BP's share price data - by clicking on 'Data' (at the top of the Excel sheet) and then 'Refresh All'. Updated information will appear from the Internet ether.
10) Add more web data links. The easiest way I have found is as follows:
- copy the rows of the BP data (i.e. rows 1 to 7 in the example above)
- paste these somewhere below the original rows you have copied (i.e. don't over-paste)
- click somewhere on the new data, then RIGHT CLICK and click on 'Edit Query' (in the menu that appears) - this should bring up the original Yahoo BP web page
- now comes the clever bit: if you look at the website address it will look like this: http://uk.finance.yahoo.com/q?s=BP&m=L&d= : can you see the 'BP' in the address?
- edit the address with the symbol for another company: for example change the 'BP' to 'BA' but leave the rest of the address the same. Then click on 'Go' (next to the address bar) - this should take you to the financial data for BAE Systems
- you can now 'scrape' similar data for this company into your Excel worksheet
You can do this for all share prices and for data for many other securities, although you may not be able to find 'scrapeable' data for many corporate bonds.
In this way you can drive the whole of your spreadsheet from the 'Data' refresh function.
I hope this was reasonably clear. Good luck!
I am not a financial advisor and the information provided does not constitute financial advice. You should always do your own research on top of what you learn here to ensure that it's right for your specific circumstances.