Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Rom1b
Posts: 36
Joined: Fri Jan 05, 2018 4:34 am
Contact:

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

Post by Rom1b »

siamond wrote: Fri Feb 23, 2018 6:29 pm
LadyGeek wrote: Fri Feb 23, 2018 6:18 pm All credits to Simba (whoever he/she is) for starting this terrific project.
Not to all, you're too humble, you've been doing this for years already; but yet and yes of course 'Kudos' (is that still trendy Im a foreigner ? or was it ever?) - to Simba and all the best to him.

Once again great version, it would be nice to get rid of the old link where possible. I'll get in touch or am there if you need, only slightly busy at the moment (cant swear it will change or aren't we all - even you early retirees).
Dr Bernstein - If You Can / free PDF, google it | 3Fund Portfolio www.bogleheads.org/forum/viewtopic.php?f=10&t=88005 | CollectiveThought www.bogleheads.org/forum/viewtopic.php?f=10&t=7353
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

siamond wrote: Fri Feb 23, 2018 8:19 am I was wondering how many people download this spreadsheet. Sometimes, I have doubts, due to the fairly limited amount of feedback about it (e.g. just a few posters). So I set up a counter associated with the short URL which allows people to download it.

In the past 2 weeks, v17b has been downloaded more than 150 times. Call me impressed. :beer
300+ downloads, and counting. Cool! :happy
jlhod1
Posts: 34
Joined: Wed Aug 05, 2009 9:11 pm

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

Post by jlhod1 »

Awesome work!

Unfortunately, I cannot download the excel spreadsheet because I get a "too many redirects" error when clicking on the 17b link. Any way to link more directly to the document?

Thanks!

John
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

A quick google search suggests the problem is a bug in the Chrome browser. I'm using Firefox and the link worked for me.
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: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Hm, actually, I use Chrome on MacOS and I have no problem. I see more than 400 clicks on the stats, seems to work pretty well overall.

jlhod1, if you continue to have an issue, please contact me via private message, and I'll try to help you.
jlhod1
Posts: 34
Joined: Wed Aug 05, 2009 9:11 pm

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

Post by jlhod1 »

Had same problem when trying again with Safari, but worked fine with Firefox thanks!
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

For reference, here is a new discussion on how to extend the Emerging Markets data series back in time:
viewtopic.php?f=10&t=246941
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

For reference, the discussion about International Small was re-opened in light of a new data source (Independence International Associates, aka IIA):
viewtopic.php?f=10&t=206489&p=3902671#p3902228
Kelgroup
Posts: 1
Joined: Sun May 13, 2018 1:29 pm

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

Post by Kelgroup »

Regarding Backtest-Portfolio-returns-rev17b - I noticed that the annual returns from 1985 are no longer listed year by year on the lazy portfolio tab. Am I missing something - is there an easy way to display it? What I found valuable about this page was that even though annual data - it at least gives me a better idea on things perform year to year.

Thx

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

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

Post by siamond »

Kelgroup wrote: Sun May 13, 2018 1:35 pm Regarding Backtest-Portfolio-returns-rev17b - I noticed that the annual returns from 1985 are no longer listed year by year on the lazy portfolio tab. Am I missing something - is there an easy way to display it? What I found valuable about this page was that even though annual data - it at least gives me a better idea on things perform year to year.
Most of the portfolio-level math has been moved to the Portfolio_Math tab. The 'lazy' portfolios start around column AV. If you scroll down, you can find nominal portfolio returns starting row 97, and real (inflation-adjusted) portfolio returns starting row 255, per year. I would suggest to freeze (or split) the window display around cell D5 for convenience.
gips
Posts: 1752
Joined: Mon May 13, 2013 5:42 pm

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

Post by gips »

thank you for this fine work! I was reading a thread about merriman that included this link to published returns of his portfolio:
https://paulmerriman.com/wp-content/upl ... Update.pdf

when I change the starting year to 1970 in the lazy portfolio tab, his portfolio doesn't perform as well. Could it be different data sources/selection of funds/indexes? The pdf also notes yearly vs. monthly rebalancing, does the backtesting spreadsheet perform rebalancing?

thanks again for this wonderful tool!
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

gips wrote: Fri May 25, 2018 11:39 am thank you for this fine work! I was reading a thread about merriman that included this link to published returns of his portfolio:
https://paulmerriman.com/wp-content/upl ... Update.pdf

when I change the starting year to 1970 in the lazy portfolio tab, his portfolio doesn't perform as well. Could it be different data sources/selection of funds/indexes? The pdf also notes yearly vs. monthly rebalancing, does the backtesting spreadsheet perform rebalancing?

thanks again for this wonderful tool!
Hi there. Thanks for your interest and for the nice words. As to your questions:
- the spreadsheet computations assume annual rebalancing (and I can tell you from another experiment that monthly rebalancing doesn't improve performance)
- in the lazy tab, if you change the start date to 1970, you should see quite a few cells in column D being displayed in red. This is a warning that the spreadsheet doesn't have data coming back that far for a given asset class (e.g. Emerging, Int'l Small, TIPS: all of them apply to the Merriman portfolio). The spreadsheet still does the math, but then returns for the missing years are empty cells, hence zeroes, hence a negatively skewed impact on the stats.
- I do not know how Merriman computed his own stats back to 1970, but this probably involved some approximations. Note that the PDF file you provided only documents the equity part of the portfolio.
- Note that in the next update of the spreadsheet, we'll fill the gaps for EM and Int'l Small, as we recently identified a new data source for those.
gips
Posts: 1752
Joined: Mon May 13, 2013 5:42 pm

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

Post by gips »

thank you for the information and, again, thank you for the great work.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

What a better way to celebrate July 4th than a new version of the Simba spreadsheet? :wink:

Here is the link to download: Backtest-Portfolio-returns-rev17c.xlsx

