Simba's backtesting spreadsheet [a Bogleheads community project]
Re: Spreadsheet for backtesting (includes TrevH's data)
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+.
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+.
Re: Spreadsheet for backtesting (includes TrevH's data)
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).
* 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).
Re: Spreadsheet for backtesting (includes TrevH's data)
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.siamond wrote: My vote: scratch this data series, which I seriously doubt anybody is using anyway.
-
- Posts: 5672
- Joined: Sat Aug 11, 2012 8:44 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
Global bonds are different than global ex-US bonds.longinvest wrote: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.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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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...Tyler9000 wrote: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.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.
PS. this being said, for Bogleheads located outside the US, I can see the value of a global bonds (unhedged) data series...
Re: Spreadsheet for backtesting (includes TrevH's data)
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).
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).
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
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.
-
- Posts: 5672
- Joined: Sat Aug 11, 2012 8:44 am
Re: Spreadsheet for backtesting (includes TrevH's data)
Siamond,siamond wrote:International (global ex-US, USD-hedged) bonds would be more useful to have than Global (unhedged) bonds.
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)
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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).
Last edited by siamond on Mon Nov 28, 2016 10:51 pm, edited 2 times in total.
-
- Posts: 5672
- Joined: Sat Aug 11, 2012 8:44 am
Re: Spreadsheet for backtesting (includes TrevH's data)
Siamond,
So, I'll agree that having unhedged Global bonds is good enough.
OK. One could probably recover relatively good unhedged international returns by accounting for the ratio of US/Global and known US returns.siamond wrote: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.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.
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)
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
The outcome matched my intuition (for once!), not much point bothering with another unhedged data series.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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!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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
FRED has Moody's corporate AAA and BAA bonds back to the 1920's or so.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 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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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?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.
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'm looking forward to the 2017 release. It can't arrive soon enough!
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
Neither. Use TR. Every taxpayer's or country's withholding and/or treaty situation is different.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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
P.S. If NR matches current indexes/funds much better than TR, then go for it.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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?
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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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]
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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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!
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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.AlohaJoe wrote:This is certainly one of the downsides of including new things -- we have to decide which flavor to include 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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
Keep up the great work! I think it finally dawned on me that you're probably not simba.siamond wrote:Still a work in progress, mind you! Feedback welcome.
Re: Spreadsheet for backtesting (includes TrevH's data)
No, I am not. 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.MachineGhost wrote:Keep up the great work! I think it finally dawned on me that you're probably not simba.siamond wrote:Still a work in progress, mind you! Feedback welcome.
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
Re: Spreadsheet for backtesting (includes TrevH's data)
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.MachineGhost wrote:FRED has Moody's corporate AAA and BAA bonds back to the 1920's or so.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 also has 20-year Treasury bonds from 1942.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
MachineGhost wrote:Neither. Use TR. Every taxpayer's or country's withholding and/or treaty situation is different.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.
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.MachineGhost wrote:P.S. If NR matches current indexes/funds much better than TR, then go for it.
Re: Spreadsheet for backtesting (includes TrevH's data)
Yes, this is what we ended up settling on. Glad to see that you agree.MachineGhost wrote: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.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]
Re: Spreadsheet for backtesting (includes TrevH's data)
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: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!
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 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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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.siamond wrote: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.MachineGhost wrote:FRED has Moody's corporate AAA and BAA bonds back to the 1920's or so.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 also has 20-year Treasury bonds from 1942.
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?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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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: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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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'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).
And I thought I was nearly finished with my 2017 update...MachineGhost wrote:I'll save us both time and just provide the links for what I use already: [...]
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
All of these look super interesting!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). I'll save us both time and just provide the links for what I use already: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.
Corrected S&P 500 1870-1999:
http://www.jstor.org/stable/10.1086/339903
Just as a quick preview, here's the difference between the above JSTOR article and the simba/Shiller data
It can be fairly substantial. 6-7% a year. I'll have to read the entire paper now
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".
- 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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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:MachineGhost wrote: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.Siamond wrote: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.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.
Corporate bonds yield series (1919+)
Twenty Year United States Government Bonds (1942+)
Excellent input. Thank you!
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
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...
Re: Spreadsheet for backtesting (includes TrevH's data)
Sure I'll do that tonight (well, tonight Vietnam time ).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...
I also couldn't find any references to it, pro or con, which was surprising.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?
Re: Spreadsheet for backtesting (includes TrevH's data)
The thread is located here: viewtopic.php?f=10&t=205081AlohaJoe wrote:Sure I'll do that tonight (well, tonight Vietnam time ).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...
Re: Spreadsheet for backtesting (includes TrevH's data)
I issued my first blog entry on the Bogleheads Financial Page! And what better topic to discuss than the Simba spreadsheet...
https://blbarnitz4.wordpress.com/2016/1 ... readsheet/
https://blbarnitz4.wordpress.com/2016/1 ... readsheet/
Re: Spreadsheet for backtesting (includes TrevH's data)
Fantastic! A link to the blog is on the left-side menu of every wiki page "Blog".
Re: Spreadsheet for backtesting (includes TrevH's data)
Thanks for all of the work!
Re: Spreadsheet for backtesting (includes TrevH's data)
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).
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).
Re: Spreadsheet for backtesting (includes TrevH's data)
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
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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?
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
Re: Spreadsheet for backtesting (includes TrevH's data)
Thanks siamond. Any quick update on the HUGE discrepancies in international stock returns?
Kevin
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
===========
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.
-
- Posts: 108
- Joined: Sat Feb 14, 2009 1:46 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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?)
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.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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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?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.