Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
Tyler9000
Posts: 495
Joined: Fri Aug 21, 2015 11:57 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Tyler9000 » Sun Dec 22, 2019 12:24 am

Thanks, Siamond. You rock!

The data splicing functionality does seem to require a bit of a learning curve, but I like where you're going with that. Am I interpreting it correctly that the blue column selects the series available for use but the priority of those series is fixed? (For example, it will always use Vanguard fund data over any other option). That's not necessarily a bad thing, but I'm just trying to understand the logic.

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sun Dec 22, 2019 12:56 am

Tyler9000 wrote:
Sun Dec 22, 2019 12:24 am
The data splicing functionality does seem to require a bit of a learning curve, but I like where you're going with that. Am I interpreting it correctly that the blue column selects the series available for use but the priority of those series is fixed? (For example, it will always use Vanguard fund data over any other option). That's not necessarily a bad thing, but I'm just trying to understand the logic.
Yes, customizing Raw_Data is definitely for advanced users...

The priority follows the order of the columns (the leftmost column before the splicing column has a higher priority). This is subject to the TRUE/FALSE setting in row 4 though. If a cell in row 4 is set to FALSE, then the corresponding column is ignored. As I tried to explain in the README instructions, you can either play with global settings (e.g. choose to ignore all Russell indices) which act in turn on row 4, or modify 'by hand' a specific cell in row 4.

One cool application of such logic is to entirely ignore real-life funds (i.e. deactivate Vanguard, iShares, etc as data sources) and to optionally deactivate the ER adjustment (there is also a global setting for that). Then you have spliced series solely made of index returns (and synthetic models), which is something I occasionally felt the need to analyze, to avoid side-effects of old/high ERs.

In general, the priority order reflects the history of the indices being tracked by the fund and how this changed over time. But I applied common sense in some cases and tweaked a bit the order... I also often provided more indices than strictly required by the history of the fund.

PS. please download again the spreadsheet, I just fixed a silly bug with the correlation table... Make sure you have Draft 3c or later...

RayKeynes
Posts: 160
Joined: Mon Nov 11, 2019 2:14 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by RayKeynes » Sun Dec 22, 2019 6:07 am

Until when do you have daily SP500TR data available?

Could you insert SMA200 and SMA252 as indexes based on SP500 price index? With the usage of a simple "if" Formula, you can then retrieve Returns For sp500tr sma200

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sun Dec 22, 2019 11:06 am

RayKeynes wrote:
Sun Dec 22, 2019 6:07 am
Until when do you have daily SP500TR data available?

Could you insert SMA200 and SMA252 as indexes based on SP500 price index? With the usage of a simple "if" Formula, you can then retrieve Returns For sp500tr sma200
S&P 500 Total Return on a daily basis starts end of Sep 1989. S&P 500 Price on a daily basis goes way back (1928). So yes, I guess it wouldn't be hard to assemble a moving average model. I took a note and I will consider it.

In the mean time, there is already a "Momentum Factor" data series in the spreadsheet (derived from iShares MTUM and its index MSCI USA Momentum GR USD, starting in 1982). Follow this link for the corresponding methodology. Note that momentum appears to be assessed based on price's trajectory with this index.

edge
Posts: 3464
Joined: Mon Feb 19, 2007 7:44 pm
Location: NY

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by edge » Mon Dec 30, 2019 6:41 pm

Will there be an update incl 2019 data?

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Mon Dec 30, 2019 7:44 pm

edge wrote:
Mon Dec 30, 2019 6:41 pm
Will there be an update incl 2019 data?
Indeed. Once we're done with 2019 though! :wink:

edge
Posts: 3464
Joined: Mon Feb 19, 2007 7:44 pm
Location: NY

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by edge » Tue Dec 31, 2019 10:07 am

Thank you. This is a fun/helpful resource. Much appreciated!!

siamond wrote:
Mon Dec 30, 2019 7:44 pm
edge wrote:
Mon Dec 30, 2019 6:41 pm
Will there be an update incl 2019 data?
Indeed. Once we're done with 2019 though! :wink:

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sun Jan 05, 2020 3:12 pm