Rev17c:
1. Updated Expense Ratios (ERs) with latest known values, e.g. latest reductions Vanguard announced in Apr-18 and more
=> Changed ERs: VTSMX 0.14, VIVAX/VIGRX/VIMSX/NAESX/VGTSX 0.17, VFSVX 0.25, VWNDX 0.31, VGENX/VGHCX 0.38, VGPMX 0.36, VWNFX 0.34, VTIBX 0.13, VTWSX 0.19, VDAIX 0.15, EFV 0.39
2. Replaced the Emerging Markets pre-1988 numbers by IFCG EM data series, from Credit Suisse Global Investment Returns Yearbook 2010, starting in 1976
3. Replaced the Int'l Small pre-1995 numbers by IIA Int'l Small data series (eliminating the dependency on DFA data), starting in 1975
4. Improved the dynamic title for portfolio cycles comparison chart, adding the metric's name to the title

Feedback welcome.
Bob
Posts: 213
Joined: Mon Feb 26, 2007 4:15 pm

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

Post by Bob »

Thank you ! Happy 4th of July to U and everyone who supports this great effort. It is appreciated.
mjb49
Posts: 52
Joined: Sat Jan 07, 2017 1:25 pm

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

Post by mjb49 »

Thank you for the update and all your hard work!
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

A new update to the Simba backtesting spreadsheet is now available, click here to download it. Feedback welcome.

