Spreadsheet for backtesting (includes TrevH's data)

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.

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

Postby Fear and Loathing » Fri Jan 06, 2012 8:41 pm

How do I add results for the missing years?
Fear and Loathing
 
Posts: 1025
Joined: Mon May 26, 2008 11:55 pm

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

Postby simba » Mon Jan 09, 2012 6:47 pm

Folks - Sorry but I have been hibernating from the forum ;)

I'll be posting an update with 2011 returns shortly.

Wishing you all a very happy new year.

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

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

Postby sschullo » Mon Jan 09, 2012 6:50 pm

Thanks Simba!
Steve
“It’s what you learn, after you know it all, that counts.” - John Wooden
sschullo
 
Posts: 1581
Joined: Sun Apr 01, 2007 9:25 am
Location: Rancho Mirage, CA

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

Postby wtoner » Fri Jan 13, 2012 11:04 am

At last, the triumphant return of Simba....
wtoner
 
Posts: 6
Joined: Mon Nov 29, 2010 1:07 pm

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

Postby bob90245 » Tue Jan 24, 2012 7:37 pm

Don't hibernate too long...
Ignore the market noise. Keep to your rebalancing schedule whether that is semi-annual, annual or trigger bands.
User avatar
bob90245
 
Posts: 6512
Joined: Mon Feb 19, 2007 9:51 pm

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

Postby simba » Thu Jan 26, 2012 5:43 pm

The latest version of the spreadsheet (rev11a) is now available.

You can download the latest version of Excel Spreadsheet [rev11a] or the LibreOffice version [rev11a]

The latest version will always be reflected in post #1 for this Topic - Spreadsheet for backtesting

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Re:

Postby simba » Thu Jan 26, 2012 6:05 pm

kyounge1956 wrote:Hi Simba,
thanks for all the work you do on these spreadsheets. I was looking at some sample asset allocations the other day and I think I've discovered a slight correction that needs to be made. On both of the "Returns" tabs, I don't think the conditional formatting has been applied to all the cells it should have. When I entered enter a 1972-2010 portfolio of 5% each Small-cap Value, REIT, EAFE & commodities, 40% 5-year Treasuries and 40% TIPS, the worst drawdown is -3.51% (in Column AM, line 43) but the orange cell is on line 29, which is the next worst return (+0.21%). I can't tell whether the Real Return columns are affected because the worst real return is up toward the top of the spreadsheet. Using the same AA and the 1985-2010 data, the worst return is -4.07% (Column AZ, line 30), but the orange cell is on line 16, the next worst return, and the same glitch occurs in the Real Return.

kyounge1956 - This has been fixed in the latest version rev11a.

Lbill wrote:Simba-
Just wanted to verify that the OpenOffice version 10c is not able to display the returns charts on the Porfolio tab, as they are in the Excel version, correct? Is this a limitation of OpenOffice?


The spreadsheet uses some special conditions/formula that LibreOffice/OpenOffice does not currently support. Since there is no easy workaround I removed the charts in the Portfolio tab. The charts in the other tabs are fine so I left them there.

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Re:

Postby simba » Thu Jan 26, 2012 6:17 pm

Clive wrote:Simba - great spreadsheet - love it :)

Query on the 5 year treasury VFITX for 1995 though that shows a 20.44% gain.

From http://www.federalreserve.gov/releases/h15/data.htm it looks like the 5 year T yields were

1994 6.69
1995 6.38
1996 6.18

So I can't see how a 20.44% gain in 1995 occurred.

Yahoo shows that value http://au.finance.yahoo.com/q/pm?s=VFITX - but just seems ???


Clive - for VFITX here's the split for 1995
Capital Return - 12.98%
Income Return - 7.46%
Total - 20.44%

I posted the historical returns for Vanguard and also capital-income returns for all of the Vanguard Funds in this post - Historical Returns of Vanguard Funds
I know it's time to update it but just wanted to confirm that the data in the spreadsheet is correct.

As staythecourse said - "Beware the Simba Risk" :)

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

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

Postby Lbill » Thu Jan 26, 2012 7:10 pm

