Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
Tyr0ne
Posts: 64
Joined: Thu May 10, 2012 1:35 pm

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

Post by Tyr0ne »

blorman wrote: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
I am using the spreadsheet from here and am seeing identical CAGR's and Std Dev's (9.84, 18.18) on both tabs. Maybe double check you have the start and end year set to the same range?
There are times when, at least for now, one must be content to love the questions themselves - Neil deGrasse Tyson
blorman
Posts: 14
Joined: Tue Nov 19, 2013 10:11 am

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

Post by blorman »

I am using the spreadsheet from here and am seeing identical CAGR's and Std Dev's (9.84, 18.18) on both tabs. Maybe double check you have the start and end year set to the same range?
Thanks for the reply TyrOne. Which software are you using?

I'm sure I have the years right. If I download a fresh copy of the sheet from the first post in this thread, and just put in 100% VTSMX in Compare_Portfolios, I get 9.43/18.18.

I just bit the bullet and purchased Excel. Downloading now...
blorman
Posts: 14
Joined: Tue Nov 19, 2013 10:11 am

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

Post by blorman »

I see a couple of problems with the rolling returns calculated in "Returns_72_12" and plotted in "Portfolio" tab.

1) The rolling window is in the future, not the past. For example, the 5 year rolling window for 1980 is 1980-1984. This doesn't make any sense to me - it should be calculated for the year ending the period.

2) The rolling return window is one year short of what it should be. For example, for the 5 year window 1980-1984, the 5 year return is calculated between end-of-1980 and end-of-1984. This is only 4 years of returns; it should be using end-of-1979. Note that the CAGR denominator is correctly using the 5 year time period.

If anybody agrees I can fix it. What's the procedure for updating the spreadsheet?
User avatar
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

Updated with 2014 data. Thanks Hatch Batten and Paul Douglas Boyer for Rev14a that I used as starting point.

From revision history tab:
****************************************************************************************************************************************
Rev14b
1. Serbeer checked and updated spredsheet fixing outstanding issues with rev14a formulas
2. Updated rolling returns calculations


Since there is report that Excel 2003 .xls version works better in OpenOffice, will be using that going forward:
Backtest-Portfolio-returns-rev14b.xls

(That was original post in xlsx format: Backtest-Portfolio-returns-rev14b.xlsx)
****************************************************************************************************************************************
Reminder: Once Google Drive page with images of tabs of spreadsheet loads, you have to click on Download (arrow pointing downwards) button to get the entire spreasdheet in .xls format which you can open locally on your PC if you have MS Excel 2000 or older installed (or LybreOffice or OpenOffice--but that is not supported though works for many). I use Google Drive for storage only, this is not Google spreadsheet, this is Excel spreadsheet, so ignore all errors.
Last edited by serbeer on Thu Jan 29, 2015 10:57 am, edited 10 times in total.
User avatar
JasonF
Posts: 98
Joined: Sun Jan 17, 2010 8:12 am

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

Post by JasonF »

serbeer,

Thanks so much for updated the spreadsheet, I think it's a great resource.
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

serbeer - You're all set. I merged JonoJono1's thread into here and updated Post #1 to point to your post.

I also updated the wiki to point back here: Simba's backtesting spreadsheets (Removed the JonoJono1 thread). If I can get to it, or if someone would like to help, an overview and description of what this spreadsheet does would make a nice addition to the wiki.

Now, lets talk about compatibility. As mentioned in my post: Spreadsheet Design and Testing, I revised the spreadsheet to work with LibreOffice Calc. Unfortunately, it never got cut into JonoJono1's spreadsheet and it's not in your Excel version.

Would you mind updating your spreadsheet in a similar manner so those without access to MS Excel can use it? If not, I'd be happy to make the update, but I won't be able to get to it for a few days. I documented the changes in the README tab, starting at Q62. The changes need to be made in MS Excel, then saved as an .xls file.
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
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

Thank you LadyGeek.

As far as compatibility is concerned, I do not mind merging the spreadsheet if at least a couple of other people on this board express their interest in LibreOffice Calc-compatible version by January 17 when I plan update the spreadsheet again to revise CPI-U numbers after their official release. Simply to avoid doing purely esoteric work since I've never known anyone using LibreOffice Calc myself while a copy of MS Office 97 or 2000 that this and most other spreadsheets can run on just fine can be purchased off Ebay for less than $20 and Office 2003 (that can use xlsx files with free add-on) goes for $25...

By the way, if anyone see problems with data for 2013 or spreadsheet itself, make sure to report it by Jan. 17 as well please, much easier to do all changes at the same time.
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

