Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Clive
Posts: 1950
Joined: Sat Jun 13, 2009 5:49 am

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by Clive »

This spreadsheet http://www.gold.org/download/value/stat ... prices.xls available from this web page http://www.gold.org/investment/statisti ... ice_chart/ contains Annual End of Period gold prices for a range of currencies since the end of 1978 and might be a reasonable reference to include in the Gold section of the "Data Sources" worksheet.

i.e. Perhaps update gold to be based on the existing Kitco data for 1972 to 1978 and the above Gold Council data thereafter?
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Spreadsheet Design and Testing

Post by LadyGeek »

I'm looking at compatibility with LibreOffice Calc 4.04 (latest stable version). There's a problem with the Portfolio charts.

The original LibreOffice spreadsheet format (Spreadsheet for backtesting (includes TrevH's data)) is also missing these charts, so maybe they've never worked correctly. BTW, if I save the Excel file as .xlsx format (Office 2010), I get part of the Names in the chart data fields. Otherwise (.xls), none of the defined names appear. This is a compatibility problem.

Saving the MS Excel file as .ods (LibreOffice) is unusable.

It may boil down to maintaining MS Excel and LibreOffice as separate versions, especially if gummy's Magic Sum function is added (VBA is not very compatible).

======================================
As for documentation, the instructions in README are off by one row.

In "Things you can change: (Cells that can be changed have pale blue background)", references to rows 57/58 should refer to rows 56/57.
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
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Spreadsheet Design and Testing

Post by LadyGeek »

(I retitled the Subject line in this post so members not interested in this discussion will know to skip over it.)

Simbda did a very nice job to automatically scale the charts when the portfolio dates change. He used some slick Excel tricks which utilize named ranges (put the range inside a Name). Those interested can trace the formulas through the offset() functions in the defined Names.

The problem is that LibreOffice does not support named ranges in charts. A bug fix / feature request is filed with the LibreOffice developers, but it might take a while (Bug 66250). Until this is fixed, the charts are broken.

You can always manually scale the charts, but it's not very convenient. I'll continue to look at alternative approaches.

The latest version of OpenOffice does support named ranges in charts, so it gave it a try. Better, but it breaks on the rolling returns charts (additional functions inside the defined names). In Returns_72_12, it put "#N/A" in the blank cells - they should be blank. This is a show-stopper, as it's OK in LibraOffice.

For reasons other than this spreadsheet (and that OpenOffice broke the formula), I'm going to stick with LibraOffice as my open-source Office software.
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
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Spreadsheet Design and Testing

Post by LadyGeek »

I found a clean solution that fixes the broken PORTFOLIO charts in LibreOffice, there's no need to keep separate versions for Excel and LibreOffice. :happy

Charts in other tabs appear correct (unless I missed something). The fix does not modify any data or associated formulas. The gory details are in the README tab.

Please download and test: JonoJono1 Portfolio Backtester - LibreOffice compatible.xls - This file works on both MS Excel and LibreOffice Calc.

The spreadsheet was updated in MS Excel 2010 and tested in LibreOffice Calc 4.1 on Windows 7. You can get LibreOffice here: LibreOffice

One notable difference is that the charts will scale differently on the X-axis, it's a side effect of the change in chart type and autoscaling. I don't think anything is wrong, just different. You can now easily see that the rolling returns charts don't include the last time period for the averaging window.

==============================================
JonoJono1 - This updated is intended to be incorporated into your master copy. I think it's important that this works on free and open source software (supported on Windows, Mac, Linux). Let me know if you need any help. You may want to use this as the master and roll your changes into this copy.
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
BlueEars
Posts: 3968
Joined: Fri Mar 09, 2007 11:15 pm
Location: West Coast

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by BlueEars »

Hi LadyGeek, you might want to mention how you'd like feedback. I just downloaded it and peaked at it in 2007 Excel and it looks good. But did not really run some of the options that are there. Just saying that many may do as I did and if you don't hear from them you will think there is a lot of indifference to this effort.
Thanks!
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

I appreciate your comments, as lack of feedback does give a feeling of indifference. I didn't really think about how to supply feedback.

Just posting what you've found (nothing broke), along with what you're using (Excel 2007) is a good start.
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.
JonoJono1
Posts: 37
Joined: Sun Jul 07, 2013 10:31 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by JonoJono1 »

Blueears:

My sentiments exactly! I want to construct the dataseries from 1926 up unil the present. But this way beyond my expertise. I used Shiller's data for monthly S&P and have found some good gold data series too.

I have even contacted the big data houses and gotten some one-off access to all the data that is available. (The price is $20,000 plus for a subscription, so I have to be judicious in what we access, meaning, I want to know precisely what we need.) My problem is that I do not know how to judge the reliability of a data series I put together, meaning will it provide to us what we think it is providing? Will it be a fair representation of each asset class so we can acquire data strings that fairly represent each asset class?

Does anybody out there know how to assemble these data strings back to 1926?
User avatar
BlueEars
Posts: 3968
Joined: Fri Mar 09, 2007 11:15 pm
Location: West Coast

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by BlueEars »

JonoJono1, you may know how to do this already but I do have a spreadsheet with the FF data + the use of it to construct a data series from smb/hml as variables. It checks out with the SP500 as a test case for recent decades. PM me with an email address if you want it. I guess you don't have a PM right now.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

If you want historical returns of the S&P 500, there's an excellent site for this information. See: Damodaran Online, from the NY Univ. Stern School of Business. The Data Page is quite comprehensive.

(Annual Returns on Stock, T.Bonds and T.Bills: 1928 - Current is used in the wiki for the Risk and return: an introduction article.)
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
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

On a technical note, the spreadsheet works in Linux using LibreOffice Calc 3.6.7.2 (prior release). This is good.

While JonoJono1 is buried working on the spreadsheet (give him some time to organize things), I was wondering if the wiki could be updated. It's a blank page right now: Simba's backtesting spreadsheets

The spreadsheet is a tool to help you understand your investments. Backtesting means that you are using past performance to predict future results, so I want to be sure the expectations are set appropriately.

I'd like to provide a framework so that investors understand the limitations of what this spreadsheet is intended for, and how to apply it properly. How is this spreadsheet used? What is the significance of the drawdown charts? Rolling returns?

If there are any helpful posts in Simba's thread, Spreadsheet for backtesting (includes TrevH's data), point me to them. Otherwise, I'll take suggestions here. Detailed examples would be helpful.

Wiki editors may update the article directly.
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
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by stratton »

I'd love to see quarterly performance.

The only reason I mention this is some funds on Yahoo finance have data going back that far. Some that are that old don't have the data. Yahoo gets it from Morningstar.

As an example this page for Wellington that goes back to 1929 has quarterly and annual returns.

http://finance.yahoo.com/q/pm?s=VWELX+Performance

Paul
...and then Buffy staked Edward. The end.
M1garand30064
Posts: 93
Joined: Tue Sep 04, 2012 8:49 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by M1garand30064 »

This may be overly ambitious, but I'd like to see a 401k simulator be added. Ideally I'd like to be able to pick an asset allocation, enter a starting salary and expected raises and see what your portfolio would be worth after x number of years of contributions.

Something replicating this would be great! http://www.bloomberg.com/personal-finan ... tors/401k/

Is this doable?
Call_Me_Op
Posts: 9872
Joined: Mon Sep 07, 2009 2:57 pm
Location: Milky Way

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by Call_Me_Op »

I think going back to 1926 is a great idea - even if the data is restricted to a relatively small number of asset classes.
Best regards, -Op | | "In the middle of difficulty lies opportunity." Einstein
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by siamond »

Call_Me_Op wrote:I think going back to 1926 is a great idea - even if the data is restricted to a relatively small number of asset classes.
+1
User avatar
JamesSFO
Posts: 3404
Joined: Thu Apr 26, 2012 10:16 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by JamesSFO »

Neat tool. Adding this to track the thread.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

M1garand30064 wrote:This may be overly ambitious, but I'd like to see a 401k simulator be added. Ideally I'd like to be able to pick an asset allocation, enter a starting salary and expected raises and see what your portfolio would be worth after x number of years of contributions.

Something replicating this would be great! http://www.bloomberg.com/personal-finan ... tors/401k/

Is this doable?
Interesting concept, but it's an entirely different approach. Doing a straight-forward future value (FV) calculation belies the underlying complexity of what you're really asking. The results would not be realistic. For example, expected inflation should be incorporated. You need to change your tactics and use statistics to predict future performance.

What I'm leading up to is that you need to implement Monte Carlo analysis or any number of different math techniques (it gets complicated). This brings you into the realm of retirement planning calculators. They're in the wiki: Retirement calculators and spending Calculators which utilize historical U.S. stock and bond returns are designated as "Hret" types.

Based on complexity and the difficulty of verifying the results, I'd say your suggestion is not practical to implement. (Nice idea, though.)
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.
JonoJono1
Posts: 37
Joined: Sun Jul 07, 2013 10:31 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by JonoJono1 »

Great suggestions all around. I read this post every night now for new ideas. I am hoping we get a good group of ideas to pull from.

On my personal copy of the backtester, I have expanded the number of portfolio comparions to 24, instead of 5. This was tedious but the results are helpful because it allows the users to compare essentially the whole field of different ways to divvy up a portfolio. I have made about 1,000 (not an exaggeration) iterations and have found the outputs informative, to understate it.

What I notice as a glaring shortage of information in our backtester is a timeseries longer than 41 years. So it seems to me that going back to 1926 is probably the biggest shortcoming in the backtester as it sits now.

Again, I am not trying to exclude anyone's suggestion, I am just trying to listen to all of them and make some sort of input so we can collectively determine what direction we should take the backtester and the best sequence. I have seen a +1 for 1926, blueears was helping some offline, and my personal observation is that this is needed most.

Any strong disagreements or agreements?

Can anyone be of valuable assistance getting the dataseries for as many asset classes as we can get our hands on going back to 1926?

Please announce yourself! :)