Rev17d:
1. Improved rolling returns tables parameterization (e.g. starting year) in Data_TR_USD
2. Consolidated growth charts in Analyze_Portfolio with toggle nominal/real, option to remove the unbalanced portfolio display, and label displaying last value
3. Added new portfolio cycles chart in Analyze_Portfolio, tracking the portfolio end value when making (fixed, inflation-adjusted) annual contributions or withdrawals
4. Settled on the more intuitive 'Maximum Withdrawal Rate' (MWR) and 'Perpetual Withdrawal Rate' (PWR) to replace the SSR/PSR little-known terminology. Linkage to SWR terminology added in explanation of percentile table.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Here are two examples of the new chart. The point is to look at every cycle of N years (in this case, 30) contained in the time period being studied (in this case, 1970-2017) and compare the outcomes for two portfolios (first one is 50% US stocks, 30% Int'l stocks, 20% bonds; second one is 60% US stocks, 40% bonds) at the end of the cycle.

The first chart assumes a portfolio of $1,000,000 at the beginning of each cycle, and an annual withdrawal of $40,000. The second chart assumes a portfolio of $10,000 at the beginning of each cycle, and an annual contribution of $25,000. All numbers are inflation-adjusted. As you can see, the starting year of the cycle REALLY matters...

Note for nerds like me (!!): in order to keep the spreadsheet reasonably efficient, I used a variation of the wonderfully compact formula described here.

Image

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

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

Post by siamond »

As discussed in this thread, the Vanguard Precious Metals and Mining Fund (VGPMX) will radically change its strategy in Sep-18 and no longer properly represent the precious metals and mining (PME) sector. We have such a PME data series in Simba, so we have to decide what to do from 2018 onward.

Starting from this post, various options are explored. Current proposal (with rationale) is detailed here. Feedback on the corresponding thread would be welcome.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Another update to plan for the first 2019 Simba revision. The Global Bonds data series was anchored on the Pimco Global Bond Fund (unhedged) Class D (PGBDX). This was never terribly satisfying, as this is a rather active fund with a very high ER (0.95). Or I should say "it was", as the fund now disappeared, which kind of settles it. Also, there was no currency hedging with this Pimco fund, which might not be what most US investors would prefer, and wasn't consistent with the Int'l bonds data series.

Fortunately, a new Vanguard Total World Bond ETF (BNDW) fund is now available to address the same space (with currency hedging though), with a sweet ER 0.09%, see fund page and press release. And its benchmark is the Bloomberg Barclays Global Aggregate Float Adjusted Composite Index, which is consistent with the type of index we were using for the years before PGBDX's inception. So the logical move would probably be to redefine this data series as:

FTSE WGBI USD (hedged) 1985-1989
Bloomberg Barclays Global Aggregate TR USD (hedged) 1990-2009
Bloomberg Barclays Global Aggregate Float Adjusted TR USD (hedged) 2010-2018
Vanguard Total World Bond ETF (BNDW) 2019+
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

A new update to the Simba backtesting spreadsheet is now available, click here to download it. Feedback welcome.

Rev17e:
1. Added new distribution of returns chart in Analyze_Portfolio
2. Fixed bug in Perpetual Withdrawal Rate cycles formula in Lazy_Portfolios (row 125, percentile cell was improperly referenced).
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Here are two example of the new chart. The point is to analyze the distribution of returns for a given portfolio during a given time interval and compare to a benchmark portfolio.

The first example shows the distribution of one-year nominal returns between 1970 and 2017. You can see that the first portfolio's (in blue) most common returns were in the [15%, 25%[ range (in other words, 5% variation around 20%) and displayed some 'fat tails' (some returns around -30% and +40%). While the second portfolio's most common returns were in the [5%, 15%[ range.

Image

The second example shows more advanced features of the chart. Here I selected real returns instead of nominal returns, and I looked at rolling periods of 10 years contained in the time interval of interest. The horizontal axis shows annualized returns for such rolling periods. Also note that I chose a smaller granularity for the chart's range (horizontal axis).

Image
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

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

Post by AlohaJoe »

siamond wrote: Thu Nov 29, 2018 12:58 pm The second example shows more advanced features of the chart. Here I selected real returns instead of nominal returns, and I looked at rolling periods of 10 years contained in the time interval of interest. The horizontal axis shows annualized returns for such rolling periods. Also note that I chose a smaller granularity for the chart's range (horizontal axis).
Nice work! I'm a big fan of rolling returns charts like this. It's nice to see them made more easily available by being in the Simba spreadsheet.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Here is the first version with the 2018 returns and a few more updates. Most numbers are final, some are still temporary (e.g. inflation) and will be updated in a few weeks, when the official data will become available. Most changes were previously discussed on this thread and/or on related threads.

Here is the short URL for download: http://bit.ly/2BXrZt2. If a couple of folks could run a quick sanity test to verify that it works for them, this would be appreciated. I'll officialize via the wiki page over the week-end.

Rev18a
1. Spreadsheet updated for 2018 returns and latest Expense Ratios (ER)
2. Inflation and some historical 2018 returns (e.g. Shiller) are only an educated guess for now, waiting for official numbers to come later in January
3. Updated the LCV, LCG, MCV, MCB, MCG, SCV, SCB, SCG data series with the latest data from the Stock Index Calculator (Tyler 2018) for pre-index history
4. Updated the LTT, ITT, STT, TBM, STB and TIPS data series with the latest data from the Bond Fund Simulator (Longinvest 1.19) for pre-index history
5. Renamed the VDVIX data series "International Developed" (in short, "Int'l Dev") to better reflect the new orientation of the fund since 2016 (i.e. Canada now included)
6a. Redefined the Global Bond data series: it used to be anchored on Pimco PGBDX (unhedged), it is now anchored on low-cost Vanguard BNDW (hedged)
6b. Global Bond data series now uses FTSE WGBI USD (hedged) index starting in 1985, Bloomberg Barclays Global Aggregate TR USD (hedged) index starting in 1990, then…
6c. … Bloomberg Barclays Global Agg. Float Adjusted TR USD (hedged) index starting in 2010; Vanguard Total World Bond ETF (BNDW) will start in 2019 (fund's inception was in Sep-18)
6d. The old PGBDX (+ corresponding index returns) data series was moved to Data_Misc. Beware: no ER adjustment was applied to the index returns.
7a. Redefined the Precious Metals data series: it used to be anchored on Vanguard VGPMX, it is now anchored on Fidelity Select Gold (FSAGX)
7b. Precious Metals data series now uses INIVX (VanEck International Investors Gold, 1957-1985), then FSAGX (Fidelity Select Gold, 1986+, ER 0.84%)
7c. The VGPMX data series was moved to Data_Misc. Beware: starting from Sep-18, the fund changed its strategy (and its name to Vanguard Global Capital Cycles Fund).
8a. Added new Vanguard Extended Duration Treasury ETF (EDV) data series, aka LT STRIPS (2008+)
8b. LT STRIPS data series extended back in time with Longinvest's Bond Fund EqPar Zero 30-11 model till 1996, and Bloomberg Barclays 20-30Y Treasury Strips TR USD till 2007
9. Updated data sources for Canadian returns in Data_Misc (e.g. reducing dependency on Libra's approximate numbers)
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

This is nothing new, but when I save in LibreOffice Calc, I am warned that the document may contain formatting or content that cannot be saved in Excel 2007-20019 format. In the past I've saved as ODF, which is what is suggested. What do you recommend?

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Kevin M wrote: Sat Jan 05, 2019 11:52 am This is nothing new, but when I save in LibreOffice Calc, I am warned that the document may contain formatting or content that cannot be saved in Excel 2007-20019 format. In the past I've saved as ODF, which is what is suggested. What do you recommend?
Yes, LibreOffice does mess up a bit the original Excel formatting when going from Excel to LibreOffice to Excel. But then why would anybody want to do that? I would advise you to stick to the ODF format if your intent is to save a copy for further editing with LibreOffice. Or am I missing your point?

PS. as a side note, I made a very minor update today, a couple of small things I forgot to do yesterday. The few of you who downloaded a copy of v18a last night or this morning may want to resync. Same link: http://bit.ly/2BXrZt2.
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

siamond wrote: Sat Jan 05, 2019 1:44 pm I would advise you to stick to the ODF format if your intent is to save a copy for further editing with LibreOffice.
That pretty much answers my question, and is what I assumed. I was wondering if there was any advantage to keeping it in the Excel format when using LibreOffice.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

For the next version, besides finalizing a few 2018 numbers (notably inflation), I had a new thought while reacting to this thread. The OPs' question is actually a fairly common one and it would be good to have some numbers readily available to show the consequences of such 'income-based' approach. It may not be the best idea in the world, but since it's a common question, it would be good to give the means to explore it further.

It turns out we already have an S&P500 (price-only) data series in Data_Misc and a historical S&P500 (Shiller Total-Return) in the main data series. Those two series are actually a tad inconsistent because Prof. Shiller does this weird averaging prices & dividends over January. There is also a historical data series for IT Treasuries. I am thinking to clean that up and have:
- a consistent price-only and total-return data series for S&P500 (Data_TR_USD tab, feeding the regular tools)
- a consistent price-only and total-return data series for IT Treasuries (Data_TR_USD tab, feeding the regular tools)
- move the S&P 500 'Shiller' data series to Data_Misc

To do so, I'll use index data series for S&P 500 and IT Treasuries, but we need to go further back in time for the bonds. Which requires an extension of the bond fund model, hence my request here, and longinvest's graceful answer.
jmk
Posts: 642
Joined: Tue Nov 01, 2011 7:48 pm

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by jmk »

Kevin M wrote: Tue Jan 12, 2016 6:26 pm For actual backtesting, Chromebook and other non-Excel (or Excel-clone) users are best served by Portfolio Visualizer. Maybe PV will incorporate the pre-1972 data someday.
You can upload your own data sets into PV going earlier than its defaults. There is some limit on first year though (1900?), but it's earlier than the defaults.
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Kevin M »

jmk wrote: Sat Jan 12, 2019 11:52 am
Kevin M wrote: Tue Jan 12, 2016 6:26 pm For actual backtesting, Chromebook and other non-Excel (or Excel-clone) users are best served by Portfolio Visualizer. Maybe PV will incorporate the pre-1972 data someday.
You can upload your own data sets into PV going earlier than its defaults. There is some limit on first year though (1900?), but it's earlier than the defaults.
What I did when I was using a Chromebook almost exclusively was to open the backtest spreadsheet in a free Excel clone (e.g., LibreOffice Calc) on a Windows PC (which I otherwise used only to do tax returns), delete all the sheets except the one(s) with the data, then import the data into a Google Sheets spreadsheet. I still use a Google Sheets spreadsheet with the data loaded from the backtest spreadsheet from time to time to generate data for posts here.

One could use a similar approach to load the older data from the backtest spreadsheet into PV. I've never loaded any data into PV.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

An interesting question which was asked on the bond fund model thread (click on the tiny arrows to check the context). Here is my initial feedback. More opinions welcome.
siamond wrote: Sun Jan 13, 2019 10:00 am
dcabler wrote: Sun Jan 13, 2019 9:45 amSecond, but related question, is whether Simba should switch over to the pure index funds instead of the managed ones
Yes, this is a legitimate question. I am not too hot about making a spliced series with years of active fund (e.g. VUSTX) followed by more recent years of passive fund (e.g. VLGSX). What we could do is to switch to a series splicing the bond model of relevance, then the index until 2009, then the corresponding passive fund. Such series would undoubtedly be more consistent. We would lose more than 20 years of real-life history though. Plus, I wonder how many Bogleheads actually use those truly passive bond funds as opposed to the more well-known active funds. Feedback welcome, it is clearly a valid question, but I am not too sure such a change would be worth it.
PS. there is another consideration. Vanguard Intermediate Term Treasury funds (passive and active) follow the Barclays US Treasury 3-10 Yr index. Unfortunately, this 10-3 index has limited history, its history only goes back to Jan-2002. If we give up on using VFITX (the active Vanguard fund, 1992+), we would have to rely on the bond fund simulator until 2001. This doesn't seem desirable.

EDIT: PS was wrong, I got mixed up... see next posts for clarification.
Last edited by siamond on Tue Jan 15, 2019 10:40 am, edited 3 times in total.
dcabler
Posts: 4482
Joined: Wed Feb 19, 2014 10:30 am

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

Post by dcabler »

siamond wrote: Sun Jan 13, 2019 11:00 am An interesting question which was asked on the bond fund model thread (click on the tiny arrows to check the context). Here is my thinking. More opinions welcome.
siamond wrote: Sun Jan 13, 2019 10:00 am
dcabler wrote: Sun Jan 13, 2019 9:45 amSecond, but related question, is whether Simba should switch over to the pure index funds instead of the managed ones
Yes, this is a legitimate question. I am not too hot about making a spliced series with years of active fund (e.g. VUSTX) followed by more recent years of passive fund (e.g. VLGSX). What we could do is to switch to a series splicing the bond model of relevance, then the index until 2009, then the corresponding passive fund. Such series would undoubtedly be more consistent. We would lose more than 20 years of real-life history though. Plus, I wonder how many Bogleheads actually use those truly passive bond funds as opposed to the more well-known active funds. Feedback welcome, it is clearly a valid question, but I am not too sure such a change would be worth it.
PS. there is another consideration. Vanguard Intermediate Term Treasury funds (passive and active) follow the Barclays US Treasury 3-10 Yr index. Unfortunately, this 10-3 index has limited history, its history only goes back to Jan-2002. If we give up on using VFITX (the active Vanguard fund, 1992+), we would have to rely on the bond fund simulator until 2001. This doesn't seem desirable.
Here's a place where there are only opinions and no facts. Not like that ever happens on this forum, though, eh? :D

So, if Barclays 3-10 index only goes back to 2002, what did VFITX do before that? If it wasn't the Barclays 3-10 index, then I'd argue that there's already a splice. Yeah, it's a splice with real data instead of derived data, but a splice nonetheless.

Similar thing happens with other Simba Series. For example consider SCV
1927-78: Tyler's Pseudo-CRSP Series
1979-92: Russell 2000 Value TR (USD)
1993-98: MSCI US Small Cap Value GR
1999-Present: VISVX
But VISVX itself is spliced due to changing indices several times.
1999--May 2003 it's S&P Small Cap Value600
May 2003-April 2013 it's MSCI US Small Cap Value Index
April 2013- Present, it's CRSP US Small Cap Value Index

I didn't intend this post to ask questions about SCV until I looked at it, but why isn't everything from 1927 to 1998 using the Pseudo-CRSP series? Why the trip through Russell and MSCI?

Anyway, as somebody who uses VISVX (VSIAX Admiral shares actually), I prefer to look as far back as possible using the closest thing to the actual index currently used by Vanguard for this fund. So I usually manually edit Simba to replace the entire series with VSIAX (post switchover to CRSP), and before switchover, I use the Pseudo-CRSP series, adjusted for E/R, all the way back to 1927 - even if it means losing actual VISVX/VSIAX returns as they actually occurred. If some day Vanguard change their index again, I'll switch over - though I doubt there is any decent way to create a pseudo-index for any other series besides CRSP as Tyler did.

For me, it goes to the question of what I'm looking for. I'm looking for something that most closely matches what I hold today, looking as far back as I can. And of course there's a reality check here: past performance not guaranteeing future performance, the difference between indices might be in the noise over such a long time period anyway. And, no, I'm not trying to get tenth of a degree accuracy in my backtests. I just have my own view of consistency. :D
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

dcabler wrote: Sun Jan 13, 2019 4:16 pmSo, if Barclays 3-10 index only goes back to 2002, what did VFITX do before that? If it wasn't the Barclays 3-10 index, then I'd argue that there's already a splice.
Actually, I got mixed up, apologies. Both VFITX and its Barclays index have been in existence since 1982. Confusingly enough:

1) Vanguard passive ITT fund (VSIGX) tracks the 3-10 Barclays index:
https://investor.vanguard.com/mutual-fu ... ance/vsigx

2) Vanguard active ITT fund (VFITX/VFIUX) tracks the 5-10 Barclays index (like Fidelity FIBAX does):
https://investor.vanguard.com/mutual-fu ... ance/vfitx
dcabler wrote: Sun Jan 13, 2019 4:16 pmI didn't intend this post to ask questions about SCV until I looked at it, but why isn't everything from 1927 to 1998 using the Pseudo-CRSP series? Why the trip through Russell and MSCI?
Well, my order of priority (which I believe is the same as the original Simba designers) is:
1) real-life fund
2) real-life index matching the fund
3) real-life index matching the asset class
4) synthetic model

