Reconstructing historical data

[SDN123]

Hi Everyone,

My main portfolio tracking spreadsheet has died and - entirely my fault - my last backup is six weeks old.

I want to reconstruct the missing data. I have a separate (online) record of all sales and purchases (not many!) and all dividends received (more events). I record the overall value of the portfolio AND separately the value of the HYP shares whenever these events occur. So, I'm looking for a way to quickly calculate the value of a portfolio of shares, ETFs, funds and gilts at the close of business on any particular day between December 2016 and now.

Can anyone recommend a tool to do this ? I'd rather not have to lookup and record data at the individual hold level !

All help greatly appreciated,

Thanks in advance,

SDN123

[SDN123]

Hi Everyone,

No-one replied to my email above and so I created my own tool for reconstructing historical data using excel. The method I used is summarised below. Perhaps this information is useful to someone out there. I haven't tried to explain every step, just enough to point you to how to create a tool for your own specific needs. Google is your friend if you want to try playing with this.

First I created a VBA macro in my excel spreadsheet called "StockQuote(strTicker, dtDate)" which was copied straight from this link (http://www.financial-modelling.net/tutorials/excel/open-excel-retrieve-stock-quotes-in-a-formula). This macro effectively creates a new excel formula that uses Yahoo (via your internet connection) to pick up the closing price for the stock "strTicker" on the date "dtDate". For example putting the formula =StockQuote("ADM.L",DATE(2016,12,16)) returns the value 1782. A quick check of google finance (an independant source) shows that on 16/12/16 ADM closed at £17.82 or 1,782p - so the macro works.

Aside: I have never created a macro in excel before this exercise and so it took a bit of googling and experimentation to get it all to work. Overall it wasn't too hard - but I should say that I was a professional computer programmer for a long time (although I probably haven't written any meaningful code for at least 10 years).

Next I created a blank excel worksheet and typed the tickers of all of the stocks in my portfolio into Column A, each ticker in a separate row (eg after leaving cell A1 for a title I set cell A2 = ADM.L, cell A3 = AMFW.L, etc)

Next I typed the number of shares that I own in each stock next to each ticker (eg cell B2 = 10, cell A2 = 12, etc - not my real number of holdings by the way, just an example).

Next I put each of the dates for which I wanted a portfolio total across the top row of the spreadsheet (eg cell C1 = 16/12/16, cell D1 = 30/12/16, etc). In my case the dates were the dates of "events" that had happened to my portfolio - mainly when I had received a dividend.

In effect at this stage I had created a grid of ticker names (vertical labels) and dates (horizontal labels).

Next I typed the following formula in the top left cell of the grid that I had created (eg in cell C2): =StockQuote($A2,C$1)*$B2/100
This formula represents StockQuote ("ADM.L", Date(2016,12,16)) * 10 / 100 or put another way the closing value of "ADM.L" on 16/12/16 [in pence] multiplied by the number of shares I held divided by 100 [to convert to pounds]. NB: the dollar signs in the formula are to fix the row name or column name when I copy the formula in the next step.

Next I copied the formula in cell C2 (when I have got it working) to all of the other cells in the grid.

Next, at the bottom of the table, under each column, I entered a formula to add up all of the values for the date at the top of the column. This gave me the total for the portfolio for that date. For my 30 share (30 row) portfolio the formula under the first column (entered into cell C32) was =Round(Sum(C2:C31),2) The round() function simply rounds to 2 (in this case) decimal places.

As an initial sense check at the end of each row of values I calculated the average and standard deviation of values for the share. I then calculated in another cell the number of values for that share that were plus or minus 5 standard deviations from the average. My experience is that sometimes yahoo gets the decimal place wrong. Any errors like that would be would have been flagged up by this check. The check would also have flagged any missing values. As it happens no errors were found. I'll leave this as an exercise for the reader if you want to try it.

As a final sense check I calculated the portfolio value for some dates where I already knew the total (values for dates that I was able to recover from my backup). The previous total and the newly calculated totals were not identical, but the variance was small enough that it could easy be explained by rounding errors and/or by the difference between inter- and intra- day values.

Overall I was happy with my new spreadsheet (maybe it took me two hours to get it all working?) It's a good temporary solution but it does make for a very slow spreadsheet as every value is downloaded individually from Yahoo. I'd try to find a more efficient method else if I wanted a more permanent solution.

Well done if you read this far !!

SDN123

[recklesssaving]

Hi SDN123,

I'm managing tracking of my share portifolio current and historic pricing with Excel using the SMF addin (stock market functions) that grabs any of the information that yahoo finance presents. It's essentially an expanded set of functions to the one you've created yourself that a community of excel users have built up over the last 10 years.

Using this addin and mastering entering your formula's into excel as array formula's instead of normal one will remove your current issue of slow multiple calls to yahoo as this addin combined with array formula's will do the same thing in a single call to yahoo - alot faster

Get the excel addin, templates, install guide, links for user groups from ogres-crypt.com/SMF/ (newbie to forum can't post link) - recommend downloading the templates SMF-Template-RCHGetYahooQuotes.xls and RCHGetYahooHistory-Example-By-Parameters.xls to get you started.

reckless-saving

[SDN123]

Hi reckless,

Thanks for that information / link, that sounds like it would have been perfect for me. As you probably know it will be easier to get my head around the "advanced" functions because I ground it out the hard way the first time.

Anyway, I'll have a play during the week and if appropriate feedback on this thread.

Thanks again,

SDN123