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
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

I am mostly done scrubbing the historical returns of the spreadsheet. I went through each data series, made sure I could rebuild each of them with corresponding data sources, extended 1972 series to 1970+ (or earliest date possible), updated the numbers as needs be, and made various changes along the way, as documented by previous posts and the README notes. Here is my TEMPORARY working spreadsheet:
https://drive.google.com/open?id=0B0svR ... i01b3FxNTQ

Two primary pending issues about the raw data:
- decide to stay with Prof. Kothari's TIPS numbers, or switch to a Bogleheads model (under development, discussed here)
- sort out the pre-1988 Emerging Markets numbers (currently crudely approximated to EAFE/Developed for the sake of plugging something in there) - stay tuned.

Primary next step: some structural changes to cleanly switch all 1972+ math to 1970+.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Actually, there is one data series where reconstruction is trickier than the other ones. Global bonds. Currently defined as:
* JP Morgan Global Gov Bond 1987-1996 (plus a very coarse guess for 1985-1986)
--- also known as JP Morgan GBI Global (unhedged).
* Pimco Global Bond PIGLX Instl 1997+

I can't find the historical returns for JP Morgan GBI online (if somebody knows how to do it, do tell). I finally figured out that the numbers came from a book from David Darst. Which I got my hands on, and confirmed the numbers, but this is NOT an easy to repeat process, and maybe not something entirely kosher.

Furthermore, the recent years are based on PIGLX, an active AND institutional fund with a high ER (cardinal sins!). Finally, as far as I know, there is no Vanguard or iShares fund doing anything similar, so we're not even speaking of something investible for a regular passive investor. Finally, this is the only Pimco fund left in the spreadsheet and I wouldn't mind sticking to fund providers more inclined to do passive management...

My vote: scratch this data series, which I seriously doubt anybody is using anyway.

PS. if somebody is nevertheless curious about Global Bonds, I'll be happy to discuss them in a separate thread, based on my own private research, in a more synthetic manner (like I did for International Bonds here).
User avatar
Tyler9000
Posts: 739
Joined: Fri Aug 21, 2015 11:57 am

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

Post by Tyler9000 »

siamond wrote: My vote: scratch this data series, which I seriously doubt anybody is using anyway.
Removing global bonds because the data can't be verified is one thing, but I would not assume nobody uses them. I get lots of questions about them and they are called for in a few popular portfolios. Personally, I think there's a lot of value in keeping them around. Provided the data is sound, if course.
longinvest
Posts: 5672
Joined: Sat Aug 11, 2012 8:44 am

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

Post by longinvest »

Tyler9000 wrote: Removing global bonds because the data can't be verified is one thing, but I would not assume nobody uses them. I get lots of questions about them and they are called for in a few popular portfolios. Personally, I think there's a lot of value in keeping them around. Provided the data is sound, if course.
I agree with Tyler, here. While I have no personal interest in unhedged international bonds, I wouldn't remove the biggest asset class (with no use of derivatives) from the spreadsheet other than for not having reliable data about it.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
stlutz
Posts: 5585
Joined: Fri Jan 02, 2009 12:08 am

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

Post by stlutz »

Finally, as far as I know, there is no Vanguard or iShares fund doing anything similar, so we're not even speaking of something investible for a regular passive investor.
There is IGOV (iShares International Treasury Bond - unhedged) which has been around since '09. Not a too much of a history but it does exist and is investible.

