Friday, 28 April 2017

Scraping A Living

Introducing-Web-Scraping
Source

If you are a DIY Income Investor - or any kind of DIY investor for that matter - you need to keep track of your portfolio. You could pay for expensive software...or you could build your own EXCEL spreadsheet.

You need to update it, of course - and this is where you need to learn how to 'scrape' share prices (and other data) from websites. Free of charge. However, this does have its problems.

I have written a post about the EXCEL basics on how to 'scrape' a share price: it's fairly easy, once you know how. Be aware that if you have a diversified portfolio you might not find all your prices on a single website - I need to go to some specialist sites for some of my bonds and PIBS.


However, the problem is that it is increasingly difficult to find suitable websites (in the UK at least). Unfortunately, financial websites are not designed with 'scrapers' in mind; providing share price data is normally expensive and the website usually would like to sell this service.

So every so often, the website changes something in its website design that frustrates my EXCEL commands. YAHOO Finance did this to me in 2013 - I had to find an alternative. (I subsequently found a work-around and went back to YAHOO Finance).

Well, it happened again to me a couple of weeks ago and my spreadsheet 'went dark'. It turned out that my spreadsheet was unable (again) to update most of the prices of my DIY Income Investor portfolio from YAHOO Finance.

So it was 'back to the drawing board': this time I used ADVFN, which is a powerful site.Here is an example of the website page (for the Exchange Traded Fund SHYU) that is 'scrapable' (I don't think you even need to be registered). I won't bore you with the details of how to do it but there is a little trick to it: if the page freezes when you are editing the link, you need to click on Options, and then Cancel.

So my spreadsheet is now back and running, although not 100% of what is was (as I can't get a summary of my daily gain/loss yet). I have found that editing and improving the spreadsheet is a fairly constant activity, so if I find a better solution - I'll let you know.



I am not a financial adviser 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.

5 comments:

  1. Hi. Thanks for the link to ADVFN - I'll give it a try. I only update my spreadsheets on the last working day of the month, so it was only earlier today I found that Yahoo had changed all their stock price links again...

    ReplyDelete
  2. Thanks for sharing! I looked into this and interesting to see, but I tend to do it manually now - as DM says I only do it once a month at most so it doesn't take too long, but I would love to automate it... on principle!
    Cheers,
    FiL

    ReplyDelete
  3. If you're using a Mac and have the free Numbers spreadsheet programme, it now has a stocks and shares function built in. I think it gets data from Yahoo, but it does allow you to put a ticker in your formula and will update automatically every few minutes. You can also get changes in % or value terms and a range of other factors.

    ReplyDelete
  4. Hi - have you seen this? I use bits of it for my spreadseet and it still works fine for closing prices.

    ReplyDelete
  5. http://investexcel.net/live-stock-quotes-in-excel/
    (forgot to paste!!)

    ReplyDelete