Here is the first 2020 update... I will hold for a couple of more days before updating wiki download instructions and so on. If you find errors or anything suspicious, please speak up, either by posting on this thread or via a private message!

You can download v19a here: http://bit.ly/2sHkhST

As usual, the early January update includes a few numbers subject to change because of some official numbers and synthetic models not being available yet (e.g. inflation; Prof. Shiller 2019 numbers; etc).

This update includes a LOT of changes to the structure of raw data, as discussed in the posts from the last few months. I updated the instructions accordingly, while trying to stay fairly concise. See also the detailed revision history. Also note that all data series now track Admiral funds (that is, for Vanguard funds). Feedback welcome.

User avatar
LadyGeek
Site Admin
Posts: 61060
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by LadyGeek » Sun Jan 05, 2020 3:42 pm

With LibreOffice Calc in Linux, the chart titles in Analyze_Portfolio don't match the drop-down list selection of "Real / nominal". The charts do update, but the title doesn't change. For example:

Tab: Analyze_Portfolio
Cell: P134
Select: Real

Chart title: Portfolio Balance (Nominal) 1985 - 2019 <-- should be (Real)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sun Jan 05, 2020 5:38 pm

LadyGeek wrote:
Sun Jan 05, 2020 3:42 pm
With LibreOffice Calc in Linux, the chart titles in Analyze_Portfolio don't match the drop-down list selection of "Real / nominal". The charts do update, but the title doesn't change.
Yes, I am aware. It's been a limitation of LibreOffice for the longest time. Every now and then, I download the latest version, hoping they would address the issue, but no luck so far. I couldn't find a way around. I logged the issue (and a few others) around cell Q300 in the README tab.

wapiti22
Posts: 7
Joined: Sat Apr 08, 2017 3:23 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by wapiti22 » Wed Jan 08, 2020 10:00 am

Thanks for the last spreadsheet. Would it be possible to add back the Global Bonds (Unhedged) data? It is relevant for non-US investor. I even think we could go back to 1970 using Barclays US Treasury index and/or Barclays Aggregate Bond Index Returns as a proxy

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Wed Jan 08, 2020 10:40 am

wapiti22 wrote:
Wed Jan 08, 2020 10:00 am
Thanks for the last spreadsheet. Would it be possible to add back the Global Bonds (Unhedged) data? It is relevant for non-US investor. I even think we could go back to 1970 using Barclays US Treasury index and/or Barclays Aggregate Bond Index Returns as a proxy
Hi there. Thanks for the feedback. Well, the corresponding fund (Pimco PGBDX) disappeared, so I don't have a real-time anchor any more.

Let me suggest something. I could add the FTSE WGBI Unhedged index series (1985+) in the splicing columns associated with BNDW (around column KK in Raw_Data). It wouldn't be used for actual splicing as the FTSE WGBI Hedged series would take precedence. BUT you could then easily reference the FTSE index in Data_Series and the rest of the spreadsheet. Actually, this would enable an easy comparison process between hedged and unhedged, which could be useful.

Let me take a note and mull it over a little more...

Noobiest
Posts: 1
Joined: Wed Jan 08, 2020 10:41 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Noobiest » Wed Jan 08, 2020 10:50 am

My first time on this forum, so I'd like express my thanks for all the hard work that's gone into this and making it available to all of us.

My question: when 'true' is selected to change from nominal to real, why does a portfolio only consisting of hard cash stay constant at $10,000? Should it not fall in value over time due to inflation? This can be seen on the 'Compare_Portfolios' tab.

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Wed Jan 08, 2020 3:36 pm

Noobiest wrote:
Wed Jan 08, 2020 10:50 am
My question: when 'true' is selected to change from nominal to real, why does a portfolio only consisting of hard cash stay constant at $10,000? Should it not fall in value over time due to inflation? This can be seen on the 'Compare_Portfolios' tab.
You have a sharp eye... This is indeed an exception I made to the inflation adjustment. The primary (sole?) application of the 'Hard Cash' data series (just a bunch of zeroes!) is to be used as benchmark in Compare_Portfolios tab, so that the telltale charts become growth charts. I labeled it 'Hard cash - zero return' for this reason. It stays to zero, irrespective of the nominal/real setting.