There are just too many simplifying assumptions about synthetic models, I would be quite reluctant to use them past such order of priority. This being said, I share some of the angst about the Russell index, which seems to be a bit of an antiquated design.

Russell is used because it starts way before the other indices (in 1979). MSCI is used because most of Vanguard's early SCV history is anchored on it, plus MSCI goes further back in time than S&P.

But... one thing at a time, let's sort out the bond funds first...
dcabler wrote: Sun Jan 13, 2019 4:16 pmFor me, it goes to the question of what I'm looking for. I'm looking for something that most closely matches what I hold today, looking as far back as I can.
Yes, I understand the viewpoint. Simba is kind of stuck between two goals: 1) study history for the sake of history 2) study history for the sake of making analogies with what is available today. There is no perfect answer here. I tried to ease the life of advanced users for them to add their own data series at will (and I often do it myself for a point test or another). Maybe there are ways to go further in this direction, but I fear the extra-complexity coming with it...
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

dcabler wrote: Sun Jan 13, 2019 9:45 amSecond, but related question, is whether Simba should switch over to the pure index funds instead of the managed ones
I am starting to ponder if we should not leave the active treasury bond fund data series as is (if only because of the long history of corresponding Vanguard funds), while adding separate passive treasury bond index funds data series (which tend to be anchored on separate indices, confusingly enough).

