This blog article is the fourth of a series intended to document the inner workings of the Simba backtesting spreadsheet. The objective is two-fold:
- Provide a better understanding of how the spreadsheet works, to facilitate peer reviews and future maintenance.
- Describe techniques that have been used to compute various investment metrics in a compact manner, which could be useful in other contexts (e.g. your own spreadsheet).
The first article of the series discussed the layered structure of the spreadsheet. The second and third articles discussed risk ratios and more advanced topics. The intent of this fourth article is to address a few miscellaneous topics (e.g. end label on charts, compatibility issues, spreadsheet analytics).
To follow this article in detail, you should open a separate window using version 17a of the Simba spreadsheet, which can be downloaded by clicking on this link. The cell references are of course subject to change in future updates, even if the general structure will probably stay very similar.
Growth/telltale charts and end label
In the Compare_Portfolios worksheet, a growth/telltale chart is provided, with corresponding controls on row 160.
We will not explain the mechanics of a growth chart or a telltale chart here. The former (absolute growth) is well-known and fairly trivial, and the later (relative growth) was described at length by this wiki page. Just note that the Simba spreadsheet unified the two concepts, as a growth chart is identical to a telltale chart relative to cash.
The point we’d like to illustrate is more specific. Note the data label at the end of each line on the chart (e.g. the purple line ends with 1,014,5121)? This data label is the value of fixed, predetermined cell. Now the question is, how to make it work the same while the time period for the chart (starting and ending year) can be changed at will?
The trick is the “IF($C204=$C203, E203, ” part of the formula. Look at cell C204, the formula is “=IF(ROW()-ROW(C$202)>E$87, C203, C203+1)”. Which means that computing the year doesn’t increment itself after the ending year. Which means that the condition “IF($C204=$C203, E203, ” is fulfilled for all rows after the one corresponding at the ending year. Let’s scroll down a good deal now.
You can see that columns C to K all include non-changing values. You can also see the text in cell B357. The cells on this row are the single ones associated with a data label on the chart. The data source for the chart actually spans row 202 to row 357. Spreadsheets don’t have a problem with data points being exactly the same, they just perfectly overlap on the graph. And the last data point comes with the proper label, with its value inherited from the row computing the appropriate value for the ending year of the time period of interest.
Compatibility challenges
The Simba spreadsheet is currently developed using Microsoft Excel for Mac 2011. It is published in XLSX format. Obviously, it works best with a version of Excel that natively supports this format. Still, it is usable on other spreadsheet software, and some efforts are made to enable such compatibility. Unsurprisingly, this lead to a few challenges, which are listed deep down the README tab. Here is a screenshot.
Some of the points about LibreOffice were really not obvious (e.g. row heights, avoid constant value with percentage sign), and chart displays remain imperfect.
The last two points are the most troublesome, and the author has some difficulties finding a proper balance between Excel ease of use and LibreOffice compatibility. Some of the charts do have a somewhat dynamic title (matching the most typical use), while others have a static title which is overly generic… Ideas welcome.
Spreadsheet download – link and analytics
The Simba spreadsheet is currently available for download on Google Drive. Sharing a direct link to a file on Google Drive has two shortcomings:
- the procedure to download requires multiple manual steps, as the typical sharing link provided by Google ends up trying to open the file with Google Sheet.
- there are no readily available statistics, notably the number of downloads.
Inspired by this online article, it turns out that both problems can be addressed. Let’s take the example of the Simba v17a file. Here is the regular sharing link provided by Google Drive: https://drive.google.com/open?id=1JSEY4fczWS2Uq7Xyh9RnC_soTZOFv6NO
Replacing the string “open” by “uc”, and adding the string “&export=download” to the end provides a link instructing Google to directly download the file, hence solving the first issue: https://drive.google.com/uc?id=1JSEY4fczWS2Uq7Xyh9RnC_soTZOFv6NO&export=download
Then the trick is to create a shortened version of this download link, using the author’s Google account and the Google URL Shortener tool. Then the shortened link must be used as the sole way to advertise the downloadable spreadsheet. Here is the outcome: https://goo.gl/g1x4DK. The point being that Google tracks analytics for such shortened link (which can be checked by the author here: https://goo.gl/#). Every time it is clicked, this is a download (solving issue #1) and analytics count the number of such events (solving issue #2). Magic!
Jun-18 update: the Google Shortener tool is unfortunately being deprecated by Google (see this blog post), this approach will have to change by early 2019.
Financial Page 