Simba - Many, many thanks for your continuing efforts to provide the updated spreadsheet. This tool has been of enormous help to me in increasing my investment knowledge, as it has been to legions of other individuals. :beer
"Life can only be understood backward; but it must be lived forward." ~ Søren Kierkegaard | | "You can't connect the dots looking forward; but only by looking backwards." ~ Steve Jobs
User avatar
Lbill
 
Posts: 4997
Joined: Fri Mar 14, 2008 12:25 am
Location: Somewhere between Up and Down

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

Postby Clearly_Irrational » Thu Jan 26, 2012 7:21 pm

Lbill wrote:Simba - Many, many thanks for your continuing efforts to provide the updated spreadsheet. This tool has been of enormous help to me in increasing my investment knowledge, as it has been to legions of other individuals. :beer


Completely agree, though I was somewhat chagrined to only find it after having done a significant amount of work to build something similar myself. However since yours was far superior to the product I had and so many more people are checking it for errors I was happy to switch. Now everyone can be sure that when they accuse me of curve fitting that at least the curves are the right ones!

I just wish I could get the same thing with quarterly data, but I doubt that's easily available.
User avatar
Clearly_Irrational
 
Posts: 2604
Joined: Thu Oct 13, 2011 4:43 pm

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

Postby Clive » Thu Jan 26, 2012 7:23 pm

Thanks yet again Simba. Great stuff. :D

One modification that you might like to consider for later revisions is to support negative allocations i.e. for the 1972 onwards data changing

Returns_72_11 cells C4 to AL4

=IF(Portfolio!$B$58*B$3<>0,Portfolio!$B$58*B$3,"")

Cells B7to AL50

=IF(AND($A7>=SYear72,$A7<=EYear72,B$3<>0),B$3*VLOOKUP($A7,Data_72_11!$A$8:$AM$51,MATCH(B$2,Data_72_11!$B$2:$AM$2,0)+1),"")