You're welcome. I just took a look using MS Excel. The charts in the Portfolio tab are broken - no data is selected. I'm guessing that the something broken during the update. The charts need to be rebuilt.

Use this for a reference: JonoJono1 Portfolio Backtester - LibreOffice compatible.xls, which has all the charts working - including the rolling window checkboxes (what broke in LibreOffice).

Since that spreadsheet has all the work done, perhaps I can just copy the charts and edit the Series() entries.

BTW, you can dump an export of the Defined Names from the ribbon: Defined Names --> Use in Formula --> Paste Names...

Update: I also needed to copy some formulas over from the JonoJono1 spreadsheet. Spreadsheet updated, now in testing.
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.
pvguy
Posts: 27
Joined: Mon Sep 23, 2013 7:54 pm
Location: Austin, TX
Contact:

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

Post by pvguy »

serbeer wrote:By the way, if anyone see problems with data for 2013 or spreadsheet itself, make sure to report it by Jan. 17 as well please, much easier to do all changes at the same time.
When I compared the 2013 asset class returns in the spreadsheet against the ones I had updated on my web based version earlier I noticed the following differences:

1. BRSIX - MicroCap
Fidelity shows 48.40%, which is currently in the spreadsheet. However, the Fidelity page states that the data is as of 11/30/2013. Both Morningstar and Yahoo show 50.91%. Bridgeway's page for the fund has not yet been updated (data as of 09/30/2013).

2. PIGLX - Global Bond
Fidelity shows -4.63%, which is currently in the spreadsheet. However, the Fidelity page states that the data is as of 11/30/2013. Both Morningstar and Yahoo show -5.04%. PIMCO's page for the fund has not yet been updated (data as of 11/30/2013), but matches the Fidelity number for 11/30/2013.

I recommend changing the 2013 annual returns for the two asset classes above to match the Morningstar number at least until the final 2013 returns are published on Bridgeway's and PIMCO's pages for each fund since it looks like the Fidelity number does not include the full year.

Edit: spelling
User avatar
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

LadyGeek wrote:You're welcome. I just took a look using MS Excel. The charts in the Portfolio tab are broken - no data is selected. I'm guessing that the something broken during the update. The charts need to be rebuilt.

Use this for a reference: JonoJono1 Portfolio Backtester - LibreOffice compatible.xls, which has all the charts working - including the rolling window checkboxes (what broke in LibreOffice).

Since that spreadsheet has all the work done, perhaps I can just copy the charts and edit the Series() entries.

BTW, you can dump an export of the Defined Names from the ribbon: Defined Names --> Use in Formula --> Paste Names...

Update: I also needed to copy some formulas over from the JonoJono1 spreadsheet. Spreadsheet updated, now in testing.
LadyGeek,
no charts in Portfolio tab are broken when I open the spreadsheet on my Windows 7 / Excel 2010 PC. Everything charts perfectly fine on my local copy of the spreadsheet that I uploaded to Google Drive. I tried different date ranges, they all work and chart fine. Not 100% sure what to make of it, hopefully someone else can try it and confirm the report of the problem.
User avatar
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

pvguy wrote:
serbeer wrote:By the way, if anyone see problems with data for 2013 or spreadsheet itself, make sure to report it by Jan. 17 as well please, much easier to do all changes at the same time.
When I compared the 2013 asset class returns in the spreadsheet against the ones I had updated on my web based version earlier I noticed the following differences:

1. BRSIX - MicroCap
Fidelity shows 48.40%, which is currently in the spreadsheet. However, the Fidelity page states that the data is as of 11/30/2013. Both Morningstar and Yahoo show 50.91%. Bridgeway's page for the fund has not yet been updated (data as of 09/30/2013).

2. PIGLX - Global Bond
Fidelity shows -4.63%, which is currently in the spreadsheet. However, the Fidelity page states that the data is as of 11/30/2013. Both Morningstar and Yahoo show -5.04%. PIMCO's page for the fund has not yet been updated (data as of 11/30/2013), but matches the Fidelity number for 11/30/2013.

I recommend changing the 2013 annual returns for the two asset classes above to match the Morningstar number at least until the final 2013 returns are published on Bridgeway's and PIMCO's pages for each fund since it looks like the Fidelity number does not include the full year.

Edit: spelling
Thanks pvguy. Fidelity pages have already been updated to "AS OF 12/31/2013" and numbers now match those on other sites. That will be reflected in the next update.
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