I can go through the tedium of inserting this data into the backtester and making the excel spreadsheet flawless, once I have a good set of dataseries. But I am not able, for the time being at least, to be confident that I have the right dataseries. Fyi, we will need dataseries that incorporate total return.

Anyone?

Ladygeek: I have Shiller's data that he makes public, which includes the S&P and dividend data. I feel confortable this is a fine series. Do you agree?
Last edited by JonoJono1 on Fri Aug 02, 2013 1:57 am, edited 2 times in total.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by siamond »

Jonojono, totally agreed with your assessment. I used the backtesting data for various simulations (notably on SWR methods), and this time span of ~ 40 years isn't that different from a (long & healthy) retirement period. So it makes it quite hard to actually make a good series of simulation cycles, something akin to Firecalc.

Trouble is it seems rather difficult to gather such pre-1972 data. Yes, the Shiller information is reliable, but so few asset classes in there... I had the idea of asking the Firecalc author to share his underlying data, as Firecalc allows to parameterize a rather diversified portfolio, and backtest 100+ cycles, but... it appears that the individual is enjoying life on a boat and is pretty much unreachable. Good for him (or her), but... Darn it! :?

Bottomline: totally agree with your intent, don't know how to proceed to actually do it...
JonoJono1
Posts: 37
Joined: Sun Jul 07, 2013 10:31 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by JonoJono1 »