That way you can somewhat (very generally) emulate historic leveraged allocations (ETF's).

Only as a very broad guideline I know, but say I'm looking at what a 2x leveraged 60% TSM and 2x leveraged 40% bond allocation might have looked like historically, then allocating +120% to TSM, +80% to Total Bond and -100% to T-Bill provides a feel for what such a leveraged portfolio might have achieved on the assumption that the cost of borrowing (to leverage) compared to T-Bill yields.

In reality leveraged ETF's don't track the non leveraged (underline) due to daily rebalancing creating more unique price motions, but as a better than nowt indicator that relatively small change is a nice additional feature to support IMO. The figures would be more indicative where funds might have been borrowed to increase asset exposure. The modification might also provide a feel for where assets might have been sold short (deleting the <1 or >1 check in Returns_72_11 cells AM4 ensures the CAGR is shown in Portfolio Cell C53).

Best regards. Clive.
Clive
 
Posts: 1153
Joined: Sat Jun 13, 2009 6:49 am

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

Postby edge » Thu Jan 26, 2012 8:18 pm

Thanks Simba.
edge
 
Posts: 1865
Joined: Mon Feb 19, 2007 8:44 pm
Location: Great Falls VA

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

Postby Cyclone » Fri Jan 27, 2012 12:54 pm

I love the spreadsheet, but I am using OpenOffice and the new versions (both) are not working for me. Nothing but errors. Am I doing something wrong?
Cyclone
 
Posts: 179
Joined: Sun Jan 10, 2010 10:48 pm

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

Postby simba » Fri Jan 27, 2012 4:43 pm

Cyclone wrote:I love the spreadsheet, but I am using OpenOffice and the new versions (both) are not working for me. Nothing but errors. Am I doing something wrong?


Please use libreoffice. I noticed openoffice had some issues but I saw none with the libreoffice [I am using the current stable version 3.4.5] [FYI - libreoffice is a spinoff from openoffice after Oracle/SUN decided to stop supporting openoffice and is actively being maintained]
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

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

Postby simba » Fri Jan 27, 2012 4:49 pm

Clearly_Irrational wrote:Completely agree, though I was somewhat chagrined to only find it after having done a significant amount of work to build something similar myself. However since yours was far superior to the product I had and so many more people are checking it for errors I was happy to switch. Now everyone can be sure that when they accuse me of curve fitting that at least the curves are the right ones!

I just wish I could get the same thing with quarterly data, but I doubt that's easily available.

Thanks CI. I don't mind including the quarterly data but it's just too much work to capture the data for all the funds. Even if I venture out on this path, I won't be able to get the quarterly data for the funds back to 1972 or 1985 for that matter.

I do appreciate the kind words and a BIG thank you to everyone who enjoys using this spreadsheet.

Just as an FYI - the revision numbers follow the last year of returns and then any updates made for that revision. rev11a is the first revision that includes returns for 2011. Any subsequent updates would be rev11b, rev11c etc.

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

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

Postby Clearly_Irrational » Fri Jan 27, 2012 4:53 pm

simba wrote:Thanks CI. I don't mind including the quarterly data but it's just too much work to capture the data for all the funds. Even if I venture out on this path, I won't be able to get the quarterly data for the funds back to 1972 or 1985 for that matter.


That's what I figured, I didn't find it easily myself either. There is probably a paid database (or two) somewhere that has it but I doubt it would change the results enough to be worth it.
User avatar
Clearly_Irrational
 
Posts: 2604
Joined: Thu Oct 13, 2011 4:43 pm

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

Postby Paul Douglas Boyer » Fri Jan 27, 2012 5:01 pm

Clearly_Irrational wrote:
simba wrote:Thanks CI. I don't mind including the quarterly data but it's just too much work to capture the data for all the funds. Even if I venture out on this path, I won't be able to get the quarterly data for the funds back to 1972 or 1985 for that matter.


That's what I figured, I didn't find it easily myself either. There is probably a paid database (or two) somewhere that has it but I doubt it would change the results enough to be worth it.


I did a spreadsheet with MONTHLY data about a year ago. It does not noticeably change the results at all. I also computed Max Drawdown, and determined that you can estimate Max Drawdown by multiplying the Standard Deviation by 3.

So no, it is not worth your time to go quarterly, it wasn't even worth it going monthly.
Paul Douglas Boyer, host, MadMoneyMachine.com
User avatar
Paul Douglas Boyer
 
Posts: 127
Joined: Wed Mar 07, 2007 4:19 pm
Location: Leesburg, VA

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

Postby simba » Fri Jan 27, 2012 5:38 pm

Paul Douglas Boyer wrote:I did a spreadsheet with MONTHLY data about a year ago. It does not noticeably change the results at all. I also computed Max Drawdown, and determined that you can estimate Max Drawdown by multiplying the Standard Deviation by 3.

So no, it is not worth your time to go quarterly, it wasn't even worth it going monthly.


Paul - Thanks for the info. Btw did you get the monthly data from M* principia pro or somewhere else?

For the heck of it I looked up the prices for M* Principia Pro. I am absolutely sure they have a typo for the the Principia Suite. It is listed at $3.345 instead of $3,345.

I should maybe call them to see if they'll honor the listed price :)

-Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

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

Postby stratton » Fri Jan 27, 2012 5:46 pm

You can get quarterly data off Yahoo Finance. Use funds that have existed for many years.

See Wellington's performance page.

With a little judicious digging we can probably get quarterly data without much work because it's right there for the taking. As oposed to doing it manually from Yahoo historical prices or moving a slider on Morningstar's web site.

Paul
Last edited by stratton on Fri Jan 27, 2012 5:48 pm, edited 1 time in total.
...and then Buffy staked Edward. The end.
User avatar
stratton
 
Posts: 10810
Joined: Sun Mar 04, 2007 6:05 pm
Location: Puget Sound

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

Postby Paul Douglas Boyer » Fri Jan 27, 2012 5:46 pm

simba wrote:Paul - Thanks for the info. Btw did you get the monthly data from M* principia pro or somewhere else?


I used the RCH_Stock_Market_Functions.xla Excel Add-In from
http://finance.groups.yahoo.com/group/smf_addin/
to bring in data into Excel from Yahoo! Finance Historical Quotes (Adjusted for splits and dividends).

(Oh and I built a spreadsheet to bring in annual data into your spreadsheet too. You should try it,
it might save you some time.)

poor man's principia

Paul
Paul Douglas Boyer, host, MadMoneyMachine.com
User avatar
Paul Douglas Boyer
 
Posts: 127
Joined: Wed Mar 07, 2007 4:19 pm
Location: Leesburg, VA

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

Postby pinebarrens1 » Fri Jan 27, 2012 7:31 pm

thanks Simba, this spreadsheet has been very helpful to me. I've spent endless hours using it:) One thing (thought it makes my portfolio look much better than it actually was.....the Vanguard Small Cap Intl VFSVX return was -19.09 for 2011, not -4.00%
pinebarrens1
 
Posts: 92
Joined: Sat Mar 05, 2011 9:08 pm

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

Postby sschullo » Sat Jan 28, 2012 12:16 am

One more thank you for your hard work Simba. I also noticed that when I inserted 2011 the output was "0" or "na" for the averages, SD and so on. But the other single years worked fine.
This is what I input:
Initial Investment 5000
Starting Year (>=1985) 2011
Ending Year (<= 2010) 2011
MAR Nominal 5%
Offset #N/A
# of Years of Backtesting 1

Output all the way across each portfolio:

Wellington
Average #DIV/0!
Std. Dev. #DIV/0!
Down SD #N/A
Up SD #N/A
CAGR #N/A
Sharpe #DIV/0!
Sortino #DIV/0!
US Mkt. Corr. #N/A
Intl. Corr. #N/A
Total - Rebalanced (N) #N/A
Total - Unbalanced (N) 0
Total - Rebalanced (Real) #N/A
$1 Portfolio = #N/A

If I input year 2010 or any other year, except 2011

I get this, which is expected:
Wellington
Average 10.94%
Std. Dev. #DIV/0!
Down SD 0.00%
Up SD #DIV/0!
CAGR 10.94%
Sharpe #DIV/0!
Sortino #DIV/0!
US Mkt. Corr. #DIV/0!
Intl. Corr. #DIV/0!
Total - Rebalanced (N) 5547
Total - Unbalanced (N) 5547
Total - Rebalanced (Real) 5465
$1 Portfolio = 1.11


Thanks again for your excellent work. I have been using it to see what I have had in my portfolio since 1994 had I used some of the lazy portfolios and compared to what actually happened. After the tech bubble disaster, I wasn't such a bad investor afterall.
Last edited by sschullo on Sat Jan 28, 2012 6:42 pm, edited 1 time in total.
“It’s what you learn, after you know it all, that counts.” - John Wooden
sschullo
 
Posts: 1581
Joined: Sun Apr 01, 2007 9:25 am
Location: Rancho Mirage, CA

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

Postby grok87 » Sat Jan 28, 2012 2:31 pm

simba wrote:The latest version of the spreadsheet (rev11a) is now available.

You can download the latest version of Excel Spreadsheet [rev11a] or the LibreOffice version [rev11a]

The latest version will always be reflected in post #1 for this Topic - Spreadsheet for backtesting

Best Regards,
Simba

Thanks Simba- great work as always!
cheers,
grok, CFA | Danon delenda est
grok87
 
Posts: 6492
Joined: Tue Feb 27, 2007 10:00 pm

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

Postby JasonF » Wed Feb 01, 2012 5:22 pm

Simba,

This is truly an outstanding piece of work, thank you for updating it.

Thanks! Jason
Last edited by JasonF on Fri Feb 03, 2012 7:57 pm, edited 1 time in total.
User avatar
JasonF
 
Posts: 51
Joined: Sun Jan 17, 2010 9:12 am

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

Postby Liquid » Thu Feb 02, 2012 11:59 am

Thanks Simba, this is awesome! :beer
User avatar
Liquid
 
Posts: 296
Joined: Sat Aug 06, 2011 11:17 am

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

Postby pcola2234 » Fri Feb 03, 2012 7:28 pm

Random question,

If a financial advisor used this in his or her practice instead of the software one has to pay thousands a year for, would you think that right or wrong?

Do you think that person should have to pay a fee to bogleheads or at least a donation?

just curious
pcola2234
 
Posts: 112
Joined: Tue Jul 12, 2011 12:24 am

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

Postby RaleighStClaire » Thu Feb 09, 2012 2:07 pm

Thanks for updating this stuff again simba, but I have found what seems like an error.

Under the compare portfolios tab, cell P77 does not update when you change the starting year in cell P59.
Where's that red one gonna go?
User avatar
RaleighStClaire
 
Posts: 731
Joined: Mon Jun 18, 2007 3:08 pm
Location: Tilting

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

Postby Clive » Fri Feb 10, 2012 3:51 pm

Worksheet Data_72_11 Cell R47 missing 2011 data for PCRIX (shown as -7.56% on the Data_85_11 worksheet)
Clive
 
Posts: 1153
Joined: Sat Jun 13, 2009 6:49 am

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

Postby roberts1001 » Mon Feb 13, 2012 2:33 pm

Thank you, Simba, for sharing your work so generously.
roberts1001
 
Posts: 1
Joined: Sat Oct 08, 2011 9:21 pm

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

Postby wtoner » Fri Feb 17, 2012 5:26 pm

Simba,

Thanks once again for this outstanding contribution.

Walter :D
wtoner
 
Posts: 6
Joined: Mon Nov 29, 2010 1:07 pm

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

Postby Annonymous » Sun Feb 26, 2012 12:04 pm

Just found this post/spreadsheet. All I can say is: WOW. Thx for your hard work.

dj
Annonymous
 
Posts: 2
Joined: Mon Jan 23, 2012 2:16 pm

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

Postby rntevans » Mon Mar 19, 2012 2:36 pm

Simba,

WOW! This is fantastic tool. Thanks for all your effort.

--Russ
rntevans
 
Posts: 2
Joined: Thu Jan 15, 2009 12:04 pm

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

Postby hudson4351 » Mon Mar 26, 2012 1:07 pm

I just checked this out for the first time today and am very impressed. Thanks for your contribution to the Bogleheads.

I just have one question: would it be possible to modify the spreadsheet to show actual after-tax historical returns, given the user's top marginal tax rate and LT capital gains rate?
hudson4351
 
Posts: 263
Joined: Fri Aug 10, 2007 9:30 pm

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

Postby hudson4351 » Thu Apr 12, 2012 4:43 pm

I just thought of another question:

Where did the backtested data come from in cases where the fund was not in existence for the entire duration? For example, there is data for VFITX going back to 1985, yet when I look that fund up on Vanguard, the inception date is only 10/28/1991.
hudson4351
 
Posts: 263
Joined: Fri Aug 10, 2007 9:30 pm

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

Postby Clearly_Irrational » Thu Apr 12, 2012 4:52 pm

hudson4351 wrote:I just thought of another question:

Where did the backtested data come from in cases where the fund was not in existence for the entire duration? For example, there is data for VFITX going back to 1985, yet when I look that fund up on Vanguard, the inception date is only 10/28/1991.



Look at the tab on the spreadsheet named "Data Sources".
User avatar
Clearly_Irrational
 
Posts: 2604
Joined: Thu Oct 13, 2011 4:43 pm

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

Postby Charybdis » Tue Jun 26, 2012 12:02 pm

Where could I find the international or global REIT performance, from 1972 to 2011? Is there such a thing exist? I don't know where to download the global or international REIT returns.
User avatar
Charybdis
 
Posts: 242
Joined: Sun Apr 25, 2010 5:38 am
Location: Budapest, Hungary

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

Postby vset » Wed Nov 14, 2012 5:23 pm

:) :) :)