If one were to keep 'cash' in their portfolio (hence a given % of their asset allocation), they would not keep it as 'Hard Cash' in a checking account with no return whatsoever, they would use a savings account or a money market accounts instead, which is equivalent to T-Bills (and this data series is properly adjusted for inflation!). Yes, a bit of a subtle point, maybe.

EDIT: I wonder if the labeling of the data series should change. Maybe call a cat a cat and label it 'Fixed returns: 0%', instead of speaking of 'hard cash'. I could even make the return % a parameter in 'Data_Series' (or Analyze_Series?), with the series name adjusting itself; it could open a few applications to have such a fixed returns series handy. Thoughts?

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sat Jan 11, 2020 12:31 am

The v19a revision is now official, including 2019 annual returns. Please download the corresponding spreadsheet by clicking here.

Rev19a
1a. Split old Data_TR_USD tab in three new tabs: Raw_Data (splicing layer), Data_Series (selection layer), Analyze_Series (stats layer).
1b. Consolidated data sources information that used to be split between Data_Sources and Data_Misc in Data_Sources; moved per-fund sourcing information to Raw_Data.
1c. Developed an explicit and dynamic splicing mechanism for all series of Raw_Data to collate synthetic, index and fund returns in a given data series, and perform ER adjustment math.
1d. Introduced various settings to control the splicing and ER-adjustment mechanisms in Raw_Data (for advanced users).
1e. Moved all funds series from (old) Data_Misc to (new) Raw_Data following the same principles, allowing them to be potentially selected in Data_Series. ER and SEC Yield series have been removed, being overly out of scope.
1f. New Data_Series tab allows to select and assemble the data series to be used in the rest of the spreadsheet. It is also an easy sheet to copy by value for other spreadsheets, with both nominal and real returns.
1g. New Analyze_Series tab looks very much like the old Data_TR_USD tab, without the raw data rows. Same statistics, correlation and rolling returns tables. Portfolio-level tabs are unchanged.
2. Switched all Vanguard data series to Admiral Funds, as Investor Funds are getting deprecated. Note that Vanguard & Morningstar extended history of Admiral funds with Investor funds returns.
3. Added new ITB data series (Intermediate-Term Govt/Corp Bonds, e.g. Vanguard VBILX). Moved IT Corp after LT STRIPS to make room for new ITB in first block of data series.
4a. Treasury Bonds data series (STT, ITT, LTT) are now based on corresponding Vanguard passive index funds and corresponding indices.
4b. Previously used active Treasury bonds data series can be optionally selected in Data_Series (e.g. replace "ITT" by "ITT(A)" in the appropriate cell in the first row).
4c. Similarly Micro Cap, Int'l Value and Int'l Small data series are mapped by default to a passive fund, but have an active counterpart with an "(A)" extension.
5a. Tuned bond fund simulator mappings to better match avg-maturity and benchmarks: TBM and ITT mapped to 10-4 model; ITT(A), ITB, IT Corp and TIPS mapped to 10-6; STT mapped to 3-2; STT(A) and STB to 5-2
5b. Tuned MCV and MCG series splicing to include corresponding CRSP benchmarks when possible (falling back to MSCI for past years)
5c. Tuned REIT series splicing to include corresponding MSCI benchmark when possible (falling back to FTSE NAREIT for past years); removed 1970-71 overly crude mapping to TSM.
5d. Tuned Extended Market series splicing to include Russell Small Cap Completeness TR USD from 1980 to 1983.
5e. Tuned Total-World series splicing to include MSCI ACWI GR USD from 1988 to 1993.
5f. Tuned Quality Factor series splicing to include MSCI USA Sector Neutral Quality GR USD from 1999 to 2013.
5g. Tuned Value Factor series splicing to include MSCI USA Enhanced Value GR USD from 1998 to 2013.
5h. Tuned LT STRIPS series to start in 1942, as no long-term interest rates were available before to properly feed a synthetic model.
6. Improved general performance of the Portfolio_Math calculations by replacing many uses of OFFSET volatile functions by non-volatile INDEX logic.
7. Updated and expanded instructions in the README tab.
8. Added new series in Raw_Data for broad indices beyond S&P 500 (e.g. DJ Industrial Average, Nasdaq Composite, Nikkei, FE 30 and FTSE 100, DAX)
9. Added 2019 returns: funds and index returns updated; Expense Ratios (ER) updated; inflation is a rough estimate for now; updates for a few synthetic models are pending.