serbeer wrote:...LadyGeek,
no charts in Portfolio tab are broken when I open the spreadsheet on my Windows 7 / Excel 2010 PC. Everything charts perfectly fine on my local copy of the spreadsheet that I uploaded to Google Drive. I tried different date ranges, they all work and chart fine. Not 100% sure what to make of it, hopefully someone else can try it and confirm the report of the problem.
I'm not sure what went wrong. :confused In any case, I incorporated the Portfolio chart changes to work with LibreOffice Calc: Backtest-Portfolio-returns-rev13a3.xls. Those with LibreOffice / OpenOffice (and Excel) should give it a try. I made no changes other than what was needed for the charts.

I incremented the revision number and documented everything in the README tab. Consider updating your master copy to this version and update your post. From there, you can incorporate bug fixes on Jan 17.

I'm pushing compatibility with LibreOffice is because (1) it's free and (2) it it runs on Windows, Linux, and Mac. Everyone who can views this forum can use the spreadsheet for free. I've got MS Excel on my desktop but also run Linux. I also have a Win 7 laptop with LibreOffice.

<For the tech crowd>
Simba used a very creative technique to automatically scale the Portfolio charts. The charts contain a Defined Name which translates to a cell range. By pointing to a cell range, the data is automatically updated.

Unfortunately, LibreOffice Calc does not supported Defined Name in charts. (Defined Names are supported, but not in charts.) I worked around this problem by reverse engineering the names and regenerating the data. Scatter charts are used to autoscale the display.
</For the tech crowd>
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
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

LadyGeek wrote: I incorporated the Portfolio chart changes to work with LibreOffice Calc: Backtest-Portfolio-returns-rev13a3.xls. Those with LibreOffice / OpenOffice (and Excel) should give it a try. I made no changes other than what was needed for the charts.

I incremented the revision number and documented everything in the README tab. Consider updating your master copy to this version and update your post. From there, you can incorporate bug fixes on Jan 17.
Thank you. This is done. I simply re-posted as 13b, fixed return data as well.
I'm pushing compatibility with LibreOffice is because (1) it's free and (2) it it runs on Windows, Linux, and Mac. Everyone who can views this forum can use the spreadsheet for free. I've got MS Excel on my desktop but also run Linux. I also have a Win 7 laptop with LibreOffice.
I guess it makes sense to be able to use it on Linux.
<For the tech crowd>
Simba used a very creative technique to automatically scale the Portfolio charts. The charts contain a Defined Name which translates to a cell range. By pointing to a cell range, the data is automatically updated.

Unfortunately, LibreOffice Calc does not supported Defined Name in charts. (Defined Names are supported, but not in charts.) I worked around this problem by reverse engineering the names and regenerating the data. Scatter charts are used to autoscale the display.
</For the tech crowd>
Yes, this is great way to work with dynamic datasets. If you need to constantly update and add to to your data, or if you work with charts and PivotTables, you'll want to create dynamic named ranges, which expand and contract relative to your data. If you use dynamic named ranges in lieu of range references in charts, your chart will plot any new data the moment you add it to your worksheet. I use the same technique in my own performance tracking spreadsheets and charts are updated automatically as I add new set of annual performance data to summary tables.
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