thank you for sharing


:beer :beer :beer :beer
vset
 
Posts: 67
Joined: Sun Apr 22, 2012 5:55 pm

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

Postby CWRadio » Sat Nov 24, 2012 11:15 am

Is there a way to show a 4% a year withdrawal and how it effects the portfolios? Thanks
User avatar
CWRadio
 
Posts: 140
Joined: Sat Feb 02, 2008 11:04 am
Location: Michigan

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

Postby wtoner » Wed Jan 02, 2013 1:02 pm

Ah January....always an exciting time of year as we await new data for the beloved Simba spreadsheet.

Couple of questions for interested parties..

Is there any long term return data for prefererred stocks that could be included?

Has anyone ever seen an attempt to simulate TIPS back prior to 1972?



Happy and prosperous New Year to all Bogleheads! :sharebeer
wtoner
 
Posts: 6
Joined: Mon Nov 29, 2010 1:07 pm

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

Postby serbeer » Mon Jan 21, 2013 2:58 pm

Simba,
I realize that you rarely seen on the forum lately, but please consider updating the spreasheet with 2012 results and CPI data.

This is one of the more valuable tools of this forum!

Thank you!
User avatar
serbeer
 
Posts: 897
Joined: Fri Dec 28, 2007 3:09 pm

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

Postby sschullo » Mon Jan 21, 2013 4:30 pm