Siamond:

That was a helpful post. Thanks.

If we can get the dataseries, this backtester will be flush with a powerful set of information to help all of us.
Call_Me_Op
Posts: 9872
Joined: Mon Sep 07, 2009 2:57 pm
Location: Milky Way

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by Call_Me_Op »

siamond wrote: Trouble is it seems rather difficult to gather such pre-1972 data. Yes, the Shiller information is reliable, but so few asset classes in there... I had the idea of asking the Firecalc author to share his underlying data, as Firecalc allows to parameterize a rather diversified portfolio, and backtest 100+ cycles, but... it appears that the individual is enjoying life on a boat and is pretty much unreachable. Good for him (or her), but... Darn it! :?

Bottomline: totally agree with your intent, don't know how to proceed to actually do it...
Just limit asset classes that can be back-tested before 1972. Simba's spreadsheet does something similar, in that there are a lot more asset classes starting in 1985. After all, it's the stock-bond-cash mix that drives most of your portfolio return over long time periods.
Best regards, -Op | | "In the middle of difficulty lies opportunity." Einstein
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by siamond »

Call_Me_Op wrote:Just limit asset classes that can be back-tested before 1972. Simba's spreadsheet does something similar, in that there are a lot more asset classes starting in 1985. After all, it's the stock-bond-cash mix that drives most of your portfolio return over long time periods.
For sure, this is the way to go by default. Then we can always simulate a more diversified portfolio by using some kind of approximate rule (e.g. default total US market returns to S&P -known- returns, etc). That being said, a diversified portfolio has really nice properties, and the more asset classes we can gather, the better we'd be. I know that in my SWR backtesting simulations, using a more diversified portfolio changed the outcome in a non-negligible manner. At least for the main categories like international/emerging stock, REITs, small caps, small/large value... Ideally... ;-)
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

JonoJono1 wrote:I can go through the tedium of inserting this data into the backtester and making the excel spreadsheet flawless, once I have a good set of dataseries. But I am not able, for the time being at least, to be confident that I have the right dataseries. Fyi, we will need dataseries that incorporate total return.