I think anybody who has used the spreadsheet has not been able to find a scenario where unhedged international bonds are helpful (using the data series provided). If the data at least seems logical, I'd also vote to keep it as it provides good backup for a claim that is frequently made--that there is no advantage to including international *unhedged* bonds.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Having thought more about the topic, and received good feedback, some of my issues can be addressed:
1. PIGLX being active is less significant than I thought as most Vanguard bond funds in the spreadsheet are active anyway - which I had not realized (I took the opportunity to add an Active/Passive tag in the data sources tab, so that we see this aspect more clearly).
2. We could replace PIGLX (Institutional) by the equivalent in share class D, that is PIMCO Global Bond (Unhedged) D (PGBDX) => same history (inception in Nov-93), the ER become 0.95, which isn't exactly cheap, but then we are much more consistent with the investor class we typically use in Simba. I would have preferred Class A or B, but they don't appear to exist (see PIMCO's definitions here).
3. Let's also be clear that Global bonds is not the same thing as International bonds (i.e. US bonds are included in Global)

Then remains the issue with the JP Morgan index. I may have an idea for this part, stay tuned...

(EDITED) PS for Slutz: iShares IGOV tracks ex-US Government bonds, i.e. International Bonds. Not the same scope as PIGLX, although it appears to be unhedged too.
Last edited by siamond on Sun Nov 27, 2016 10:49 pm, edited 2 times in total.
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

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

Post by AlohaJoe »

longinvest wrote:
Tyler9000 wrote: Removing global bonds because the data can't be verified is one thing, but I would not assume nobody uses them. I get lots of questions about them and they are called for in a few popular portfolios. Personally, I think there's a lot of value in keeping them around. Provided the data is sound, if course.
I agree with Tyler, here. While I have no personal interest in unhedged international bonds, I wouldn't remove the biggest asset class (with no use of derivatives) from the spreadsheet other than for not having reliable data about it.
Global bonds are different than global ex-US bonds.

What popular portfolios call for global bonds? I've only ever seen portfolios that pair global ex-US bonds (sometimes called international) with US bonds.
User avatar
Tyler9000
Posts: 739
Joined: Fri Aug 21, 2015 11:57 am

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

Post by Tyler9000 »

AlohaJoe wrote: Global bonds are different than global ex-US bonds.

What popular portfolios call for global bonds? I've only ever seen portfolios that pair global ex-US bonds (sometimes called international) with US bonds.
That's a fair point. Keeping the global bond data would be nice, but adding international (ex-US) bond data would be even more helpful. It's true that the portfolios I have seen call for international bonds, not global. Thanks for pointing that out.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Tyler9000 wrote:
AlohaJoe wrote: Global bonds are different than global ex-US bonds.

What popular portfolios call for global bonds? I've only ever seen portfolios that pair global ex-US bonds (sometimes called international) with US bonds.
That's a fair point. Keeping the global bond data would be nice, but adding international (ex-US) bond data would be even more helpful. It's true that the portfolios I have seen call for international bonds, not global. Thanks for pointing that out.
Yes, that is my perception as well. International (global ex-US, USD-hedged) bonds would be more useful to have than Global (unhedged) bonds. And then we have an easy real-life fund to match, i.e. VTIBX (Vanguard Total International Bond Index Fund). I actually dabbled with it in this thread. Challenge is to find proper historical data. Let me continue to explore both tracks...

PS. this being said, for Bogleheads located outside the US, I can see the value of a global bonds (unhedged) data series...
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

I think I finally found a good way to address both Global bonds (unhedged) and International bonds (ex-US, hedged), starting from the fact that both Pimco (PGDBX) and Vanguard (VTIBX) use a version of the Barclays Global Aggregate index as their benchmark. Then I used Citigroup WGBI for the earlier years, as it goes back to 1985 (plus it matches Barclays reasonably well in the years of overlap). VTIBX has very little history, but since Vanguard is pretty good at tracking indexes, I think we have a meaningful data series.

Global Bonds (Unhedged) (A)
===========================
Citigroup WGBI USD (unhedged) 1985-1989
Bloomberg Barclays Global Aggregate TR USD (unhedged) 1990-1993
Global Bond Fund (unhedged) Class D (PGBDX) 1994+

International Bonds (USD-hedged) (P)
================================
Citigroup WGBI NonUSD Hedged USD 1985-1989
Bloomberg Barclays Global Aggregate Ex USD TR Hdg USD 1990-2013
Vanguard Total International Bond Index Fund (VTIBX) 2014+

(as a side note, I finally figured out where to get the JP Morgan data for Global Bonds (Morningstar symbol FOUSA06EE1), and some of the numbers we had in previous versions of the spreadsheet were not quite correct; anyhoo, Citigroup WGBI has more history for both global and int'l bonds, so no regrets).
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

And... here is the latest and greatest working version of the Simba spreadsheet.

Besides addressing Global and International bonds, I also finalized the move to 1970+ computations for all corresponding charts and statistics (in lieu of 1972+). In addition to the usual 1985+ and 1871+ charts and stats. Since this is a big move, I changed the name of my working spreadsheet to 'DRAFT2', it can be found here:
https://drive.google.com/open?id=0B0svR ... HJlWURjVTQ

Still a work in progress, mind you! Feedback welcome.
longinvest
Posts: 5672
Joined: Sat Aug 11, 2012 8:44 am

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

Post by longinvest »

siamond wrote:International (global ex-US, USD-hedged) bonds would be more useful to have than Global (unhedged) bonds.
Siamond,

Adding international (global ex-US, unhedged) bonds could useful, too. It would allow for studying the impact of currency fluctuations (or hedging, depending on one's view) on 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: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond »

longinvest wrote:I hear you though, you're speaking of a direct analysis between hedged and unhedged comparing apple to apple, and we can discuss it in a separate thread if you're truly interested (I now have all the numbers handy).
Yes, I pondered about that too, but believe it or not (!), I am trying to keep things reasonably simple... People interested in unhedged series can take a good look at Global Bonds (which is largely why I kept it!). I think this is good enough for the scope of the spreadsheet. Also there is no Vanguard fund for Int'l Unhedged.
Last edited by siamond on Mon Nov 28, 2016 10:51 pm, edited 2 times in total.
longinvest
Posts: 5672
Joined: Sat Aug 11, 2012 8:44 am

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

Post by longinvest »

Siamond,
siamond wrote:
longinvest wrote:Adding international (global ex-US, unhedged) bonds could useful, too. It would allow for studying the impact of currency fluctuations (or hedging, depending on one's view) on returns.
Yes, I pondered about that too, but believe it or not (!), I am trying to keep things reasonably simple... People interested in unhedged series can take a good look at Global Bonds (which is largely why I kept it!). I think this is good enough for the scope of the spreadsheet. Also there is no Vanguard fund for Int'l Unhedged.
OK. One could probably recover relatively good unhedged international returns by accounting for the ratio of US/Global and known US returns.

So, I'll agree that having unhedged Global bonds is good enough.
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: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond »

Being my curious self, I ran some quick tests using annualized returns with hedged and unhedged Int'l bonds data series, see posts in this separate thread.

The outcome matched my intuition (for once!), not much point bothering with another unhedged data series.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

We had a discussion about corporate bonds a little while ago. So far, we have short-term and high-yield corporates in the spreadsheet, and no other type of corporates. It would seem that an intermediate-term series, or maybe an aggregate ('total') series would be in order, if we could find proper data sources.

I kept this point in mind, and finally figured out possible data series to use for such purpose, one centered on Vanguard VCIT, the other centered on iShares LQD. Please see the following thread for a full discussion (and feedback welcome).
viewtopic.php?f=10&t=204362

EDIT: after a good discussion, the VCIT data series was selected (mostly for consistency with other data series), and added to the working version of the Simba spreadsheet.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

siamond wrote:I am well aware... But in most cases, we have at least 20 years of 'real' data (if not from an actual fund, at least from a well-known index provider). For momentum, if you can find such historical data (without a copyright constraint), by all means, please share, but 5 years of real-life data seems definitely too short.
You can create momentum indexes by decile from the same FF data used to create the size and style. The recent momentum funds are a joke -- way too many stocks in the portfolio and the resulting meager momentum effect is muted if not outright demolished by the management fees (here's look at you AQR!). But fortunately, that keeps it working for the rest of us!
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

siamond wrote:[Not sure what to do with corporates, as I don't believe there is a (public) source for corresponding historical interest rates. Note that the (copyright-protected) SBBI data about LT corporate bonds doesn't display any striking difference with LT treasuries, as far as I recall.
FRED has Moody's corporate AAA and BAA bonds back to the 1920's or so.

FRED also has 20-year Treasury bonds from 1942.
Last edited by MachineGhost on Wed Dec 07, 2016 10:38 am, edited 1 time in total.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

Tyler9000 wrote:
siamond wrote:Tyler poked a bit at my logic here, and after doing some research, well, he was quite right.

I just push back occasionally because I trust your judgment and appreciate your tenacity for seeking out the truth. :D

Nice work tracking down the fund history. Not just for NAESX, but also for VTRIX. I'm always reassured to have you and others constantly looking to improve our shared Simba resource.
I wonder what the lit the fire under his posterior because simba's been terribly neglected for years in terms of its data quality. Why now? :happy

I'm looking forward to the 2017 release. It can't arrive soon enough!
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

siamond wrote:I think the case is clear, and we should switch to the NR/Net data series. Which is going to lower the historical returns a tad, sorry. Feedback welcome.
Neither. Use TR. Every taxpayer's or country's withholding and/or treaty situation is different.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

siamond wrote:If we were to do that, I would also provide the entire VTRIX history (starting from 1984) in the new Data_Misc tab, for those interested. So if somebody is truly attached to the old way of combining numbers, a simple copy & paste will do the trick. Feedback welcome.
We should always prefer indexes and index ETF's over active management indexes and funds in the past. It's more in keeping with the times. It also keeps the expenses consistent instead of swinging wildy.

P.S. If NR matches current indexes/funds much better than TR, then go for it.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

stlutz wrote:Just to make sure I was clear above--I'm not arguing that anything that could not have been invested in index form should be excluded from the spreadsheet. If one couldn't buy any kind of mutual fund--active or passive--that targeted the asset class, then I do raise a serious question.

The oldest "emerging markets" mutual fund launched in 1989. So, how exactly how could any of us invested in the asset class before it even existed?
You openend a brokerage account in a foreign country and buy the stocks. It wasn't that difficult compared to these "enlightened" days of FATCA causing ordinary Americans to be rejected. Me thinks you're conflating availability of indexes/funds with nonavailability of investments that make them up.

Now, sure, you won't be able to buy a several hundred foreign stock diversified portfolio for little expense. But that's why returns are lower for those stocks now.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

siamond wrote: We could also replace PCRIX by iShares GSG, which uses S&P GSCI as its benchmark, but GSG is fairly new (2006) and a bit of a strange ETF (check the factsheet). Feedback welcome.
3111&p=3115217#p3113198]this thread[/url].[/i]
None of the commodity indexes are ideal because of the dumb ways they weight commodity futures which almost always overweights oil. That being said, I would go with GSCI TR and GSG because PCRIX is hardly a fund anyone with any sense would buy as its for stupid-rich institutions with 1 million minimum.

Now, technically, managed futures (trend following commodities) would be superior but I'd be surprised if there's any data available going back to the early 1970's.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

siamond wrote:I confess that I am a big skeptic about momentum. It seems to me that this is one of those things which may disappear once identified and over-hyped. Maybe I am biased by my intuition here. Additional maintenance cost is minimal, don't worry about that. I don't mind adding those in the next official release if there is more than one individual asking! :wink:
Momemtum's been going on for centuries, so, yes, you're operating on flawed biases -- however, the net returns have been decreasing since 2000 but that is more due to three yield-seeking bubbles in a row than anything else (i.e. running out of suckers). The real problem here is AQR's momentum indexes and retail funds are junx and don't represent what real-world momentum investing is actually like.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

AlohaJoe wrote:This is certainly one of the downsides of including new things -- we have to decide which flavor to include :happy My vote would be to pick MTUM over AQR, mostly because the existing choices already have what I'll call a "non-purist Vanguard-slant". By that I mean: the spreadsheet doesn't include the "best" (or more pure) small or value factor funds; it includes Vanguard's offerings. I think MTUM's low-ER makes it more Vanguard-ish than AQR's offerings and more in keeping with the other entries in the spreadsheet. I think it more likely that Vanguard brings to market an MTUM competitor than an AQR competitor; if that ever happens I imagine it likely that the spreadsheet would start tracking the Vanguard fund.
MTUM is a substantial improvement, but it's still too large of a portfolio to maximize the momentum effect. However, the low expense ratio is almost an outlier for momentum funds, so it is a reasonable compromise for vanilla.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

siamond wrote:Still a work in progress, mind you! Feedback welcome.
Keep up the great work! I think it finally dawned on me that you're probably not simba. :D
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

MachineGhost wrote:
siamond wrote:Still a work in progress, mind you! Feedback welcome.
Keep up the great work! I think it finally dawned on me that you're probably not simba. :D
No, I am not. :wink: I never had the opportunity to communicate with him (or her), actually. The spreadsheet was maintained by a string of volunteers since its creation in 2007, and I am the latest one. I just happen to have more time and interest for research available than most, hence the renewed effort to update the spreadsheet in multiple respects.

Many thanks for all your comments. I will address them one per one, but first things first, I really appreciate to see somebody new on this thread who is willing to communicate. It's been a bit difficult to get feedback on this big 'scrubbing' project I've been going through in the past couple of months, so seeing a new contributor with a wide range of thoughts is definitely appreciated.

The link to my working spreadsheet is below. There will be some changes until it is officialized (2016 returns for one!), but most of my historical research is completed:
https://drive.google.com/open?id=0B0svR ... HJlWURjVTQ
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

MachineGhost wrote:
siamond wrote:[Not sure what to do with corporates, as I don't believe there is a (public) source for corresponding historical interest rates. Note that the (copyright-protected) SBBI data about LT corporate bonds doesn't display any striking difference with LT treasuries, as far as I recall.
FRED has Moody's corporate AAA and BAA bonds back to the 1920's or so.

FRED also has 20-year Treasury bonds from 1942.
Hm, I don't think so. Please check by yourself and give me an explicit pointer if you find otherwise (would be great!), but I can't find anything like that. Lots of data about yields and spreads, but not so much about historical returns.

For corporates, here is the discussion and what we recently settled on: viewtopic.php?f=10&t=204362

For long-term treasuries (and much more), here is the model that we're using to backtrack earlier in history than what indices track: viewtopic.php?f=10&t=179425. I also very recently updated my working spreadsheet to extend the period of time covered by the indices for STT and LTT.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

MachineGhost wrote:
siamond wrote:I think the case is clear, and we should switch to the NR/Net data series. Which is going to lower the historical returns a tad, sorry. Feedback welcome.
Neither. Use TR. Every taxpayer's or country's withholding and/or treaty situation is different.
MachineGhost wrote:P.S. If NR matches current indexes/funds much better than TR, then go for it.
TR returns are not available for international series. More precisely, they are, but they are named 'GR' or 'NR', which are forms of total-returns. And yes, Vanguard (international) funds use NR indices as their index/benchmark. See this Morningstar document for a great explanation. So I updated all (index) historical returns accordingly.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

MachineGhost wrote:
siamond wrote: We could also replace PCRIX by iShares GSG, which uses S&P GSCI as its benchmark, but GSG is fairly new (2006) and a bit of a strange ETF (check the factsheet). Feedback welcome.
3111&p=3115217#p3113198]this thread[/url].[/i]
None of the commodity indexes are ideal because of the dumb ways they weight commodity futures which almost always overweights oil. That being said, I would go with GSCI TR and GSG because PCRIX is hardly a fund anyone with any sense would buy as its for stupid-rich institutions with 1 million minimum.
Yes, this is what we ended up settling on. Glad to see that you agree.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

MachineGhost wrote:You can create momentum indexes by decile from the same FF data used to create the size and style. The recent momentum funds are a joke -- way too many stocks in the portfolio and the resulting meager momentum effect is muted if not outright demolished by the management fees (here's look at you AQR!). But fortunately, that keeps it working for the rest of us!
In general, we try to keep it real in the spreadsheet, at least as much as we can (which is admittedly a rather relative statement for some data series). This means to use real-life funds for as many years as possible, and if we only have a few years, then try to find a passive fund, so that the numbers from the corresponding index are meaningful too. The work from Prof. French is fascinating, but very academic in nature, so this doesn't quite pass the test. His size/value series are notably quite remote from anything investible. Anyway, we're not allowed to reproduce the numbers from the Fama-French library as is (I checked with Prof. French last year).
MachineGhost wrote:MTUM is a substantial improvement, but it's still too large of a portfolio to maximize the momentum effect. However, the low expense ratio is almost an outlier for momentum funds, so it is a reasonable compromise for vanilla.
Yes, I do understand that there are many options here, and the Simba spreadsheet is indeed centered on low cost 'vanilla' options. So it seems to me that MTUM (which is now part of the working spreadsheet) does the job. With a little bit of work, you'll find it very easy to add your own data series in your private copy of the spreadsheet, if interested. It is kind of hard to assemble data series with a significant history though. If you find more, by all means, please share.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

siamond wrote:
MachineGhost wrote:
siamond wrote:[Not sure what to do with corporates, as I don't believe there is a (public) source for corresponding historical interest rates. Note that the (copyright-protected) SBBI data about LT corporate bonds doesn't display any striking difference with LT treasuries, as far as I recall.
FRED has Moody's corporate AAA and BAA bonds back to the 1920's or so.

FRED also has 20-year Treasury bonds from 1942.
Hm, I don't think so. Please check by yourself and give me an explicit pointer if you find otherwise (would be great!), but I can't find anything like that. Lots of data about yields and spreads, but not so much about historical returns.
Not sure what you "don't "hink so". Moody's isn't going to mesh with VCIT anyway, so its moot. The 20-year Treasury is spread over two separate data sets.
For long-term treasuries (and much more), here is the model that we're using to backtrack earlier in history than what indices track: viewtopic.php?f=10&t=179425. I also very recently updated my working spreadsheet to extend the period of time covered by the indices for STT and LTT.
That is very long thread I don't have time to catch up on and seems extraordinary complex for just generating a simple total return time series. What is the drama?
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

siamond wrote:With a little bit of work, you'll find it very easy to add your own data series in your private copy of the spreadsheet, if interested. It is kind of hard to assemble data series with a significant history though. If you find more, by all means, please share.
I've been doing that for years, in fact. However, I'd say Tyler now has the bee knees for size and style data (and a few others if he'd ever finish those, hint hint). I'll save us both time and just provide the links for what I use already:

Corrected S&P 500 1870-1999:
http://www.jstor.org/stable/10.1086/339903

EAFE and Emerging:
https://www.msci.com/end-of-day-data-search

Corporate Bonds:
http://research.stlouisfed.org/fred2/ca ... =os&od=asc

Municipal Bonds:
http://www.jvlassociates.com/files/Avg% ... 202014.pdf

Real Estate:
http://www.econ.yale.edu/~shiller/data/Fig3-1.xls

Timber:
http://www.ncreif.org/timberland-returns.aspx

You can look for more at Quandl.com

Another good addition would be the put write S&P 500 indexes which are available at the above or CBOE. Looks like there's a couple of funds offering that already.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

MachineGhost wrote:I've been doing that for years, in fact. However, I'd say Tyler now has the bee knees for size and style data (and a few others if he'd ever finish those, hint hint).
Ahah, a 'Simba power-user'. Very cool. And yes, Tyler did some amazing work, and I'd rather focus on historical research than developing more computational tools in the spreadsheet, as he is miles ahead in this respect.
MachineGhost wrote:I'll save us both time and just provide the links for what I use already: [...]
And I thought I was nearly finished with my 2017 update... :wink:

Ok, thank you, I wasn't aware of most of those resources. We never cease to learn... Let me try to slowly unravel them in the coming few weeks. Thanks a lot for sharing. I'll probably send you a few PMs for clarifications.
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

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

Post by AlohaJoe »

MachineGhost wrote:
siamond wrote:With a little bit of work, you'll find it very easy to add your own data series in your private copy of the spreadsheet, if interested. It is kind of hard to assemble data series with a significant history though. If you find more, by all means, please share.
I've been doing that for years, in fact. However, I'd say Tyler now has the bee knees for size and style data (and a few others if he'd ever finish those, hint hint). I'll save us both time and just provide the links for what I use already:

Corrected S&P 500 1870-1999:
http://www.jstor.org/stable/10.1086/339903
All of these look super interesting!

Just as a quick preview, here's the difference between the above JSTOR article and the simba/Shiller data

Image

It can be fairly substantial. 6-7% a year. I'll have to read the entire paper now :happy

Having read the paper, their main points are:
  • The data between 1926-1954 could be improved.
    • Historically there were two S&P indices. One had daily quotes but only about 90 companies. Another had weekly quotes but with ~400 companies.
    • Shiller uses the smaller index; the broader index is better.
  • Cowles' data for 1871-1940 is flawed because they are "averaged".
Cowles did two things they take issue with
  • From 1918-1940 he takes the 4 weekly returns of each month and computes the arithmetic average of them. That's used as the monthly return.
  • From 1870-1918 he takes the highest price of the month, the lowest price of the month, and then averages them together.
This averaging is "bad". They use some mathematical technique to try to un-average things. They do that by looking at the Dow Jones Industrial data for the same time period.

For instance (numbers are made up, not real at all).

For June 1902 Cowles found that the lowest price was $10 and the highest price was $20, so he quoted $15 as the "end of June 1902" price. The authors look at the DJIA. They find that the lowest price was $8, the highest price was $16 and the actual monthly close was $9. The averaged-close price would be $12 but the real-close price is $9. So the difference is -33%. They would take that -33% and apply it back to the Cowles data. Every month will have its own modifier based on looking at the DJIA for that month.

How much of a difference does it make? Here's a chart showing cumulative returns expressed as a ratio. When it is above 100% then the paper's methodology has higher returns. When below, Cowles/Shiller/simba has higher returns. To the naked eye, it looks like Wilson & Jones's index results in consistently better returns for stocks. Often up to 10% better.

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

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

Post by siamond »

MachineGhost wrote:
Siamond wrote:
MachineGhost wrote:FRED has Moody's corporate AAA and BAA bonds back to the 1920's or so. FRED also has 20-year Treasury bonds from 1942.
Hm, I don't think so. Please check by yourself and give me an explicit pointer if you find otherwise (would be great!), but I can't find anything like that. Lots of data about yields and spreads, but not so much about historical returns.
Not sure what you "don't think so". Moody's isn't going to mesh with VCIT anyway, so its moot. The 20-year Treasury is spread over two separate data sets.
Ah ok, I now understand why we were speaking past each other. I was looking for historical returns, and you were probably thinking historical rates or yields. And I was looking at the regular part of the FRED Web site, and you probably had in mind the academic part (e.g. NBER) of the Web site... Ok, for reference, here are the pointers I think you were suggesting:

Corporate bonds yield series (1919+)

Twenty Year United States Government Bonds (1942+)

Excellent input. Thank you!
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

AlohaJoe wrote:How much of a difference does it make? Here's a chart showing cumulative returns expressed as a ratio. When it is above 100% then the paper's methodology has higher returns. When below, Cowles/Shiller/simba has higher returns. To the naked eye, it looks like Wilson & Jones's index results in consistently better returns for stocks. Often up to 10% better.
Thank you for the detailed analysis. I also read the article (although less in details than you did), and thought that they were definitely onto something. Another thing which struck me is that, akin to Shiller, the authors provided a full data series, including dividends, earnings, price and P/E math. I am very curious to run some expected return models on this data now! The authors call it the S&P composite price (SPWC) index.

Now what puzzles me is that I did some quick Internet search, and could hardly find any citation or reference about this work, aside from the most striking one, the Triumph of the Optimists book having used some of the data for early days US returns. One would think this research should trigger more academic analysis and peer papers? I also didn't find any discussion about this work on our forum aside from a couple of passing references. I am willing to add a second (in addition to Shiller) S&P 500 historical series in Simba with the SPWC numbers if we can secure the authorization from the authors to do so, but first things first, I think we should open a dedicated thread about it...

=> AlohaJoe, why don't you start it, while repeating your analysis? I don't want to steal your thunder... :wink:
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

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

Post by AlohaJoe »

siamond wrote:I think we should open a dedicated thread about it...

=> AlohaJoe, why don't you start it, while repeating your analysis? I don't want to steal your thunder... :wink:
Sure I'll do that tonight (well, tonight Vietnam time 8-) ).
Now what puzzles me is that I did some quick Internet search, and could hardly find any citation or reference about this work [...] One would think this research should trigger more academic analysis and peer papers?
I also couldn't find any references to it, pro or con, which was surprising.
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

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

Post by AlohaJoe »

AlohaJoe wrote:
siamond wrote:I think we should open a dedicated thread about it...

=> AlohaJoe, why don't you start it, while repeating your analysis? I don't want to steal your thunder... :wink:
Sure I'll do that tonight (well, tonight Vietnam time 8-) ).
The thread is located here: viewtopic.php?f=10&t=205081
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

I issued my first blog entry on the Bogleheads Financial Page! And what better topic to discuss than the Simba spreadsheet... :wink:

https://blbarnitz4.wordpress.com/2016/1 ... readsheet/
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Fantastic! A link to the blog is on the left-side menu of every wiki page "Blog".
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
JamesSFO
Posts: 3404
Joined: Thu Apr 26, 2012 10:16 pm

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

Post by JamesSFO »

Thanks for all of the work!
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

About TIPS, as a reminder, we attempted to develop a TIPS fund model, while (in parallel) also succeeding to get the exact numbers from the Kothari article. This was investigated in this thread.

Based on the discussion which unfolded starting here, we're really losing trust in any synthetic model for TIPS, hence any annual return before 1996. For now, I did one preliminary change in the working spreadsheet:
- I kept the data series with the Kothari returns (1970+), but this is TEMPORARY.
- I moved the TIPS data series to the 1985 group, as minimally we'll eliminate the 1970-1984 numbers
- I moved the new Intermediate Term Corporate Bonds to the 1970 group

We may end up having to further restrict the TIPS data series, if we can't find some kind of coarse proxy for 1985-1996. Two possible candidates (IT Treasuries and ST Treasuries) are very much out of sync with TIPS in the known years (1997+), and do not seem a good approach (see comparison here).
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 »

Portfolio Visualizer seems to have made a big change recently in some of its data. Examples that I've just noticed.

Data previously was available back to 1972 for international stocks, but now is available only back to 1982. I think this has something to do with switching over to a monthly data source. More disconcerting, some of the returns for international stocks have changed dramatically, and are no longer aligned with the backtesting spreadsheet (at least as of my last download). Example: our spreadsheet shows a 57.03% return for 1985, but PV now shows a return of only 15.32% for that year. This is too big to ignore.

So the question is, which data source is more reliable?

Siamond, this seems to be in your wheelhouse in terms of sleuthing to verify or improve accuracy of data sources. Observations?

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: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond »

Kevin M wrote:Portfolio Visualizer seems to have made a big change recently in some of its data. [...]
Siamond, this seems to be in your wheelhouse in terms of sleuthing to verify or improve accuracy of data sources. Observations?
Yes, you're right, Portfolio Visualizer is in flux nowadays, and some of the data series being used are no longer aligned with Simba (e.g. some data series coming from AQR -notably Int'l-; no use of Tyler's FF/CRSP or longinvest's bond fund model -if only by lack of a monthly model-, etc). I have yet to take a solid dive in the AQR data series, to be honest.

I am now in regular communication with the author of Portfolio Visualizer, and well aware of what he's doing and why, and he's well aware of what I am doing and why. His work is very much like my ongoing 'scrubbing' of the Simba spreadsheet, a work in progress. I am happy to openly discuss discrepancies on this thread or a dedicated thread. Here is my latest working spreadsheet, by the way.

You can find the data sources currently used by Portfolio Visualizer here:
https://www.portfoliovisualizer.com/faq
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 »

Thanks siamond. Any quick update on the HUGE discrepancies in international stock returns?

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

FYI The author of Portfolio Visualizer is forum member pvguy.

===========

Is there any reason the Sortino ratio is not mentioned in this forum as much as the Sharpe ratio? (Or did I miss something?)

================

Administrative: The rolling returns charts (Portfolio tab) have all the plots enabled when first viewing the spreadsheet in LibreOffice Calc (Linux OS). This is a minor compatibility issue not worth fixing, but should be mentioned. Clicking on the chart's checkboxes work as expected.

Selecting different rolling returns is taking a bit of horsepower to update the charts.
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.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

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

Post by MachineGhost »

Sortino has a required rate of return barrier which can vary person to person. Sharpe is pretty much fixed. Neither one is very good at what they try to do but that's all that is in simba.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

LadyGeek wrote:Is there any reason the Sortino ratio is not mentioned in this forum as much as the Sharpe ratio? (Or did I miss something?)
MachineGhost wrote:Sortino has a required rate of return barrier which can vary person to person. Sharpe is pretty much fixed. Neither one is very good at what they try to do but that's all that is in simba.
Yep, agreed. I recently improved the error detection on those ratios, and I also fixed a consistency issue between the downside deviation and the upside deviation (both now use the same logic as Sortino, a comparison against a Minimum Acceptable Return). I'm happy to add a few more stats if you have suggestions. Right now, I'm trying to focus on finalizing the changes to the data sources, but I can take notes for future improvements.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

LadyGeek wrote:Administrative: The rolling returns charts (Portfolio tab) have all the plots enabled when first viewing the spreadsheet in LibreOffice Calc (Linux OS). This is a minor compatibility issue not worth fixing, but should be mentioned. Clicking on the chart's checkboxes work as expected.

Selecting different rolling returns is taking a bit of horsepower to update the charts.
I am not sure why you mention a compatibility issue, as I *had* all plots enabled in my Excel version. I just changed that to only have the 5-years and 10-years rolling returns enabled (hence 3-years and 15-years disabled), as I agree that this is not worth the horse-power to have it all displayed by default. Same link to download. Does that help?
Post Reply