serbeer wrote:Simba,
I realize that you rarely seen on the forum lately, but please consider updating the spreasheet with 2012 results and CPI data.

This is one of the more valuable tools of this forum!

Thank you!

+1
“It’s what you learn, after you know it all, that counts.” - John Wooden
sschullo
 
Posts: 1581
Joined: Sun Apr 01, 2007 9:25 am
Location: Rancho Mirage, CA

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

Postby vset » Mon Jan 21, 2013 6:27 pm

+2
:D
:)
vset
 
Posts: 67
Joined: Sun Apr 22, 2012 5:55 pm

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

Postby Dr. Gaius Baltar » Mon Jan 21, 2013 6:49 pm

This spreadsheet is so amazingly helpful and interesting, I've grown so accustomed to having it around and use it very frequently. I would love to have one that's updated with 2012 data.
Dr. Gaius Baltar
 
Posts: 189
Joined: Sun Sep 04, 2011 4:39 pm

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

Postby gc527 » Tue Jan 22, 2013 12:31 am

+3

:happy
gc527
 
Posts: 16
Joined: Sat Feb 04, 2012 12:35 pm

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

Postby mtbomb » Wed Jan 23, 2013 4:10 am

I have updated this spreadsheet with the 2012 data. I am not simba, so there may be some errors. If you find any let me know and I'll make the changes until Simba updates the official one.

