Maybe you already download tables of data from SharePad by clicking on the sharing button in SharePad’s blue Table view, but this data is dead. It doesn’t change in your spreadsheet if it changes in SharePad. Now there is a new option: Export “Live” Table. This allows you to incorporate data into spreadsheets that updates periodically, about every hour in Google Sheets, and whenever you refresh in Microsoft Excel.
Note: On Windows, only Microsoft Excel 2013 or later will support live data export. On Apple devices, neither Microsoft Office nor LibreOffice currently support live data export.
Powering a scoring system with “Live” Tables.
I invest at the speed of a glacier, and although you might expect live data to be of more interest to traders who need to know how their positions are changing on a daily basis, I too use it to help manage my portfolios.
Most of my data isn’t live. It sits in a big spreadsheet, incorporating a separate sheet for every company I follow. The purpose of the spreadsheet is to bring order to the hotchpotch of information I have about companies and to score them based on five criteria: whether each firm is profitable, resilient, adaptable, equitable and inexpensive. My collection of spreadsheets helps me confirm whether the best prospects SharePad gives me really are good companies and whether their valuations are reasonable, using what I know about each business and its strategy.
The scores allow me to rank the companies I follow most closely, determining which may be the best investments, and helping me decide how much of each investment I should hold in my portfolios.
One criterion, though, value, depends partly on the share price and because share prices fluctuate wildly it’s far more convenient to have a live source of data, even for a long-term investor.
Incorporating “Live” Tables into a spreadsheet
This is how I replaced my existing source of live data, which had proved unreliable, with SharePad’s “Live” Tables, and how I referenced data from a “Live” Table in other sheets within a spreadsheet. It’s often better to leave the original table alone, because there are limitations to what you can do with it. Since it is generated on the fly, you can’t edit a “Live” Table for example. The moment you try, the “Live” Table regenerates as though you hadn’t. Spreadsheets can also rapidly become very cumbersome if you limit them to one sheet.
In this example I’ve replaced my own valuation metric with the best known of SharePad’s many measures, the PE ratio. Since I’m exporting the PE ratio I don’t need to export the share price as SharePad has already calculated the ratio, however I’ve included it in my “Live” Table because it’s useful information to have in my spreadsheet.
As you can see, SharePad can display share prices in pence if they are less than 999p and in pounds if they are more than £10.00. This makes them easier to read, but if you are going to do calculations based on the share price, you may prefer to force all shares to use the same units. You must also choose what kind of spreadsheet you want to generate – Google Sheets, or Open Office/Microsoft Excel.
When you click on Export, SharePad downloads a spreadsheet to your hard disk, which you will need to open in your desktop spreadsheet software or upload to Google Drive and open in Google Sheets. It looks like a straightforward table of data, but it’s actually generated from a formula in the first cell in the table, which you can see in the bar above it. If you delete that formula, the whole thing will disappear. If you copy and paste it into a new sheet, the table will appear there.
Now you have the “Live” Table in a spreadsheet, but you can see this data live in SharePad anyway. It only becomes more useful when you integrate it with your own data or calculations.
Below is a second sheet in the spreadsheet for one of the shares I rate, Solid State. Cell B2 displays Solid State’s share price, which it has retrieved from the “Live Table” sheet. In plain English the formula means “get the value from the fourth column (the price column) of the “Live” Table corresponding to the contents of cell B2 (“SOLI”), round it to the nearest whole number, and display it with the suffix “p” for pence”.
Cell A14 displays Solid State’s PE ratio, 18, also grabbed from the “Live” Table. The formula is almost the same, but it looks in the third column of the “Live” Table because that’s the PE ratio column. Here it is:
The magic starts in cell B14. Here a formula takes the PE ratio in cell A14, which comes from SharePad, and converts it into a value between -2 and 2 to fit with my scoring system. The formula is egregiously complicated because in a moment of flashness I decided to use a sigmoid function to do the conversion. In practice this means a company with what many would consider a low PE of 10 or more will score two, a share with a modestly high PE of 20 scores zero. For shares trading on very high PE ratios of more than 20, the formula deducts points up to a maximum of -2. The score is displayed to one significant figure in cell B14.
The valuation score is added to the scores for the other criteria (which are each scored 0, 1 or 2) in cell B15 to give an overall score out of 10.
I do a sheet like this for every company. The empty white boxes under each heading contain notes justifying the scores on the right. And the scores from each individual company sheet are collected, ranked and compared to the value of my portfolios’ holdings. The spreadsheet warns me if the size of any of my holdings are significantly above or below their score. For example if a share scores 7, I shouldn’t really commit more than 7% of the portfolio to it. In a perfect world I would own ten shares with a ranking of ten. In reality, perfect tens are very rare, and currently I own about twenty shares with rankings from nine down to five.
If you’re a wannabe systems designer like me, exporting “Live” Tables is absurdly simple to do. That doesn’t, of course, mean the system you design will be. I’m constantly working to reduce the complexity in mine!
