# Simba backtesting spreadsheet: miscellaneous topics

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 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.