In the JonoJono1 thread (now merged here), there was a request for historical data. I think the question got dropped.Would any of this data be useful in the spreadsheet? Here's the post trail if anyone is interested. Subject: Spreadsheet for backtesting (includes TrevH's data)
JonoJono1 wrote: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.
Subject: Spreadsheet for backtesting (includes TrevH's data)
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...
With assistance from William Bernstein (wbern): Subject: Spreadsheet for backtesting (includes TrevH's data)
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

(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
Technically, this is known as "requirements creep" meaning that a new unplanned feature was added to the project - cost and schedule increase accordingly. Perhaps we should get serbeer's spreadsheet debugged first, then think about long-term features. Or not. I'm just tracking what was discussed.
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: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

The wiki has been updated to include a brief overview of backtesting and the spreadsheet contents: Simba's backtesting spreadsheet

Comments / questions / corrections are welcome.

To the lurkers (those viewing the forum without logging in): Every wiki article now has a reader feedback form "Help improve this page". If you want to comment on this article, just click on the "Yes" or "No" button and an edit box will appear for you to enter comments.
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: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Here are some suggested corrections to the README tab. I corrected the dates in the wiki.
The worksheet Portfolio is a simple way to change the allocations of various funds. It provides the CAGR/total/Sharpe Ratio etc. It also compares your portfolio to
sample portfolios like Coffeehouse and draws charts to compare the growth.
The worksheet Returns_72_13 calculates portfolio returns (total/cagr/sharpe ratio/correlation etc) for funds from 1972-2013
The worksheet Returns_85_13 calculates portfolio returns (total/cagr/sharpe ratio/correlation etc) for funds[includes sector funds] from 1985-2013
The worksheet Data_72_13 includes the returns of various funds from 1972-2013
The worksheet Data_85_13 includes the returns of various funds from 1985-2013 (to include many of the funds that were started in 1984)
The worksheet Data_Sources lists the sources of the various fund returns

The worksheet Compare_Portfolios allows you to compare up to 5 different portfolios for 1972-2013 and 1985-2013 with the abilty to change the starting and ending years.
The Spreadsheet also includes the total/cagr etc for a portfolio that's rebalanced annually (default) and one that is not rebalanced (un-rebalanced) and the
charts reflect both the returns.
"up to" notes a missing space between the words.
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.
mtbomb
Posts: 3
Joined: Wed Jan 23, 2013 2:50 am

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

Post by mtbomb »

Can someone point me in the right direction for finding an automated way to compute the best allocation for return given a std dev?
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Return vs. std deviation is also known as the efficient frontier (mean variance optimization), which is beyond this spreadsheet - at least for automating.

May I recommend using a programming language? Take a look in the wiki: Using open source software for portfolio analysis Scroll down to "Efficient frontier (mean-variance optimization)".
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
Clearly_Irrational
Posts: 3087
Joined: Thu Oct 13, 2011 3:43 pm

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

Post by Clearly_Irrational »

mtbomb wrote:Can someone point me in the right direction for finding an automated way to compute the best allocation for return given a std dev?
Using the solver should work fine, just optimize for the sharpe ratio. (personally I prefer the sortino ratio but that's a different conversation)
User avatar
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

mtbomb wrote:Can someone point me in the right direction for finding an automated way to compute the best allocation for return given a std dev?
Funny, but my very first post on this forum 5 years ago was a report of my (successful) attemp to do something very similar.

I actually took your objective a step further and automated computation of maximization of return with simultaneous minimization of standard deviation. By the way it resulted in my discovery of what was already know on this board as Larry Swedroe's portfolio.

I did it is through use of Crystal Ball company software (nowadays purchased and owned by Oracle) that I had access to.
http://www.oracle.com/us/products/appli ... index.html

Basically, on a very simplistic level, it allows to run Monte Carlo simulations in any Excel spreadsheet by designating certain fields as variables that the software changes randomly in accordance with defined distributions while attempting to minimize/maximize other fields in the spreadsheet. So I simply designated each of the tickers in the Simba spreasheet's 1972 Portfolio tab as variable being changed from 0 to 100% using linear distribution and ran the multi-hour simulation that tried tens of millions of random portfolio combination with objective to maximize Return field and minimize SD field at the same time. The tool uses special way to speed up Excel by "compiling" it into so-called Extreme speed mode program that runs calculations hundreds of times faster than original Excel engine itself, so simulation takes hours and not days it would take otherwise. The results you can find in my original post:
http://www.bogleheads.org/forum/viewtopic.php?t=10313

Hope you find this information useful. Crystal Ball is great for this type of researh, but it is not cheap and I must say that it takes more courage to act upon results than I had, so I ended up with rather trivial 75/25 portfolio.
User avatar
siamond
Posts: 5561
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Many thanks for the 2012/2013 update, folks. Very useful.
LadyGeek wrote:Technically, this is known as "requirements creep" meaning that a new unplanned feature was added to the project - cost and schedule increase accordingly. Perhaps we should get serbeer's spreadsheet debugged first, then think about long-term features. Or not. I'm just tracking what was discussed.
Ladygeek, to answer your question, yes, I continue to believe it would extremely useful to extend the data sets and provide 1927-till-now returns on various asset classes where the information is available. I'm saying 1927 because a) this would include the 1929 crisis b) the Fama-French data does go there. Also the Amodovar data starts at the same time. I don't think this is 'requirements creep' (although it is certainly more work), in the words of Mr Bernstein himself (in the 4 Pillars book), we should always use the longest data series available to get more meaningful results.

I actually looked at the 2x3 data (going to the FF Web site to get the latest), thanks to the explanations wbern provided, and imported it in a simple Excel sheet and compared to some equivalent returns from the Simba sheet. This does match... rather approximately... I am a bit puzzled that it doesn't match more precisely, but well, that's a start. Here is the Excel sheet if anybody is interested:
(link removed)

I'll take a look to the other sources - would be good to have REITs, Gold and Int'l/Emerging equities at least, and see what goes.

Anyhoo, no rush, I totally agree with you, better get the kinks out of the 2013 Simba Excel sheet as is, and then and only then ponder to add such longer-term returns - if somebody is willing to do the corresponding work.

EDIT: link removed, as Ken French indicated to me by e-mail that he'd rather not see his data re-published in such 'friendly' format. This is unfortunate, but with a modicum of effort, anybody can create their own private spreadsheet with the FF library data set, as explained in the Wbern post.
Last edited by siamond on Wed Jan 06, 2016 6:48 pm, edited 1 time in total.
User avatar
siamond
Posts: 5561
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

LadyGeek wrote:
wbern, via email, wrote: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.)
[...]
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
Well, I tried to get access to the MSCI data, registration is indeed free, but... restricted to MSCI clients & customers... :(

PS. I also uploaded the 48-Industry-Portfolios data in the Excel sheet that I shared in the previous post. Returns for real estate and gold do not seem to be correlated whatsoever to the corresponding Vanguard funds though. Not too sure what to do with those.
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

siamond - I can appreciate your frustration.

For those not familiar with the concern, I should explain that generating and tracking indexes is a highly competitive business. MSCI and other index providers, e.g. S & P Dow Jones, guard their algorithms (intellectual property) and data very aggressively. Generally, if you need the information, you must pay.

Access is negotiated with the index providers and is why certain indexes are found in Yahoo Finance! and not in Google and vice-versa. For this spreadsheet, we need to limit the content to freely accessible information.

Some background info on indexes: Stock market indexing
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: 5561
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

LadyGeek wrote:For this spreadsheet, we need to limit the content to freely accessible information.
Yep, this is well understood. I was hoping this was the case for MSCI, but alas, nope. Oh well. Need to try something else...
mtbomb
Posts: 3
Joined: Wed Jan 23, 2013 2:50 am

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

Post by mtbomb »

mtbomb wrote:Can someone point me in the right direction for finding an automated way to compute the best allocation for return given a std dev?
So I have a crude script working with the returns in this spreadsheet. I hope to improve it over time, so let me know if you find any bugs. Right now I am just using it to find low std_dev portfolio allocations which is my main goal.

https://github.com/maxtower/simba_monte_carlo


EDIT: there are definitely some bugs, but it's a work in progress.
User avatar
midareff
Posts: 7141
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

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

Post by midareff »

serbeer wrote:
mtbomb wrote:

YOUR TIME AND EFFORTS ARE VERY APPRECIATED BY ALL OF US. :D Now if only someone added IT Investment Grade Bonds to the categories, or both ST and IT.

8-)
M1garand30064
Posts: 89
Joined: Tue Sep 04, 2012 8:49 pm

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

Post by M1garand30064 »

Has this been updated to reflect the CPI data released a few days ago? Thanks again. This is a wonderful achievement by the members here! :sharebeer
User avatar
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

M1garand30064 wrote:Has this been updated to reflect the CPI data released a few days ago? Thanks again. This is a wonderful achievement by the members here! :sharebeer
Just posted 13c update, sorry for delay, enjoy
User avatar
czeckers
Posts: 1044
Joined: Thu May 17, 2007 3:49 pm
Location: USA

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

Post by czeckers »

Where can I find a link to the most recent update (13c mentioned in the preceding post)? The wiki just links back to this thread.

-K
The Espresso portfolio: | | 20% US TSM, 20% Small Value, 10% US REIT, 10% Dev Int'l, 10% EM, 10% Commodities, 20% Inter-term US Treas | | "A journey of a thousand miles begins with a single step."
User avatar
BigFoot48
Posts: 2826
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

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

Post by BigFoot48 »

czeckers wrote:Where can I find a link to the most recent update (13c mentioned in the preceding post)? The wiki just links back to this thread.

-K
13c can be found here: http://www.bogleheads.org/forum/viewtop ... 4#p1912664
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Great timing, as someone just asked the same question as part of the wiki's reader feedback form, which is at the bottom of every article. We promote useful comments to the article's discussion tab: Talk:Simba's backtesting spreadsheet

I updated the article to point back to the thread, along with a link to the actual post containing the download. See: Simba's backtesting spreadsheet

All wiki editors are welcome to assist with the reader feedback. If any editor has a question on this feature, post in the wiki editor's forum (visible to wiki editors).
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
czeckers
Posts: 1044
Joined: Thu May 17, 2007 3:49 pm
Location: USA

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

Post by czeckers »

Got it, thanks.

Again, a heart felt thank you to all who have worked so much to make this wonderful resource available to all of us. Special thanks to Simba, JonoJono, Serbeer and Lady Geek.

-K
The Espresso portfolio: | | 20% US TSM, 20% Small Value, 10% US REIT, 10% Dev Int'l, 10% EM, 10% Commodities, 20% Inter-term US Treas | | "A journey of a thousand miles begins with a single step."
dcabler
Posts: 1525
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler »

Hi - been lurking for a long time and I have a question. Has anybody gone back and looked at the Short Term bond data? The documentation says it uses a Vanguard index fund for the later dates, but IFA data (2F) for the earlier dates. The problem is that according to the IFA website, column 2F is a global index which really doesn't align with VFISX that is used in later years in the spreadsheet. I think we need a better source of short term govt bond index data before VFISX's existence.

Also, when synthetic TIPs were added, there was a large discussion. What ended up in the spreadsheet was eyeballed from the Kathari paper. I have found a copy of it on the web and attempted to replicate the eyeballing process. Some of the numbers I pulled off bore little resemblance to what's in the spreadsheet. Part of it is because it's a just an "eyeballing" and two people may see something different and part of it is because there are a number of places where the paper's data is experience a very fast slope right on a year boundary. Fat graph lines and fast slopes can make for really large errors.



Thanks,
Big-Papa
Last edited by dcabler on Mon Mar 10, 2014 6:45 am, edited 1 time in total.
Target2019
Posts: 471
Joined: Sat Mar 03, 2007 5:30 pm

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

Post by Target2019 »

I am confused by all the threads and posts and spreadsheets...

But I can contribute this. In a copy of jonojono1 spreadsheet, On Compare_Portfolios worksheet, change the "Starting Year for backtest" in 1985-2012 comparison for a 100% VEIEX portfolio. The total unbalanced does not change when 100% VEIEX is entered into P5 column. It works properly in columns P1-P4, though.

For some reason I believe this may be so in any other spreadsheets, so just a minor contribution to y'all.

EDIT: I confirmed that this error is also in the 13c spreadsheet.
Call_Me_Op
Posts: 7983
Joined: Mon Sep 07, 2009 2:57 pm
Location: Milky Way

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

Post by Call_Me_Op »

Does anyone know where I can find the latest version of Simba's spreadsheet in Open Office format?
Best regards, -Op | | "In the middle of difficulty lies opportunity." Einstein
User avatar
BigFoot48
Posts: 2826
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

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

Post by BigFoot48 »

LibreOffice Calc 4.2.1.1 appears to run rev13c just fine. I did have a problem with Calc 4.2.2 running my Roth model, so I can't recommend it but it may also run this model.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Clive
Posts: 1950
Joined: Sat Jun 13, 2009 5:49 am

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

Post by Clive »

LadyGeek wrote:... I incorporated .... changes to work with LibreOffice Calc: Backtest-Portfolio-returns-rev13a3.xls.
Looks like it might also work with gNumeric - providing you arrange the EPIC codes for each fund in sorted order on the Portfolio worksheet i.e. sort B3:C39 by column C ascending. AND set cells AD3 to AL53 to empty in the Returns_72_12 worksheet (otherwise they N/A and corrupt the total counts). I only checked the 1972 onwards parts, likely similar changes would also have to be applied to the 1985 onwards part.

gNumeric expects VLOOKUP range values to be in sorted order, the alternative would be to use a Index and Match combination against unsorted sets and adjust the formula in Returns_72_12 worksheet cells B3:AC3 to that Index(match) type formula.
Clive
Posts: 1950
Joined: Sat Jun 13, 2009 5:49 am

TrevH/JonoJono backtest spreadsheet under gNumeric

Post by Clive »

RE: TrevH/JonoJono backtest spreadsheet under gNumeric

Windows stop support of XP as of today. XP still has a large user-base and for those, the following might be of use.

There are many Linux variants, one of which can be downloaded from this link

http://archive.org/download/Puppy_Linux ... get-ff.iso

It's a ISO image that needs to be burnt to a CD/DVD. Assuming you're running Windows you can use 7-ZIP or WinRar or ... whatever archive program you prefer to view the content of that ISO once its been downloaded. Within the "burnwin" directory in that ISO there are a couple of CD/DVD burning utilities - one for 32 bit machines, another for 64 bit machines. Grab a copy of the appropriate choice and copy that to your desktop and then run it and burn the ISO to a blank CD/DVD.

Image

Set your PC BIOS to have a boot order that checks CD/DVD before HD - BIOS is usually accessed by pressing DEL or F2 or F8 or maybe something else whilst your PC is booting. Windows 8/8.1 users probably wont be able to do that as Microsoft in their wisdom have decided to take over control of BIOS for themselves - such that its more a case of they own both the software and hardware (Windows Computer (WC)) - despite you having paid for the hardware. There are means to undo that, but if your using Win 8/8.1 you probably wouldn't want to go down that path.

Reboot your PC with the CD/DVD in the drive, and a variant of Puppy Linux will start up and load. The ISO image is around 93MB, but within that is a fully functional operating system and desktop - which loads entirely into memory. Running in memory means its fast. You don't even need a HD to be present. All of your partitions will be unmounted after booting. You can however access them/it by mounting the partition (clicking on the drive image towards the bottom left of the screen), and see (read/write) to any of those files

Follow the guidance and set your locale, graphics, set up networking ...etc. The first time might take a while, but over time that process becomes a lot quicker with familiarity. For instance I can get from powered off to having booted, setup and installed additional programs such as video editing within less than 5 minutes.

Next install gNumeric - a spreadsheet program, which also comes with abiWord (word processor), you can download that from

https://archive.org/details/Puppy_Linux_ThinSlacko

which also contains a number of other programs/utilities, the one you want has a filename something like abiword-2.8.6-gunmeric.....sfs

the sfs means its a squashed file system (compressed to save space). To load a sfs from within linux you right click on it and select the "sfs_load" choice.

As a side note there are files with a .pet suffix which are similar, except you can load those simply by clicking them.

Both sfs and pet files install additional programs into memory (RAM), so after loading the abiword/gnumeric sfs as above, the menu (bottom left green button) will have a option under Business for GNumeric i.e. to start the spreadsheet program.

A variant of Jonojono's backtest spreadsheet for gnumeric can be downloaded from

https://drive.google.com/file/d/0B4MbXu ... sp=sharing

I've made some changes and its not 100% there but mostly ok (graph line names are missing). I'm not familiar with gnumeric myself and I'm on a steep learning curve. But whilst different its also quite familiar (just a little frustrating that I don't know how to do things that I normally do more intuitively with Excel).

I'd suggest downloading all of the above files when you download the ISO image and put them all into c:\ perhaps (or a newly created subdirectory). That way if you have problems getting Linux to connect to the network you'll at least still be able to try out the spreadsheet.

Puppy Linux in being small and able to run totally in memory (RAM) means that a lot of functionality is missing. Instead of pop up windows that enable you to do things such as add things to the taskbar (tray at bottom of screen), you have to edit files manually - which requires quite a bit of knowledge about the system - which likely you wont have. So its more of a fun/learning thing that a full blown thing. Its a very drag and drop type system, where you open two ROX (like file manager which is accessed by clicking the house like icon at the top left of the screen) windows and drag/drop files to move them around. More usually Linux uses single mouse click to start/load things, but in this version - at least within ROX, it uses the more common two-click choice.

Use at your own risk, make backup copies of your existing system beforehand ...blah blah.

And if you get a better version of jonojono spreadsheet working than my feeble effort then please do share.

PS : If you open HOME (house at top left of screen) and double click the 'get_latest_firefox' icon, that will grab a copy of the latest firefox directly from Mozilla (assuming of course you've set up networking and connected to the internet). Typically around a 30MB download, which for my connection takes around 10 seconds (wireless or modem connections would take longer). If you close that firefox session then to restart again you'll have to navigate to HOME folder firefox28.0/opt/firefox sub directory and double click on the "firefox" choice.

Think about that. If you can install a pristine fresh version of the operating system and desktop into memory, without even a HD being connected, and then download the latest version of firefox directly from Mozilla. And you only typed in your online banks URL to do some online banking, going nowhere else either before or after. And then shutdown and rebooted afterwards. And its all Open Source, so you could compile it all for yourself (and know that others have a close eye on exactly what things all do).

Factory fresh operating system, desktop and browser, nowhere else other than to your bank, all loaded into and run from memory (no HD connected) collectively is pretty secure.

Above all - have fun

Seasonal best wishes. Clive.
Call_Me_Op
Posts: 7983
Joined: Mon Sep 07, 2009 2:57 pm
Location: Milky Way

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

Post by Call_Me_Op »

I seem to be unable to download the Rev13 Back-testing spreadsheet. Anyone else have the same problem?

https://drive.google.com/file/d/0B8VSNc ... sp=sharing
Best regards, -Op | | "In the middle of difficulty lies opportunity." Einstein
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

It downloaded OK for me. I have cookies enabled for Google.

You'll get a "Spreadsheet is too large..." message. Click on the message's download link or use the File --> Download menu.
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
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

And your link is to the older 13a version . The latest 13c version is here:
http://www.bogleheads.org/forum/viewtop ... 7#p1912664
Always use the link on top of original Simba's post as starting to get to the latest version, even if through series of jumps through the thread.
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Or, use the wiki: Simba's backtesting spreadsheet
The latest version and download instructions are in this post.
A quick observation, probably just needs some clean-up.

In Portfolio sheet, cells F31:F39 are showing #N/A, which can be traced back to the Returns_72_13 sheet.

Compare to O43:O52, which don't have the #N/A error. I'm using LibreOffice Calc 4.2.2.1.
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
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

LadyGeek,
for version 13c of the spreasheet, opened in in Excel 2010, these cells do not contain anything including N/A error (well, invisible formulas are there)--neither Portfolio sheet, cells F31:F39 nor corresponding Returns_72_13 cells AD113-AL113.
The same as O44:O52

I do not have LibreOffice installed.
User avatar
LadyGeek
Site Admin
Posts: 66402
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

OK, I'll chalk it up to compatibility with the formatting somewhere. Checking your previous versions, I see the #N/A in rev13a.

In case anyone is concerned, nothing is wrong. LibreOffice Calc is displaying a blank cell (starting in the Reburns_72_13 sheet) as an error.

If I get some time, I'll see if I can track it down.
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.
Call_Me_Op
Posts: 7983
Joined: Mon Sep 07, 2009 2:57 pm
Location: Milky Way

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

Post by Call_Me_Op »

serbeer wrote:And your link is to the older 13a version . The latest 13c version is here:
http://www.bogleheads.org/forum/viewtop ... 7#p1912664
Always use the link on top of original Simba's post as starting to get to the latest version, even if through series of jumps through the thread.
Thanks. I managed to download it using the link on the left rather than at the top of the page. Great application!
Best regards, -Op | | "In the middle of difficulty lies opportunity." Einstein
PopOff
Posts: 17
Joined: Mon May 05, 2014 11:49 am

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

Post by PopOff »

Hi,

i have a question: Are all returns on the spreadsheet shown with gross dividends or net dividends? (Sorry my Englisch is not so good :wink: )

Bye
User avatar
serbeer
Posts: 1248
Joined: Fri Dec 28, 2007 2:09 pm

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

Post by serbeer »

I think that total return is used and it includes both capital appreciation and dividends.
PopOff
Posts: 17
Joined: Mon May 05, 2014 11:49 am

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

Post by PopOff »

Thanks for your feedback.

I have an another question:
I would like do download the return Data form the Emering Markets Index of this Homepage: http://www.ifa.com/disclosures/index-data/#EM
But i can't find there a link where i can download the Monthly returns :?:
Anybody know wehere i can download the Data from the Hompepage?

Thanks,
PopOff
User avatar
ThePrune
Posts: 959
Joined: Wed Nov 10, 2010 9:38 am
Location: Midland, MI

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

Post by ThePrune »

PopOff wrote:I would like do download the return Data form the Emering Markets Index of this Homepage: http://www.ifa.com/disclosures/index-data/#EM
But i can't find there a link where i can download the Monthly returns :?:
Anybody know wehere i can download the Data from the Hompepage?
This is the Dimensional Fund Advisors Emerging Market index. It was contructed by splicing together a number of different indices:
Time-Series Construction
January 1928 – December 1969: 50% IFA US Large Value Index and 50% IFA US Small Cap Index
January 1970 – December 1987: 50% IFA Int'l Value and 50% IFA Int'l Small Cap
January 1988 – December 1988: MSCI Emerging Markets Index (gross div.) minus 0.05%/mo (mutual fund exp ratio)
January 1989 – April 1994: Fama/French Emerging Markets Index minus 0.05%/mo (mutual fund exp ratio)
May 1994 – Present: DFA Emerging Markets Portfolio Symbol: DFEMX
You can download the monthly values for the May 1994 - Present time period from Yahoo Finance. Go to the DFA Emerging Markets I (DFEMX) historical data page. Make sure the time period runs all the way back to April 22, 1994 and that the data frequency is set to monthly. At the bottom of the page you'll see a link that will download all the data to a spreadsheet.

Now that you've seen how this is done, why don't you try searching the web for the other sections of the spliced Time Series?!
Investment skill is often just luck in sheep's clothing.
PopOff
Posts: 17
Joined: Mon May 05, 2014 11:49 am

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

Post by PopOff »

Thank you for the answer.
So the oldest Data for Emerging Markets Data is the MSCI Barra Data?
There is Data since 31. Dezember 1987 (Gross Dividends - http://www.msci.com/products/indexes/si ... mance.html)
Is there any possibility to convert the MSCI E.M - Gross Dividends- Index to a Net Index?
Because the MSCI E.M Net Dividends deliver Data only since 31.Dez.1998.

Is the DFA Emerging Markets Index comparable with the MSCI Emering Markets Index?
And is the DFA a union which deliver Data from an Index? Equal to the MSCI Barra?
Until know i dont't know about the DFA. (ps. i'm from Europe)
Post Reply