petulant
Posts: 1048
Joined: Thu Sep 22, 2016 1:09 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by petulant » Sat Jan 18, 2020 7:57 pm

siamond wrote:
Sat Jan 11, 2020 12:31 am
The v19a revision is now official, including 2019 annual returns. Please download the corresponding spreadsheet by clicking here.

Rev19a
1a. Split old Data_TR_USD tab in three new tabs: Raw_Data (splicing layer), Data_Series (selection layer), Analyze_Series (stats layer).
1b. Consolidated data sources information that used to be split between Data_Sources and Data_Misc in Data_Sources; moved per-fund sourcing information to Raw_Data.
1c. Developed an explicit and dynamic splicing mechanism for all series of Raw_Data to collate synthetic, index and fund returns in a given data series, and perform ER adjustment math.
1d. Introduced various settings to control the splicing and ER-adjustment mechanisms in Raw_Data (for advanced users).
1e. Moved all funds series from (old) Data_Misc to (new) Raw_Data following the same principles, allowing them to be potentially selected in Data_Series. ER and SEC Yield series have been removed, being overly out of scope.
1f. New Data_Series tab allows to select and assemble the data series to be used in the rest of the spreadsheet. It is also an easy sheet to copy by value for other spreadsheets, with both nominal and real returns.
1g. New Analyze_Series tab looks very much like the old Data_TR_USD tab, without the raw data rows. Same statistics, correlation and rolling returns tables. Portfolio-level tabs are unchanged.
2. Switched all Vanguard data series to Admiral Funds, as Investor Funds are getting deprecated. Note that Vanguard & Morningstar extended history of Admiral funds with Investor funds returns.
3. Added new ITB data series (Intermediate-Term Govt/Corp Bonds, e.g. Vanguard VBILX). Moved IT Corp after LT STRIPS to make room for new ITB in first block of data series.
4a. Treasury Bonds data series (STT, ITT, LTT) are now based on corresponding Vanguard passive index funds and corresponding indices.
4b. Previously used active Treasury bonds data series can be optionally selected in Data_Series (e.g. replace "ITT" by "ITT(A)" in the appropriate cell in the first row).
4c. Similarly Micro Cap, Int'l Value and Int'l Small data series are mapped by default to a passive fund, but have an active counterpart with an "(A)" extension.
5a. Tuned bond fund simulator mappings to better match avg-maturity and benchmarks: TBM and ITT mapped to 10-4 model; ITT(A), ITB, IT Corp and TIPS mapped to 10-6; STT mapped to 3-2; STT(A) and STB to 5-2
5b. Tuned MCV and MCG series splicing to include corresponding CRSP benchmarks when possible (falling back to MSCI for past years)
5c. Tuned REIT series splicing to include corresponding MSCI benchmark when possible (falling back to FTSE NAREIT for past years); removed 1970-71 overly crude mapping to TSM.
5d. Tuned Extended Market series splicing to include Russell Small Cap Completeness TR USD from 1980 to 1983.
5e. Tuned Total-World series splicing to include MSCI ACWI GR USD from 1988 to 1993.
5f. Tuned Quality Factor series splicing to include MSCI USA Sector Neutral Quality GR USD from 1999 to 2013.
5g. Tuned Value Factor series splicing to include MSCI USA Enhanced Value GR USD from 1998 to 2013.
5h. Tuned LT STRIPS series to start in 1942, as no long-term interest rates were available before to properly feed a synthetic model.
6. Improved general performance of the Portfolio_Math calculations by replacing many uses of OFFSET volatile functions by non-volatile INDEX logic.
7. Updated and expanded instructions in the README tab.
8. Added new series in Raw_Data for broad indices beyond S&P 500 (e.g. DJ Industrial Average, Nasdaq Composite, Nikkei, FE 30 and FTSE 100, DAX)
9. Added 2019 returns: funds and index returns updated; Expense Ratios (ER) updated; inflation is a rough estimate for now; updates for a few synthetic models are pending.
Thank you for your excellent work updating this spreadsheet!

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Mon Jan 20, 2020 10:23 am

