Tuesday 7 February 2012

Excel Tutorial: Scrape a Share Price!

I recommend that you keep track of your savings and investments using a spreadsheet.

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.

12 comments:

  1. Does not appear to work in Microsoft Office for Mac 2011

    ReplyDelete
    Replies
    1. Sorry about that. I use Windows but I assume that the functionality is still there somehow using a Mac - I suggest you look at the relevant topic in the 'Help' function. If you find a workaround do let us know!

      Delete
  2. Great article, you learn something every day.

    But like "Anonymous" I tried this on a Mac and cannot get it to work.

    I'm using version "X" of Excel (years old, never bothered upgrading) and the "Data" menu option doesn't have the sub-option "Data from web".

    I suspect that this version of Excel is over 10 years old and perhaps building in this kind of scraping functionality hadn't been thought of back then.

    ReplyDelete
    Replies
    1. Sorry Mac guys, life is not going to be so easy for you:

      It seems that "Excel 2010 and 2007 for Windows have the option to import data from the web. Excel for Mac users don’t."

      http://excelsemipro.com/2011/01/import-data-from-the-web-in-excel/

      You might find a (possibly more complicated) workaround at Microsoft Support, e.g.
      http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B187364

      Delete
  3. See here for Excel 2011 for Mac:
    http://www.dummies.com/how-to/content/using-a-web-query-to-load-tables-in-excel-2011-for.html

    ReplyDelete
  4. For Excel on MAC:
    File -> Open URL

    ReplyDelete
  5. And I thought that I knew Excel back to front. Very nice addition, thanks.

    ReplyDelete
  6. Great information - I have it working well...
    Thanks for the posting!

    ReplyDelete
  7. Losing the download facility from a finance web site, for end of day review, and could not find another site that could supply the data in the order I require. I know I could have got around the issue from another site but think this is a much better option. Many thanks.

    ReplyDelete
  8. Sadly Yahoo no longer works with current price as they have added volume data alongside the price, which Excel now sucks in and which then corrupts any calc base don the price.

    ReplyDelete
    Replies
    1. Sadly, you are correct. However you can use the Excel function =LEFT(...) to strip out the number you want/

      Delete