But let's not jump to conclusions yet, I'd like first to complete the discussion about the proper mapping to synthetic bond fund models that I initiated here.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

siamond wrote: Tue Jan 15, 2019 10:37 am
dcabler wrote: Sun Jan 13, 2019 9:45 amSecond, but related question, is whether Simba should switch over to the pure index funds instead of the managed ones
I am starting to ponder if we should not leave the active treasury bond fund data series as is (if only because of the long history of corresponding Vanguard funds), while adding separate passive treasury bond index funds data series (which tend to be anchored on separate indices, confusingly enough).

But let's not jump to conclusions yet, I'd like first to complete the discussion about the proper mapping to synthetic bond fund models that I initiated here.
Following the discussion about which bond fund models are the best matches for real-life bond funds, it emerged that there is more continuity than one might perceive between the active bond funds from Vanguard and their passive counterparts, even if the benchmark index is different. This post about average maturity illustrates the point.

It is therefore tempting to splice the active fund series with the more recent index fund series, while using the benchmark index of the passive fund for the early years (preceded by the corresponding bond fund model). For example, for the Intermediate-Term fund, it would be something like:
- Matching model: Bond Fund (10 to 4-year)
- Bloomberg Barclays U.S. Treasury 3–10 Year Bond Index
- Vanguard Intermediate-Term Treasury Fund (VFITX)
- Vanguard Intermediate-Term Treasury Index Fund (VSIGX)

Trouble is those index bond funds only exist in Admiral shares flavor while Simba only tracks Investor shares funds. There is actually a higher-level issue, which is that Investor shares are probably on their way out. Furthermore, most investors actually use Admiral shares, so the Simba current tack wasn't optimal (albeit more convenient to get more historical data from real-life funds). After reflecting on all of this, I think it would be wiser to postpone a change on the bond funds, wait for the Investor/Admiral transition dust to settle, and then and only then issue one big Simba revision dedicated to such changes towards Admiral funds (incl. bond index funds).