The official 2019 numbers for inflation in the US (as measured by CPI-U) are now available from the Bureau of Labor and Statistics (BLS). Calculated from December to December as we usually do for consistency with stock annual returns, this amounted to 2.29% in 2019.

I'll hold the next Simba update for a few more days as I am still missing a few other 2019 numbers from various sources, but if you want to update your own version, go to Raw_Data, look for the 'Inflation US' column and plug 2.29 in the 2019 row.

EDIT 22-Jan: as the 2019 inflation in Canada, it is now official and amounted to 2.25%.

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Mon Jan 27, 2020 9:23 pm

Ongoing work for better constructed Canadian data series:
viewtopic.php?f=22&t=301455

The outcome of this work will find its way in the v19b update of the Simba spreadsheet, to be issued early February.

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Mon Feb 03, 2020 5:43 pm

Revision v19b is now official, including fully updated and double-checked 2019 numbers, new data series for Canadian investors and more updates. Please download the corresponding spreadsheet by clicking here.

Rev19b
1. Updated inflation numbers (US and Canada) with official 2019 data.
2. Performed full consistency check on 2019 returns and latest Expense Ratios; various minor updates.
3a. Added new Global Bonds (Active/Unhedged) data series, anchored on PIMCO PAGPX. The series is extended by use of the FTSE WGBI and the Barclays Global Aggregate indices.
3b. Added T. Rowe Price Small Caps (OTCFX) to the list of long-lived funds in Raw_Data; 1957+.
4. Added more history for DJIA, NASDAQ Composite and Nikkei 225 TR series (1963+, 1995+, 1993+) in Raw_Data.
5. Minor adjustments to S&P 500 TR and T-Bills TR series (improved monthly math).
6. Restructured Canadian data series, with full spliced series derived from Vanguard Canada passive funds, plus historical inflation, all expressed in CAD.
=> Total Stocks Canada (VCN); Total Bonds Canada (VAB); Total International Canada (VXC); International EAFE Canada (VIU); Emerging Canada (VEE); S&P 500 Canada (VFV).
7a. Tuned Gold (iShares IAU) series splicing, using LBMA Gold AM USD by default (instead of LBMA PM USD) - iShares Web page performance data reports the former.
7b. Tuned Total-Bonds (TBM) series splicing, using Barclays US Treasury index from 1973 to 1975.
7c. Tuned T-Bills (VUSXX) series splicing, using FTSE Treasury Bill 3 Month USD from 1978 to 1992.

User avatar
siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sat Feb 08, 2020 11:33 am

siamond wrote:
Sun Dec 22, 2019 11:06 am
RayKeynes wrote:
Sun Dec 22, 2019 6:07 am
Until when do you have daily SP500TR data available?

Could you insert SMA200 and SMA252 as indexes based on SP500 price index? With the usage of a simple "if" Formula, you can then retrieve Returns For sp500tr sma200
S&P 500 Total Return on a daily basis starts end of Sep 1989. S&P 500 Price on a daily basis goes way back (1928). So yes, I guess it wouldn't be hard to assemble a moving average model. I took a note and I will consider it.
Sorry, took me forever to get back to this. I thought more about it and discussed it with a couple of folks familiar with the use of such technical indicator. Here is the thinking:
- yes, it wouldn't be terribly hard to compute such SMA indicators, derived from historical daily prices, although this would add to the maintenance burden (which keeps growing!)
- Simba only includes annual series though. So we would only provide an SMA snapshot as of the end of the year.
- And... there is just no practical use for such an annual indicator. The entire point of such indicators is to make a move (e.g. tweaking one's AA in one way or another) soon after noticing something special happening to the SMA indicator, 'soon' as in days or weeks or at worse the end of the month.

In other words, this kind of analysis belongs to much more fine-grain models, but doesn't seem to fit in an annual model like Simba. Sorry!

Post Reply