Ladygeek: I have Shiller's data that he makes public, which includes the S&P and dividend data. I feel confortable this is a fine series. Do you agree?
I see Robert Shiller's data used often, but I don't have any personal insight on the quality of the data (Online Data - Robert Shiller). You also make an excellent point. For data series, it is important to distinguish between the total return and price return. The difference is significant. For those who need it, the wiki has a brief overview: Index Types.

The index providers go to great lengths to protect their intellectual property, so be absolutely sure that the data series are publicly available. For example, I tried to find a comparison of the S&P 500 Total Return vs. Price Return indexes, but the S&P500 Total Return index (SPXTR) was not on the S&P public website. S&P 500 shows price return only. :( Perhaps this data can be found elsewhere, but it's usually only because there is an agreement in place with the index provider.

===========================================================
<spreadsheet development>
Now, for the flawless spreadsheet. Instead of waiting for a complete complete data series, is your spreadsheet mature enough that you could just replace the missing info with test data? IOW, get the user interface, formulas, charts, etc. in place first. An additional perspective is that you need a known series of inputs to be 100% sure that you get the right outputs. Try using data containing a full range of values and be sure nothing breaks, e.g. 0.00, -1.23, +100, -100, etc. Once you vet the formulas and charts, the insertion of new data is a minor detail.

Your spreadsheet should be flexible to handle the influx of new data series by either: adding a ton of extra room, or, allowing insertion of rows / columns and copying existing formulas without breaking anything (I think the spreadsheet can do this now).

My approach (which may not be your approach), would be to do an end-to-end development and test with one data series - as a start. Then, release it to the members for testing. After that, you can start piling on. There's no rush to do everything at once, and an incremental approach will provide early feedback in case you need to make a significant change.
</spreadsheet development>
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.
OkieIndexer
Posts: 468
Joined: Sun Aug 23, 2009 1:10 pm
Location: Kayaking in Colorado

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by OkieIndexer »

Dimson, Marsh, and Staunton have total return annual data for 14 international developed markets (UK, Japan, Germany, Italy, Australia, etc.) going all the way back to 1900, including a total world index (all of this data was summarized in their book "Triumph of the Optimists"). Not sure how you could get your hands on that data, or if it would be free. I think the spreadsheets used to be on the Internet for free at one time, but I'm not sure if they are now.

You might try emailing one or more of them and see if they can help. Would be fascinating to see the annual data for all the countries, and the total world data.
"In bull markets, people say 'The more risk I take, the greater my return.' But when people aren't afraid of risk, they'll accept risk without being compensated." -Howard Marks, Oaktree Capital
JonoJono1
Posts: 37
Joined: Sun Jul 07, 2013 10:31 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by JonoJono1 »

Ladygreek,

As usual, your comments are so involved, which is great, and a little daunting.

Ok. I agree with the premise you are making (i.e. to build in the space for later input). I am not at that stage quite yet, intentionally. I am still gathering the group's input for the best direction to take the backtester (i.e. those directions that will help us marginally the most). I think we are coming to a consensus that asset definition and dataseries for longer time periods would be best, first. That is an easy build.

We may, as an aside, get just a few fundamental asset classes that we can get reliable 1926 data for and then have the backtester then give us a useful range of pictures that teach us a lot. As we gather new dataseries that we have confidence in over time, adding them will be painless, although a bit more cumbersome if not thought out well now. Either way, adding new dataseries is not techincally difficult (I know you know this). I am more concerned about finding forum members who have access to reliable 1926 data and confidence that the data adequately represents a class. Do you agree with the above? Am I missing something you are saying? Your responses are always so well thought out I do not want to miss something.

As I process this, something is becoming clear to me. When I started building the expanded portfolio comparison tool, I wrote a macro that helped me tabulate results in a way that allowed me to see some decision point outcomes. This was enicing new information. Then I realized I needed longer term data to see whether things played out over longer histories. There are some statistical theories I have that this may be true, but I would rather not hypothesize and just get the data. Ok. But my point is this: the motivation for any updated excel sheet should be utility only. And what I hear you saying is that by building the extra space, members can go off and essentially beta test it, not just for excel code but for practicality. When I think through that dynamic, it makes perfect sense to make one substantial iteration at a time so we can keep getting more and better feedback. I just need to think through the higher altitude sequence. (Do you agree with the above?)
JonoJono1
Posts: 37
Joined: Sun Jul 07, 2013 10:31 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by JonoJono1 »

CallmeOp:

Ok. But do we have a good list of available asset classes we can get reliable longterm dataseries for, even if it is just a few classes? Can you give that to us?
JonoJono1
Posts: 37
Joined: Sun Jul 07, 2013 10:31 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by JonoJono1 »

OkieIndexer,

This is a good lead. Thanks.
Call_Me_Op
Posts: 9872
Joined: Mon Sep 07, 2009 2:57 pm
Location: Milky Way

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by Call_Me_Op »

JonoJono1 wrote:CallmeOp:

Ok. But do we have a good list of available asset classes we can get reliable longterm dataseries for, even if it is just a few classes? Can you give that to us?
I do not have such a list or the associated data. I would have to believe that at a base minimum, this data exists for US large stocks, US small stocks, 10-year treasuries, and cash. Perhaps someone else out there has some data?

Here, at least, is a link to a web page that discusses existing historical data:

http://www.investorhome.com/history.htm
Best regards, -Op | | "In the middle of difficulty lies opportunity." Einstein
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

JonoJono1 wrote:Ladygreek, ...

But my point is this: the motivation for any updated excel sheet should be utility only. And what I hear you saying is that by building the extra space, members can go off and essentially beta test it, not just for excel code but for practicality. When I think through that dynamic, it makes perfect sense to make one substantial iteration at a time so we can keep getting more and better feedback. I just need to think through the higher altitude sequence. (Do you agree with the above?)
Yes. My point was to think from a higher altitude. The principles of software engineering apply, even to spreadsheets:
  • Define your requirements (What do you want this spreadsheet to do?). Allowing the users flexibility to add their own data series would be a requirement (or at least document on how to do it).
  • What are the inputs? The User Interface is important.
  • What are the outputs? What makes sense in terms of tables vs. charts? What messages do you want to convey (flag as a problem, etc.)?
  • Once you know what you have to work with, and how to present the results, your development tasks become clear. Development is what you do to get from Point A (inputs) to Point B (results).
  • Test using every conceivable value, including corner cases (extreme values).
    -- Consider that users will enter the wrong thing no matter how well you document it. Don't go overboard, but it should be clear that something went wrong on a bad input. A broken formula or chart is usually good feedback. As long as the user is aware of a problem, then it should be straight-forward to fix; e.g. what was the last thing changed before it broke?
  • Document clearly so that others can follow (and help out)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by siamond »

Maybe I am going to speak completely out of turn here, but why not be bold and contact somebody like William Bernstein, and ask him the question about ways to fill the gap of historical data per finer grain asset classes?

His emphasis on value and small-cap tilts had to come from thorough research. Same for his recommendation to use REITs in your portfolio. Etc. And he should be friendly to the BH community and a project like refreshing this backtesting tool... I don't know, doesn't hurt trying...
User avatar
BlueEars
Posts: 3968
Joined: Fri Mar 09, 2007 11:15 pm
Location: West Coast

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by BlueEars »

In showing just annual data we miss showing the monthly stock market variations. Most of the time this is not such a big deal, especially in an up market. But there were times where the annual data misses painting a picture of the extreme volatility that US equities have shown. I'm thinking of the 1930's when there were violent swings in both directions. Also in the 1987 crash the SP500 return was about 5% but there was a violent crash mostly contained in October 1987.

Here is one format that shows the extreme monthly returns for the SP500 (synthetic data for 1930's). The months are shown on one column next to there respective monthly returns and color coded to get a quick visual feeling of the swings.


Image

Just might be one thing to get across to investors contemplating high equity allocations. I do not think standard deviation numbers quite express this volatility and the investor emotions that it can entail.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

siamond wrote:Maybe I am going to speak completely out of turn here, but why not be bold and contact somebody like William Bernstein, and ask him the question about ways to fill the gap of historical data per finer grain asset classes?

His emphasis on value and small-cap tilts had to come from thorough research. Same for his recommendation to use REITs in your portfolio. Etc. And he should be friendly to the BH community and a project like refreshing this backtesting tool... I don't know, doesn't hurt trying...
It's not out of turn - I think it's great suggestion. I sent William Bernstein a PM.
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
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

Bill Bernstein has graciously offered assistance via email. Here's his reply on the question about asset classes:
wbern, via email, wrote:The best, and most publicly quoted source is Ken French's data series, available here: http://mba.tuck.dartmouth.edu/pages/fac ... brary.html

I'd go for the SV, LV, SG, and LG series in the 2x3 file, http://mba.tuck.dartmouth.edu/pages/fac ... os_2x3.zip

(Updated 04-Aug-13: from left to right, the 6 columns are SG SB SV LG LB LV. use the first set of data you see, since that's cap-weighted (average value weighted). you don't want equal weighted.)

and for International: http://mba.tuck.dartmouth.edu/pages/fac ... folios.zip
(Updated 04-Aug-13: sorry about the 2x3 for the int'l, not there. you can get the four corners of the EAFE, and almost anything else you want, from the MSCI-Barra site. what those spit out are total return indexes which you download, and then have to compute the monthly returns from.)

Next, I'd pick a few industries: gold miners, base metals, and oil producers from his industry series: http://mba.tuck.dartmouth.edu/pages/fac ... folios.zip

All of these are a bit difficult to extract: the zip files have to be decompressed into .txt, which then have to be imported into Excel using space tab delimitation.

You also want the wider, Ibbotson series, which you'll have to get directly from the SBBI yearbook, and the MSCI emerging markets and EAFE series, which you can download directly from the MSCI-Barra site (you have to do a free registration for these.)

Bill
Update from me (not via wbern): Here is more detailed info-

the SV, LV, SG, and LG series in the 2x3 file:
-Description: Kenneth R. French - Detail for 6 Portfolios Formed on Size and Book-to-Market, Variable Definitions
- Data: 6 Portfolios Formed on Size and Book-to-Market (2 x 3)

a few industries: gold miners, base metals, and oil producers:
- Description: Kenneth R. French - Detail for 48 Industry Portfolios, Data: 48 Industry Portfolios
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by siamond »

LadyGeek wrote:Bill Bernstein has graciously offered assistance via email. Here's his reply on the question about asset classes:
Terrific! He's such an amazing individual! Please convey collective thanks, and a modest "+1" to the long cohort of Bill's admirers... :sharebeer
Cyclone

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by Cyclone »

I have been playing with the compatible version using LibreOffice, and it seems to be working fine.
Clive
Posts: 1950
Joined: Sat Jun 13, 2009 5:49 am

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by Clive »

Change SImba's Emerging Markets returns data for 1976 through 1987 inclusive to use the total return data for EM for those years from http://www.cfapubs.org/toc/rf/1997/1997/3 i.e.

4.78
48.48
42.17
33.42
11.37
-27.55
-31.86
-2.5
-1.37
14.19
32.12
36.01

and there's a distinct difference in historic EM performance indicated by Simba's spreadsheet.

After 1987 SImba's data reflects MSCI EM data which is accurate, 1987 and earlier SImba's EM data ???

IFA data was used for 1987 and earlier - that data is perhaps suspect as to being reflective of EM returns.
User avatar
lauren_knows
Posts: 193
Joined: Mon Jan 28, 2013 2:11 pm
Location: NoVA, USA
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by lauren_knows »

LadyGeek wrote:Bill Bernstein has graciously offered assistance via email. Here's his reply on the question about asset classes:
wbern, via email, wrote:The best, and most publicly quoted source is Ken French's data series, available here: http://mba.tuck.dartmouth.edu/pages/fac ... brary.html

I'd go for the SV, LV, SG, and LG series in the 2x3 file, http://mba.tuck.dartmouth.edu/pages/fac ... os_2x3.zip

and for International: http://mba.tuck.dartmouth.edu/pages/fac ... folios.zip

Next, I'd pick a few industries: gold miners, base metals, and oil producers from his industry series: http://mba.tuck.dartmouth.edu/pages/fac ... folios.zip

All of these are a bit difficult to extract: the zip files have to be decompressed into .txt, which then have to be imported into Excel using space tab delimitation.

You also want the wider, Ibbotson series, which you'll have to get directly from the SBBI yearbook, and the MSCI emerging markets and EAFE series, which you can download directly from the MSCI-Barra site (you have to do a free registration for these.)

Bill
Can anyone decipher these files? I don't see specific mention of "SV, LV, SG, and LG" in the 2x3 file. The charts aren't well labeled, except the "Number of Firms" chart and a couple of the weighted averages.
41 - Married - 2 kids - Aiming for FI/ER in early 40s - Creator of cFIREsim
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

I had the same question, also that the international file duplicated the industry portfolio (incorrect link). So, email sent and email received.

See my previous post for the update.
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
lauren_knows
Posts: 193
Joined: Mon Jan 28, 2013 2:11 pm
Location: NoVA, USA
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by lauren_knows »

LadyGeek wrote:I had the same question, also that the international file duplicated the industry portfolio (incorrect link). So, email sent and email received.

See my previous post for the update.
Still not 100% clear. Before you get to the weighted annual returns there are 2 charts. The description leads me to believe that there would be a Small/Large Value, Small/Large Neutral, and Small/Large Growth charts. I'm guessing the 2 charts that I do see are Small/Large Value, then Small/Large Growth. Right?
41 - Married - 2 kids - Aiming for FI/ER in early 40s - Creator of cFIREsim
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

I'm not sure I understand your question. Starting from here: Kenneth R. French - Data Library there are 2 tables (if that is what you mean by charts). They contain information related to the Fama/French factors (see: Fama-French three-factor model analysis) and return information for both aggregate values and benchmarks.

Next, take a look at Detail for 6 Portfolios Formed on Size and Book-to-Market which contains a 2x3 (column x row) table. The data file contains this information.

It's all a question of relating the terminology in this table to the more common categories associated with the equity Style Boxes. Which category in the equity style box (SV, LV, SG, LG) gets mapped to it's (book/market equity), size equivalent in the French table? As Bill Bernstein states, it's tricky (and is why I got stuck).
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
BlueEars
Posts: 3968
Joined: Fri Mar 09, 2007 11:15 pm
Location: West Coast

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by BlueEars »

Maybe this interpretation of the 6_Portfolios_2x3.txt will help. I cut out snippets from the file. We know that in the year 1999 that growth wildly outperformed value (SG better the SV, LG better then LV) and the situation reversed in a relative way in around March 2000.

This snippet from the file shows this relative behavior for 1999-2000. I labeled the middle columns as SN and LN but we generally use SB and LB for them.


Image

Did that help or am I missing the question. Personally I sometimes think the academic stuff is unnecessarily obscure. :happy
JonoJono1
Posts: 37
Joined: Sun Jul 07, 2013 10:31 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by JonoJono1 »

All,

I am just chiming in here, but I agree with Bill Bernstein, "It's tricky" and that is why "I got stuck."

I am trying to unravel this like everyone else.

We'll get it.
User avatar
lauren_knows
Posts: 193
Joined: Mon Jan 28, 2013 2:11 pm
Location: NoVA, USA
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by lauren_knows »

LadyGeek wrote:
M1garand30064 wrote:This may be overly ambitious, but I'd like to see a 401k simulator be added. Ideally I'd like to be able to pick an asset allocation, enter a starting salary and expected raises and see what your portfolio would be worth after x number of years of contributions.

Something replicating this would be great! http://www.bloomberg.com/personal-finan ... tors/401k/

Is this doable?
Interesting concept, but it's an entirely different approach. Doing a straight-forward future value (FV) calculation belies the underlying complexity of what you're really asking. The results would not be realistic. For example, expected inflation should be incorporated. You need to change your tactics and use statistics to predict future performance.

What I'm leading up to is that you need to implement Monte Carlo analysis or any number of different math techniques (it gets complicated). This brings you into the realm of retirement planning calculators. They're in the wiki: Retirement calculators and spending Calculators which utilize historical U.S. stock and bond returns are designated as "Hret" types.

Based on complexity and the difficulty of verifying the results, I'd say your suggestion is not practical to implement. (Nice idea, though.)
Do you think that this crowd would enjoy a monte-carlo simulator that allowed for multiple asset classes? It'd be pretty easy to come up with the geometric mean and standard deviation for each asset class. I already have the standard stocks/bonds monte carlo simulator working on my site. It would not be far fetched to allow for a bunch of asset classes.
41 - Married - 2 kids - Aiming for FI/ER in early 40s - Creator of cFIREsim
User avatar
lauren_knows
Posts: 193
Joined: Mon Jan 28, 2013 2:11 pm
Location: NoVA, USA
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by lauren_knows »

On the topic of data... I was looking at the backtester spreadsheet and comparing it to the Ibbotson 1926-1974 data. It seems like the backtester on has Long-term Corporate Bond data from 1985-present, and Ibbotson's dataset (that is publicly available) is only 1926-1974. Is there any data to fill the gap between 1974-1985? My google-fu is failing me.
41 - Married - 2 kids - Aiming for FI/ER in early 40s - Creator of cFIREsim
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

boknows wrote:Do you think that this crowd would enjoy a monte-carlo simulator that allowed for multiple asset classes? It'd be pretty easy to come up with the geometric mean and standard deviation for each asset class. I already have the standard stocks/bonds monte carlo simulator working on my site. It would not be far fetched to allow for a bunch of asset classes.
Remember that the focus in this thread is a DIY spreadsheet. Monte Carlo analysis (which distribution to use...) might take this up a notch, but then you'd have another level of complexity interpreting the results.

The spreadsheet can give you insight by simply changing the start / stop dates and looking at the changes in the rolling returns. I consider that technique a "brute-force" sampling window in which you know exactly what's going on.

If you want to go a step further, you can always change to the Fama/French 3-factor model and then do the simulations in R. That's described here: Multifactor investing - a comprehensive tutorial. It's regression testing for a slightly different purpose. Excel becomes very cumbersome when pushed to this level of analysis.
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
BlueEars
Posts: 3968
Joined: Fri Mar 09, 2007 11:15 pm
Location: West Coast

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by BlueEars »

LadyGeek wrote:...(snip)...
The spreadsheet can give you insight by simply changing the start / stop dates and looking at the changes in the rolling returns. I consider that technique a "brute-force" sampling window in which you know exactly what's going on.
...
Remember in 2008 how much angst was generated by the extreme down market volatility? Even before the year was out (and thus no annual data), some people were jumping out the window -- or so to speak. Some were finding they had misjudged their risk tolerances. I personally was having cat fits but did not bail.

So I'd just put in another plug for somehow showing monthly volatility for a given AA (and for fixed income maybe corporate bonds versus Treasuries). I know the data might be hard to come by for fairly ancient times like the 1930's, especially in bonds. We don't even have to cover all the years, just some known volatile investment environments. Or maybe even just a table with equity/bond ratios. Some how it has to express the gut level horror and this might be hard to capture. :happy
User avatar
cflannagan
Posts: 1208
Joined: Sun Oct 21, 2007 11:44 am
Location: Working Remotely

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by cflannagan »

Apologies if this was already discussed, attempted, done, failed, etc...

We all are aware of Google Doc's 400,000 cell per spreadsheet limit. I tried to convert Jon's backtester spreadsheet and ran into that limit.

I was wondering if anyone created a Google Docs-friendly "lite" version of the spreadsheet?

If no one has done this or hasn't attempted, please don't bother putting energy into it - I am fine with playing with spreadsheet at work (where I have Office installed), no big deal.

Thanks in advance!

edit: actually, i'm good now - downloaded LibreOffice for free.. it will server my needs nicely for now ;)
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by LadyGeek »

The charts are too complex for Google Docs (Sheets), have fun with LibreOffice. When saving from within LibreOffice, keep the file in .xls format. Otherwise, you'll get compatibility problems. The .xls format is the "most" compatible between them.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by siamond »

JonoJono1 wrote:All,

I am just chiming in here, but I agree with Bill Bernstein, "It's tricky" and that is why "I got stuck."

I am trying to unravel this like everyone else.

We'll get it.
JonoJono1, any news on your 'unraveling' challenging task?

(yes, I know, easy to ask, much harder to do!)
scone
Posts: 1457
Joined: Wed Jul 11, 2012 4:46 pm

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by scone »

Hello, I wanted to mention something that's not a bug, exactly, but I'm not sure I understand what's going on. And maybe I'm not reading the graph right, so please set me straight.

On the "Compare Portfolios" page, the graphic labeled "Portfolios Return vs. Risk" appears to be showing the total "annualized standard deviation." Does that mean it's showing both the "Up SD" as well as the "Down SD?" If that's the case, it's a little misleading, at least to me-- I suppose, technically, the "Up SD" could be seen as a "risk," but I think the layperson would only consider the "Down SD" as a risk. I mean, I'm only worried about losing money, not about making money! Hope that makes sense. TIA.
"My bond allocation is the amount of money that I cannot afford to lose." -- Taylor Larimore
blorman
Posts: 16
Joined: Tue Nov 19, 2013 9:11 am

Re: JonoJono1: Improve, Revise, Update, Debug Simba's Backte

Post by blorman »

Hello,

I have a problem with the "Portfolio" tab and "Compare_Portfolios" tab disagreeing on returns. If I enter a 100% VTSMX portfolio in each tab I see:

Code: Select all

Portfolio:          nominal: $468,803 CAGR: 9.84 StdDev: 18.18
Compare_Portfolios: nominal: $401,791 CAGR: 9.43 StdDev: 18.18
Time range: 1972-2012
Version: JonoJono1 Portfolio Backtester ver 1.0
Software: LibreOffice 4.1.3.2
Post Reply