The next Simba revision will be issued in a week or so (I'm still waiting for some data to come in by the end of January), notably including the final inflation 2018 numbers as well as a new chart about comparative rolling returns. And we'll wait a few more months before tackling the Admiral funds and bond funds changes.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

The long-awaited Feb-19 update... Here it is, please download here.

Rev18b
1. Updated US inflation (Data_TR_USD) with final 2018 number (1.91%); updated Canadian inflation (Data_Misc) with final 2018 number (1.99%)
2. Revisited S&P500(h), IT Bonds(h), T-Bills(h) series:
2a. Removed S&P 500 Price only from Data_Misc, removed S&P 500(h) aka Shiller series from Data_TR_USD, removed IT Bonds(h) from Data_TR_USD
2b. Introduced historical TR (total returns) and PR (price returns) index series for S&P 500 and Total-Bonds (US); T-Bills is TR only (bills' price doesn't change much) - see details in Data_Sources
3. One more pass with latest Expense Ratios (ER): EFV now at 0.38
4. Updated the LCV, LCG, MCV, MCB, MCG, SCV, SCB, SCG data series with the latest data from the Stock Index Calculator (Tyler 2018; FF Dec-18) for pre-index history - a few extremely minor changes
5. Made legends more dynamic for charts with show/no-show settings: display empty string instead of data series' name if no show; also increased size of legend's display
6. Introduced NEW rolling returns chart in Compare_Portfolios
7. Revisited Precious Metals (again!): switched to ASA 1970+ for pre-FSAGX returns (INIVX history as well as pre-70 ASA proved inadequate)
Last edited by siamond on Wed Feb 13, 2019 12:17 pm, edited 1 time in total.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Here is a little more context for the v18b update (cf. post above). Feedback welcome.

About the PR/TR data series for S&P 500 and IT Bonds, you can find more background in this post. Not only does this allow some analysis of price changes, but this also indirectly allows to figure out historical distributions (usually made of dividends) by simply subtracting PR returns from TR returns -- better do it geometrically, i.e. (1+TR%)/(1+PR%)-1. Note that this effort triggered an update of the bond fund simulator to isolate the price-only trajectory (aka capital returns).

About precious metal miners, we had a good discussion on this thread, and settled on the ASA/FSAGX combo. A bit of a dubious compromise due to the use of active funds, but well, it is what it is.

Finally, about the new chart (rolling returns in Compare_Portfolios), this is a handy way to compare two data series. Tyler9000 started to use such charts a long time ago while comparing various indices for the same categories, and I always thought it was handy. Here is an example (note the various toggles allowing to switch from nominal to real returns, set the duration of the rolling period, and decide which data series to show or not show). One notable use of such chart is to set the rolling period to one year, and compare annual returns over a couple of decades.

Image
B4Xt3r
Posts: 744
Joined: Thu Sep 29, 2016 5:56 am

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

Post by B4Xt3r »

siamond wrote: Wed Feb 13, 2019 9:23 am Here is a little more context for the v18b update (cf. post above). Feedback welcome.

About the PR/TR data series for S&P 500 and IT Bonds, you can find more background in this post. Not only does this allow some analysis of price changes, but this also indirectly allows to figure out historical distributions (usually made of dividends) by simply subtracting PR returns from TR returns -- better do it geometrically, i.e. (1+TR%)/(1+PR%)-1. Note that this effort triggered an update of the bond fund simulator to isolate the price-only trajectory (aka capital returns).

About precious metal miners, we had a good discussion on this thread, and settled on the ASA/FSAGX combo. A bit of a dubious compromise due to the use of active funds, but well, it is what it is.

Finally, about the new chart (rolling returns in Compare_Portfolios), this is a handy way to compare two data series. Tyler9000 started to use such charts a long time ago while comparing various indices for the same categories, and I always thought it was handy. Here is an example (note the various toggles allowing to switch from nominal to real returns, set the duration of the rolling period, and decide which data series to show or not show). One notable use of such chart is to set the rolling period to one year, and compare annual returns over a couple of decades.

Image
Thank you for your service. I just updated my monte-carlo simulation with the 2018 numbers.
hungrywave
Posts: 235
Joined: Tue Apr 09, 2019 7:48 pm

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

Post by hungrywave »

siamond wrote: Wed Feb 13, 2019 8:46 am The long-awaited Feb-19 update... Here it is, please download here.
This is amazing! Thank you, siamond!

:sharebeer
The world is largely random so don't sweat the small stuff.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

I have been mulling over a significant change that would apply for the first 2020 update. Several issues have been lingering in the spreadsheet since its inception...

Investor vs. Admiral Shares:
* The spreadsheet currently uses Investor shares funds, but most of us actually use Admiral Shares funds for our own investments.
* Furthermore, Vanguard investor shares funds seem destined to disappear (which also implies that corresponding history might disappear from Morningstar and similar Web sites)

Real-life funds vs. Indices:
* The spreadsheet currently focuses on using real-life funds returns whenever available, while a mix of indices (or synthetic models) are used to fill gaps for older years.
* I found myself in many occasions eager to run a test on a specific index, for consistency reasons and/or to avoid expense ratio adjustments or to compare indices with one another. This wasn't possible so far without significant manual steps.
* Note that multiple Bogleheads wiki pages display index returns from various providers, and it would seem convenient to use Simba to provide some of those values when doing annual updates, or at least when running sanity checks.

Expense Ratio (ER) adjustment:
* The spreadsheet uses the (current) ER of the investor shares funds to adjust the index/synthetic returns (used to fill gaps for older years). Trouble is ERs nowadays are much lower than ERs used by real-life funds when they were created. So we have a weird discontinuity with low ERs (e.g. index years), higher ERs (e.g. fund early years), low ERs (e.g. nowadays).
* I don't believe there is an easy fix for this, it really depends of what the end user wants to do, but the ability to ignore real-life fund returns and solely use index returns for some tests while applying a specific (customizable) ER would partly address the issue (back to the previous point).

No tab solely providing raw returns; practical difficulty for end users to add new data series
* The 'Data_TR_USD' tab currently mixes raw numbers (starting around row 190) and various computations about individual (per fund) data series
* Various advanced users (myself included) typically have other data series that they like to add for their own tests. It is currently possible, but implies some careful (and not terribly intuitive) copy and paste.
* Folks designing their own spreadsheets for other types of simulation typically only care about raw returns, not about extra math baggage.

Those various issues tend to boil down to a single root cause, the use of hard-coded splicing decisions to assemble a given data series from data sources.

I have been mulling over the addition of a new 'Raw Data' tab which would:
- provide full raw data for synthetic models, indices and real-life funds (investor and admiral shares), up to the current year
- use a format that allows easy addition and maintenance of additional raw data by advanced users
- perform the splicing and ER-adjustment math driven by various global settings to provide much better flexibility
- document the data sources being used for the default splicing (i.e. reuse/reformat most of the content we currently have in the 'data sources' tab; the rest will probably be redirected to the README tab)
- be the feed for the return numbers used in the 'Data_TR_USD' tab, which would be renamed 'Fund Math' or something like that, while eliminating its 'raw returns' rows
Last edited by siamond on Thu Aug 15, 2019 10:46 am, edited 1 time in total.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Here is a representative screen shot of the future 'Raw Data' tab. The settings in blue would allow to select (or unselect) various types of data sources (hence tuning the exact splicing being performed) and to drive the exact behavior of the ER adjustment algorithm. Click to see a larger display.

Image

Feedback welcome before I keep going... It will be quite some work to assemble all necessary raw data for the full update!
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

I like the direction. My two inputs would be to maintain the raw date in a separate spreadsheet (so smaller to download and import to Google Sheets, for example), and keep it raw data only, with any splicing in a separate sheet.

It's easy to copy a separate spreadsheet to a sheet in the main spreadsheet, right? So that shouldn't be a problem?

I may not understand enough about the splicing to know if the second request makes sense, but I think that raw data without any complex calculations would be more useful for independent use.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Kevin M wrote: Thu Aug 15, 2019 1:51 pmI may not understand enough about the splicing to know if the second request makes sense, but I think that raw data without any complex calculations would be more useful for independent use.
I pondered about how much we should keep in the 'raw data' tab and I do think the splicing and ER adjustment should stay there. First and foremost, because what should matter as an input to any modeling tool is a consistent data series of historical returns and to get there, *some* form of splicing and ER adjustment is often needed. It may be extremely simple (e.g. ER-adjust a given index, and that's it) or it may be more complex (like the default splicing settings of Simba), but it will usually be needed. And if it is truly not needed, I don't see that it really hurts to have such extra columns or act on the settings to make it a no-op.

Also, the formula in the splicing column is NOT obvious, it took me a while to tune it, so that it is easy/intuitive to copy & paste such a group of columns and possibly adjust it by inserting one more column in the group, etc. I also wanted to avoid any volatile formula (e.g. OFFSET), so that this tab is not recomputed every time, nor its direct dependencies (e.g. the tab doing math on such spliced data series/funds). It turned out to be a tad tricky to get there, and having the splicing column in a separate tab would make it harder to adjust when adding new data series.

I did make sure that the 'raw data' tab doesn't have any external dependency, can be copied to another spreadsheet and would work in Google Sheets or LibreOffice. It will be quite compact in terms of number of rows (definitely much less than the old 'Data_TR_USD'), but it will be pretty sizable in terms of number of columns (e.g. 6 or 7 columns per data series). THAT bugs me a little bit, but I don't know how to make it more compact without giving up on the key objectives.

EDIT: on second thought, maybe we should view it the reverse way around?
- The extensive raw data (including splicing logic) in one tab dubbed 'Raw_Data';
- A compact version of the spliced outcome (one column per data series/fund) in a separate 'Data_Series' tab, in nominal AND real terms, very easy to copy by value;
- Keep the more extensive math (e.g. conditional inflation adjustment, various stats, correlation matrix, rolling returns, etc) in what used to be Data_TR_USD and will be renamed 'Analyze_Series'.
longinvest
Posts: 5672
Joined: Sat Aug 11, 2012 8:44 am

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

Post by longinvest »

Siamond, I can only speak for myself. What's important to me when backtesting is to get as close a return as a real-life investor would have experienced at the time. As such, my preference is, in decreasing order, for:
  1. actual fund returns (without any adaptation to today's expense ratios)
  2. index returns (when actual fund returns are unavailable)
  3. synthetic returns (when the previous two are unavailable)
I've constructed the bond fund simulator (which derives synthetic returns) using a simple investing model that could have been used by an investor in real life at the time.

My rationale is this: past realized returns were conditional to past investing circumstances including transaction costs, tax incentives and disincentives, accessibility of investments to retail investors, regulations, etc. I think that it's futile to try isolate a single cost like the expense ratio and replace it with a modern lower expense ratio without also adapting past returns to modern investing circumstances (which is simply impossible).

But, I'm also OK with the idea that the Simba spreadsheet should offer the choice of getting raw index returns, in addition to getting unaltered real-life historical fund returns.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

longinvest wrote: Sat Aug 17, 2019 9:48 am Siamond, I can only speak for myself. What's important to me when backtesting is to get as close a return as a real-life investor would have experienced at the time. As such, my preference is, in decreasing order, for:
  1. actual fund returns (without any adaptation to today's expense ratios)
  2. index returns (when actual fund returns are unavailable)
  3. synthetic returns (when the previous two are unavailable)
This will definitely remain the default setting. I plan to move to Admiral funds instead of Investor funds by default though (while keeping the option to use Investor funds). This will NOT shorten the number of years where actual fund returns will be used, as the history of admiral funds in the Morningstar database was extended in time to cover all years of existence of the corresponding Investor fund.
longinvest wrote: Sat Aug 17, 2019 9:48 am[...]My rationale is this: past realized returns were conditional to past investing circumstances including transaction costs, tax incentives and disincentives, accessibility of investments to retail investors, regulations, etc. I think that it's futile to try isolate a single cost like the expense ratio and replace it with a modern lower expense ratio without also adapting past returns to modern investing circumstances (which is simply impossible).

But, I'm also OK with the idea that the Simba spreadsheet should offer the choice of getting raw index returns, in addition to getting unaltered real-life historical fund returns.
I do understand this perspective (hence the default setting). I also understand the other perspective that using ERs from the past might not be terribly meaningful, that index funds do a really good job of tracking indices at low cost, and that looking at a single consistent index can be of interest. I am not trying to be argumentative here, just saying that there are multiple possible perspectives.

I was doing the selective splicing logic anyway in a separate spreadsheet of mine, then copying and pasting in 'Data_TR_USD', either the standard version or custom versions using index series. My aim is to allow everybody to do it, with a cleaner packaging. As a side effect, this will make more explicit how this all works, so that whoever might take over the maintenance of the spreadsheet in the future has everything in one place.

For now, this is an experiment and I'll share it as such when I have something that holds together. Then we'll decide where to go with this... Thank you for the feedback, it is helpful to understand various perspectives on this.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Quick update on this... I like where this is going, I'm now quite sure I will officialize, but sheesh, this is a TON of work! I think I got the structure right after several rounds of refinement (and yes, it appears to work ok with LibreOffice), but gathering raw data for each data series requires a lot of careful work and often triggers new questions. Anyhoo, I'm slowly making progress and should have a decent prototype to share in a few days.

One new feature of this approach is that we can easily add extra data series in the 'Raw_Data' tab without using them by default in the actual data series being used for the portfolio computations, while making it real easy to make them available at the portfolio level if so desired. So I decided to open up the following:
- by default, Micro-Cap will be modeled after iShares IWC (passive), while making available the choice of BridgeWay BRSIX (active)
- by default, Int'l Value will be modeled after iShares EFV (passive), while making available the choice of Vanguard VTRIX (active)
- by default, Int'l Small will be modeled after Vanguard VFSVX (passive), while making available the choice of Vanguard VINEX (active)
- by default, LT Treasuries will be modeled after Vanguard VLGSX (passive), while making available the choice of Vanguard VUSTX/VUXUX (active)
- (same idea for IT Treasuries, ST Treasuries and possibly more to come)

Those all map to discussions we had in the past that lead to difficult decisions and open questions. With this new model, we could offer more flexibility...
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Here is an EARLY DRAFT of the updated spreadsheet with the splicing logic, mostly intended for ADVANCED USERS! Please download here. I didn't update instructions, the splicing work is not 100% finished, I didn't run a lot of sanity tests, told you, it's an early draft!

This being said, it should be functional and it would be good to have a few power users take a good look. Here are a few explanations:
  • The Portfolio level (and corresponding tabs) didn't change one bit. Regular users won't see much difference.
  • Data_TR_USD was reorganized, split in three new tabs: Analyze_Series, Data_Series, Raw_Data
  • Analyze_Series looks a lot like the old Data_TR_USD, except that the rows containing raw data disappeared. The same stats are provided, correlation matrix, rolling returns, etc.
  • Data_Series is voluntarily very simple, it's the synthetic outcome of the splicing process, with ER-adjusted nominal and real returns for each data series. This tab will allow folks like Kevin to copy and paste (by value) in some other spreadsheet, without dragging a lot of extra material.
    => First row (in blue) is a selection mechanism, allowing to choose which spliced data series should be used for a given column.
  • Raw_Data is where the new action is! For each data series, there are several columns of raw data (e.g. synthetic model, index, fund returns) and then one splicing column tallying the results and performing an ER adjustment as needs be.
This comes with some cool new features:
  • You can easily select investor funds or admiral funds, or solely focus on indices or even synthetic models (selectively).
  • You can easily add an extra ER adjustment to model a more dire past than actually happened, and/or disable the ER adjustment all together (e.g. when working with indices), or use the Investor/Admiral latest ER on the indices series, etc.
  • You can add your own spliced data series by duplicating a group of columns that seems close enough (templates are provided in the first few columns or you can use an existing group of columns as a starting point).
  • The data series which used to be in Data_Misc are now in this new tab and can be referenced (just go to Data_Series, select an empty column, type the short name of the data series and everything will adjust). Same process for any new spliced data series you might want to add.
  • The treasury bond funds are now based on the corresponding Vanguard index funds, but you can easily switch to a corresponding popular active fund by selecting the proper short name in Data_Series, e.g. ITT (A) instead of ITT. Same for Int'l Value, Int'l Small, Micro Caps, etc.
Note that the Data_Sources and Data_Misc tabs will disappear when I'm done (most corresponding information is now found in Raw_Data -scroll down-, while the higher level information will be moved in the README tab).

My main concern is spreadsheet performance and LibreOffice compatibility. There is a lot of stuff recomputing when cells in Raw_Data are changed. This being said, the typical use of the spreadsheet is to tweak the portfolio level cells and this should not require any more computation than before. Also, I've been extending and tweaking the Raw_Data level for a while now, and I don't have significant performance issues. I ran a quick sanity check with LibreOffice, things seem ok, but I only spent a few minutes doing so...

FEEDBACK WELCOME!!
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

I took a nearly three months break from this project, shame on me... Only excuse, I was busy fishing & traveling... Yup, I caught a few big ones... :wink:

Back to this. I am working on completing the 'splicing' project with the missing data series. Will share when I'm done. I have a couple of points to make though, feedback welcome.

Performance: I started to minimize the use of volatile OFFSET functions, now that I better master the non-volatile INDEX function and its 'address' mode. This seems to work ok with LibreOffice (relief!). I won't do it all, this makes formulas more opaque, but I processed most of the big impact cases.

Investor funds: Vanguard recently scrubbed their 'Personal Investors' Web site from references to investor funds (do a search on VTSMX or NAESX to see it; funds Web pages still exist if you know the URL though). It seems that Morningstar started to do the same, but is less advanced (VDAIX as a case in point). In Morningstar's historical database, all the admiral funds have been extended back in time to include the investor class' history.
=> Bottomline: I see little reason to keep the complicated machinery I drafted to allow to study the history of both investor and admiral funds. I plan to simplify (i.e. Admiral class only).

Misc. Data Series: the new 'Raw Data' tab is getting pretty big with all the splicing (incl. the merging with Data_Misc)... I already simplified by eliminating the ER data series, the SEC_Yield data series and a couple of special cases (e.g. the Kothari TIPS data series which doesn't extend to recent years). I think this kind of material is more for advanced research, I'm always happy to help folks who want to do so, but I'd rather not clutter Simba any more with it.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Finally stabilized something that seems worth sharing. This is still a DRAFT version, only intended for folks willing to give it a test drive. Click here to download: http://bit.ly/2sHkhST

What happened? In short, I finished what was described in the previous post (splicing project, performance, scrubbing investor funds, scrubbing Data_Misc). I also reformatted the Data_Sources tab and did some minor clean-up here and there. And I captured the various changes in the revision history.

I will run a quick update early January to factor in 2019 annual returns and will then share as the official 19a version. Feedback would be very welcome, lots of changes happened since v18b, I probably messed up a few things...

EDIT: I just added the capability to direct select an index (or a synthetic series) by name in Data_Series - making it available for analysis in the rest of the spreadsheet. Something I wanted to do for a long time!
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

I updated the instructions (README tab), describing the new set of worksheets and capabilities. Same link to download.

Reviewers welcome. I think I'm done with the changes until we get the 2019 annual numbers (aside from processing feedback and bugs, of course).

EDIT: shoot, just found and fixed a silly bug in the correlation table. Make sure to download DRAFT 3c or later.
Last edited by siamond on Sat Dec 21, 2019 11:44 pm, edited 1 time in total.
Post Reply