2012 backtest update XLS
You'll have to click download once you go to the google docs page.

Simba,
Hopefully this saves you some time from having to look up all the annual returns. I am not sure I did everything correctly, but I think it should be pretty close.



mtbomb
mtbomb
 
Posts: 3
Joined: Wed Jan 23, 2013 3:50 am

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

Postby serbeer » Wed Jan 23, 2013 3:13 pm

Thank you mtbomb.
Looks like Rolling returns on both Returns_72_11 and Returns_85_11 tabs are not extended properly for 2012?
User avatar
serbeer
 
Posts: 897
Joined: Fri Dec 28, 2007 3:09 pm

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

Postby wilson08 » Thu Jan 24, 2013 5:13 pm

I always look forward to Simba's update but thanks to you
mtbomb for an early preview. Looks like a great year all the
way around, only negative in the bunch was precious metals.

Wilson
wilson08
 
Posts: 221
Joined: Wed Apr 02, 2008 3:37 pm

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

Postby serbeer » Fri Feb 01, 2013 5:36 pm

I figured Simba may not be getting to it any time soon this year. So I went ahead and fixed formulas, rolling returns, headings, etc completing mtbomb's 2012 update, relying on the 2012 data he entered. I also looked into errors reported in this thread since 2011a update was released and fixed those as well.

Backtest-Portfolio-returns-rev12b.xls

Enjoy.

Note: There are still some problems that are not related to this update, but instead to original formulas used that may not fit all year periods. For example, if year range for 1985 backtest is set to 2011-2012, Sortino Ratio in the table errors out, because there are not enough error checks and conditionals around Sortino formula used. But I don't use Sortino and not familar with it at all, so cannot fix that.
User avatar
serbeer
 
Posts: 897
Joined: Fri Dec 28, 2007 3:09 pm

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

Postby Yipee-Ki-O » Mon Feb 04, 2013 10:33 pm

Anyone know if Simba will be updating his spreadsheet this year?
Yipee-Ki-O
 
Posts: 156
Joined: Sun May 24, 2009 1:42 pm

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

Postby gbronc » Tue Feb 05, 2013 7:13 pm

Thanks for the update, truly love this tool.

Did notice an error for the Average and Std Dev calculations on the Lazy_Portfolios_85 sheet. Rows 101 and 102 are not including 2011 and 2012 in the calculations. Should be =AVERAGE(C$116:C$143)
gbronc
 
Posts: 4
Joined: Wed Mar 07, 2012 4:24 pm

PreviousNext

Return to Investing - Theory, News & General

Who is online

Users browsing this forum: Beliavsky, bhsince87, CABob, chocolatemuffin, chrisgagner, enc0re, goggles, Kurmudjon, newtonc, psteinx and 72 guests