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 serbeer » Tue Feb 05, 2013 8:19 pm

Thanks gbronc, missed these references (and some other things on the same tab--did not pay much attention the tab since I don't look at lazy portfolios myself). This has been fixed in Backtest-Portfolio-returns-rev12c.xls

[Edit:]
This spreadsheet does not work properly in Google Spreadsheets, I use Google Drive for strorage only. If there is popup saying Google Error when you click on the link, just click on Reload button on the popup and it should load normally the second time. This can happen in some non-standard browsers I believe. If there are red error bar messages when image of spreadsheet loaded, ignore them since this not google spreadsheet but Excel spreadsheet, so it simply does not work properly within Google spreadsheets interface.

Instead, once Google Drive page with images of tabs of spreadsheet loads, you have to go to File menu and click on Download to get the entire spreasdheet in .xls format which you can open locally on your PC if you have MS Excel installed.
Last edited by serbeer on Wed Feb 06, 2013 2:33 pm, edited 3 times in total.
User avatar
serbeer
 
Posts: 869
Joined: Fri Dec 28, 2007 3:09 pm
Location: Chicago

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

Postby Browser » Tue Feb 05, 2013 10:28 pm

Yikes. Clicked on the link to download and it didn't work.
If we have data, let’s look at data. If all we have are opinions, let’s go with mine. – Jim Barksdale
Browser
 
Posts: 2726
Joined: Wed Sep 05, 2012 5:54 pm

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

Postby serbeer » Wed Feb 06, 2013 11:42 am

I double-checked and the link works. Once Google Drive page with images of tabs of spreadsheet loads, you have to go to File menu and click on Download to get the entire spreasheet in .xls format which you can open locally on your PC if you have MS Excel installed.

If there is popup saying Google Error when you click on the link, just click on Reload button on the popup and it should load normally the second time. This can happen in some non-standard browsers I believe.
User avatar
serbeer
 
Posts: 869
Joined: Fri Dec 28, 2007 3:09 pm
Location: Chicago

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

Postby Browser » Wed Feb 06, 2013 12:44 pm

serbeer wrote:I double-checked and the link works. Once Google Drive page with images of tabs of spreadsheet loads, you have to go to File menu and click on Download to get the entire spreasheet in .xls format which you can open locally on your PC if you have MS Excel installed.

If there is popup saying Google Error when you click on the link, just click on Reload button on the popup and it should load normally the second time. This can happen in some non-standard browsers I believe.

Thanks. That worked fine. When you click on the link, a red banner message appears at the top of the spreadsheet:

This spreadsheet is too large. Click to download or edit in Google Sheets.

When I clicked on download it, it fails. But following your instructions works fine.
If we have data, let’s look at data. If all we have are opinions, let’s go with mine. – Jim Barksdale
Browser
 
Posts: 2726
Joined: Wed Sep 05, 2012 5:54 pm

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

Postby Hayek314 » Tue Feb 26, 2013 6:30 pm

Dear all,

First post here. I really appreciate the Simba Excel, the work you put on it and your comments on the site.

However, I am looking for the same kind of Excel, but with MONTHLY returns instead of yearly. I would like to do more in deep research (monthly drawdowns, correlations, etc) and I need monthly returns for the main asset classes.
It is easy on internet, for example, to find historical monthly performance of the S&P500 Index since 1950 (Yahoo). But I can’t find monthly historical data since 1971 for long term treasuries bonds (20+) and short term treasuries bonds (1-3 years), in which I am especially interested.

Could someone help me on this? Thanks in advance,

Marcos.
Hayek314
 
Posts: 2
Joined: Tue Feb 26, 2013 6:15 pm

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

Postby Clive » Tue Feb 26, 2013 7:09 pm

and short term treasuries bonds (1-3 years)

Yahoo ^FVX provides 5 year treasury yield historic data back to 1962.

You might perhaps approximate a 2.5 year treasury bond fund simply by assuming a 5 year ladder i.e. average of the current and past four years 5-year-treasury yields.

Yahoo ^TYX provides historic 30 year yields back to 1977, but you'd have to approximate the total gain/losses using a bond price calculator such as http://www.fixedincomeinvestor.co.uk/x/yieldcalc.html

In the absence of anything better, you might use Robert Shiller's long interest rate (10 year) http://www.econ.yale.edu/~shiller/data/ie_data.xls for earlier years as an approximation for 20 year yields
Clive
 
Posts: 1065
Joined: Sat Jun 13, 2009 6:49 am

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

Postby Paul Douglas Boyer » Tue Feb 26, 2013 9:17 pm

Hayek314 wrote:Dear all,

However, I am looking for the same kind of Excel, but with MONTHLY returns instead of yearly. I would like to do more in deep research (monthly drawdowns, correlations, etc) and I need monthly returns for the main asset classes.

Marcos.


I modified it once for monthly returns in order to compute Max Drawdown more accurately.
But the bottom line for me was that monthly data did not change the relationship of risk and return
between the various portfolios. Annual data is easier and is just fine.
See this post:
http://madmoneymachine.com/2010/10/22/max-drawdown-in-lazy-portfolios-2001-2009/
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 Hayek314 » Wed Feb 27, 2013 9:26 am

Thanks Clive and Paul,

I have found that the Federeal Reserve of Saint Louis privedes with all the monthly rates data, but not historical prices:

http://research.stlouisfed.org/

As evolution of prices is what matters in a portfolio, I am still looking for treasuries bills and bonds prices monthly. The ideal answer would be the "monthly version" of:

http://pages.stern.nyu.edu/~adamodar/Ne ... stret.html

Also, reading some papers about porftolio construction, it seems that lots of people are using this book in order to get historical monthly prices evolution (Appendix B of the book):

http://corporate.morningstar.com/ib/asp ... e=1414.xml

But it is $175...

Is difficult to realize that something so essential in any study is something so difficult to find free on internet...
Hayek314
 
Posts: 2
Joined: Tue Feb 26, 2013 6:15 pm

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

Postby JamesSFO » Wed Feb 27, 2013 9:47 am

Hayek314 wrote:Also, reading some papers about porftolio construction, it seems that lots of people are using this book in order to get historical monthly prices evolution (Appendix B of the book):

http://corporate.morningstar.com/ib/asp ... e=1414.xml

But it is $175...

Is difficult to realize that something so essential in any study is something so difficult to find free on internet...


Ask your local library, they may be able to get it on inter-library loan...
User avatar
JamesSFO
 
Posts: 2009
Joined: Thu Apr 26, 2012 11:16 pm

Fund Data Correction (Spreadsheet for backtesting)

Postby jmk » Mon Mar 25, 2013 7:40 pm

1) I believe the returns on VFSVX are incorrect for the last couple years. Looking at Vanguard site I see: Returns 2010= 24.97, 2011= -19.09, 2012= 18.90.

2) I'm wondering how the CPI-U is calculated. When I get data at the FRED site for CPI-U:
CPI urban = (231.198 - 227.605) / 227.605) = 1.57%? This is going 1/1/12 to 1/1/13, i.e. the change from start of year to end of year. It's not the average rate, like I think is in the spreadsheet now. However, at least the several years before seem to be calculated end-of-year to start-of-year like I did.
Last edited by jmk on Fri Mar 29, 2013 12:43 pm, edited 1 time in total.
jmk
 
Posts: 73
Joined: Tue Nov 01, 2011 8:48 pm

Total Bond data wrong? (Spreadsheet for backtesting)

Postby jmk » Fri Mar 29, 2013 12:42 pm

Looking into the 1976-1986 data for the Total Bond Fund, I was curious at the discrepancy between the Lehman Aggregate Bond Index values cited by Taylor in http://www.bogleheads.org/forum/viewtopic.php?p=1650640#p1650640 and the entries in Simba's spreadsheet for same. (See below.)

One of them is wrong.

I'm wondering if Simba used the Lehman Intermediate Bond Index figures instead of the Lehman Aggregate Bond Index? If so, shouldn't we substitute the latter, since the Total Bond Fund is based on that? (Minus the ER of course.)

(Lehman's aggregate bond index didn't exist until 1986, so the ten years prior is synthesized by them..)

Code: Select all
LEHMAN BOND INDXs 1976-1987,
YR       SIMBA*    TAYLOR**
1976        11.7       15.6
1977       3.0          3
1978       2.2          1.4
1979       6.6          1.9
1980       6.6          2.7
1981      108           6.3
1982      25.4         32.6
1983      8.2           8.4
1984      14.3        15.2
1985      18.0         22.1
1986      13.1         15.2
1987      1.14          2.8

" Simba's spreadsheet VTBMX column, source: "Intermediate Term Bonds" "Lehman Brothers 1973-86"
** Taylor's post claiming to reflect: "Lehman Aggregate Bond Index"
jmk
 
Posts: 73
Joined: Tue Nov 01, 2011 8:48 pm

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

Postby roblanderson » Thu Apr 04, 2013 4:30 pm

I think there is an error in Backtest-Portfolio-returns-rev12c.xls for VFSVX. In the data tab for 2011 the value is -20.28, but according to Vanguard's website it should be -19.09.

The 2012 value also looks to be incorrect. The worksheet has 17.72, but Vanguard says 18.89.

https://personal.vanguard.com/us/funds/ ... INT#tab=1a
roblanderson
 
Posts: 14
Joined: Wed Mar 02, 2011 10:38 am

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

Postby JonoJono1 » Tue Jul 09, 2013 10:02 pm

Serbeer: Per your request, here are the two issues to Simba's Backtester (v12c) I found:


1. Excel Worksheet (EWS) "Portfolio_72_12, Cell J127" is "=10000". But, what "Portfolio_72_12, Cell J127" should be is "=Portfolio$B$58". (I think the reason for this is obvious. But to forego a longer explanation, simply compare Cell L127, and this change should be readily apparent. If you require an explanation from me, I will be happy to give a long winded one.)

2. Excel Worksheet (EWS) "Portfolio_85_12, Cell J104" is "=Portfolio$B$58". But, what "Portfolio_85_12, Cell J104" should be is "=Portfolio$K$58". (I think the reason for this is obvious. But to forego a longer explanation, simply compare Cell L104, and this change should be readily apparent. If you require an explanation from me, I will be happy to give a long winded one.)

Please reply to this post if you agree or disagree so I can feel more secure making the changes to my personal copy of the Excel file.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby Bob » Wed Jul 10, 2013 12:05 pm

Could someone please post the link to the latest Simba Backtest Excel model: version 12c. Thanks you very much :D
Bob
 
Posts: 98
Joined: Mon Feb 26, 2007 6:15 pm

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

Postby serbeer » Wed Jul 10, 2013 1:34 pm

Thank you for catching the problems JonoJono1. I posted new revision fixing that and other issues reported to date:

****************************************************************************************************************************************
From revision history tab:

Rev12e
1. In WS "Compare_Portfolios," the formulas for "OFFSET" [Row 62] references an incomplete array of data--fixed

Rev12d
Serbeer fixed several problem discovered by 12b users, all of them inhereted from original/previous versions of spreasheet:
1. "Portfolio_72_12, Cell J127" should be "=Portfolio$B$58", "Portfolio_85_12, Cell J104" should be is "=Portfolio$K$58" (thanks JonoJono1)
2. For VFSVX, for 2011 the value was -20.28, per Vanguard's website it should be -19.09, the 2012 value had 17.72, but Vanguard says 18.89
(thanks jmk and roblanderson)


Backtest-Portfolio-returns-rev12e.xls
****************************************************************************************************************************************
Reminder: Once Google Drive page with images of tabs of spreadsheet loads, you have to go to File menu and click on Download to get the entire spreasdheet in .xls format which you can open locally on your PC if you have MS Excel installed. I use Google Drive for storage only, this is not Google spreadsheet, this is Excel spreadsheet, so ignore all errors, and click Reload button in pop-up message should it appear (can happen in some browsers).
Last edited by serbeer on Tue Jul 16, 2013 11:19 am, edited 1 time in total.
User avatar
serbeer
 
Posts: 869
Joined: Fri Dec 28, 2007 3:09 pm
Location: Chicago

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

Postby czeckers » Wed Jul 10, 2013 1:53 pm

A big THANK YOU to Serbeer and all who have helped to update this spreadsheet. I know I'm not alone in saying that I have found this to be a very valuable tool.

-K
The Espresso portfolio: | | 16% LCV, 16% SCV, 16% EM, 8% Int'l Value, 8% Int'l Sm, 8% US REIT, 8% Int'l REIT, 20% Inter-term US Treas | | "A journey of a thousand miles begins with a single step."
User avatar
czeckers
 
Posts: 637
Joined: Thu May 17, 2007 4:49 pm
Location: Upstate NY

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

Postby Bob » Wed Jul 10, 2013 2:43 pm

Many many thanks for the link and for the amazingly great work you do. :thumbsup
Bob
 
Posts: 98
Joined: Mon Feb 26, 2007 6:15 pm

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

Postby JonoJono1 » Wed Jul 10, 2013 8:23 pm

Excellent! Thanks Serbeer.

Ok. All, I have searched this thread and the board for and cannot find how to run a portfolio optimizer using Simba's backtester. I found some stuff on the web that while precise, still confused me.

I would like to optimize a portfolio. I have added in the Excel function SOLVER, which I understand is the right function to use; I am just having trouble with putting in the correct inputs/constraints/target.

Can someone, anyone, walk us through how to use SOLVER (or any other similar approach) to optimize a portfolio using the backtester?

Or, if this wheel has already been invented and posted, link me to it?
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby M1garand30064 » Thu Jul 11, 2013 3:53 pm

This spreadsheet is incredible. Thank you everyone for putting it together!

I'm afraid I have a dumb question. Do the returns reflect dividends and dividend reinvestment, or are they without dividends? Thanks! :sharebeer
M1garand30064
 
Posts: 69
Joined: Tue Sep 04, 2012 9:49 pm

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

Postby sschullo » Thu Jul 11, 2013 4:35 pm

czeckers wrote:A big THANK YOU to Serbeer and all who have helped to update this spreadsheet. I know I'm not alone in saying that I have found this to be a very valuable tool.

-K

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

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

Postby JonoJono1 » Fri Jul 12, 2013 1:36 am

Serbeer:

You requested that suggested changes to Simba's Backtester be placed here in this thread, and you were efficient at updating ver. 12c to 12d (thank you), so I am posting another bug in the backtester, which I just discovered and spent a while debugging.

Here goes:

Since the backtester gets updated each year, it is important to update all of the formulas that depend on ranges of data. My suggestion is that someone keep a list of all changes that need to be made to the formulas in the backtester when updater updates with each new year's data.

Here is the bug I found:

In WS "Compare_Portfolios," the formulas for "OFFSET" [Row 62] references an incomplete array of data.

C62 reads: =MATCH(C59,$B$105:$B$143)-1
ButC62 should read: =MATCH(C59,$B$105:$B$148)-1

This change will make the backtester up to date through year 2015. After that, when additional years are added, this OFFSET row should also be updated.

Like wise:

L62 reads: =MATCH(L$59,$K118:$K143)-1
But L62 should read: =MATCH(L$59,$K118:$K148)-1

This change will also make the backtester up to date through year 2015. After that, when additional years are added, this OFFSET row should also be updated.

Important: Once the above two changes are made, be sure to copy column C into columns D through G. Likewise, be sure to copy column L into columns M through P.

Ok. I believe the above is accurate and clear. But I have no pride; so if I am wrong, please say. If I need to be more clear, please say and I will do my best to be longer winded and clearer.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby serbeer » Fri Jul 12, 2013 1:10 pm

Yes, another good catch JonoJono1. That affects comparison tab--effectively not taking into account 2011 and 2012 while calculating summary statistics for 5 portfolios being compared
Btw, in the formula below, $B$148 should actually be $B148, precisely to avoid having to do similar changes in the future if new years are inserted.

I'll wait to see if you discover more bugs before posting 12e update (say in about a month). The truth is, most bugs are found once someone uses this spreadsheet actively, like you do now. And most of the errors I've seen are due to extending the spreadsheet for later years after reaching 2010 that original spreadsheet supported.
User avatar
serbeer
 
Posts: 869
Joined: Fri Dec 28, 2007 3:09 pm
Location: Chicago

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

Postby czeckers » Fri Jul 12, 2013 1:57 pm

I noticed that version 12d doesn't have the ability to compare multiple portfolios simultaneously. The 11 version I had (don't remember which off hand) was able to compare 5 portfolios in parallel and even had a nice graph of return vs SD where all portfolios were plotted. I'm not sure why this got dropped, but I did find it helpful. Would love to see it back.

-K
The Espresso portfolio: | | 16% LCV, 16% SCV, 16% EM, 8% Int'l Value, 8% Int'l Sm, 8% US REIT, 8% Int'l REIT, 20% Inter-term US Treas | | "A journey of a thousand miles begins with a single step."
User avatar
czeckers
 
Posts: 637
Joined: Thu May 17, 2007 4:49 pm
Location: Upstate NY

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

Postby serbeer » Fri Jul 12, 2013 2:07 pm

czeckers wrote:I noticed that version 12d doesn't have the ability to compare multiple portfolios simultaneously. The 11 version I had (don't remember which off hand) was able to compare 5 portfolios in parallel and even had a nice graph of return vs SD where all portfolios were plotted. I'm not sure why this got dropped, but I did find it helpful. Would love to see it back.

-K

Still there, just switch to the next tab to find it :)
User avatar
serbeer
 
Posts: 869
Joined: Fri Dec 28, 2007 3:09 pm
Location: Chicago

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

Postby JonoJono1 » Fri Jul 12, 2013 2:40 pm

Serbeer:

Good plan on waiting for 12e. I feel bad for having not caught this before 12d got published.

Btw, in the formula below, $B$148 should actually be $B148, precisely to avoid having to do similar changes in the future if new years are inserted.


To be pedantic, because this cell is to be copied across a row and not down a column, it is irrelevant whether there is a $ before the 148. You will never want to copy this cell down a column, so the 148 will never change when copying across the row. In future years, beyond 2015, this cell will need to be manually updated (i.e. to increase 148 by however many extra years get added) to include all of the new years put into the backtester. I am clarifying this not for the trivial aspects of pedantry but so that the due diligence items for how to update the backtester is written correctly. Otherwise, a future updater may miss updating this row and this bug will creep back in.

Again, thanks for the prompt replies! You are making my active work with the backtester a better experience.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby serbeer » Fri Jul 12, 2013 4:33 pm

The proper way to extend for future years past 2015 is to insert new year rows between row 147 and 148 and relabel years column in rows 148+. And I guess both $B148 and $B$148 should get incremented automatically in the formula, so yes, probably does not make any difference.
User avatar
serbeer
 
Posts: 869
Joined: Fri Dec 28, 2007 3:09 pm
Location: Chicago

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

Postby Bongleur » Fri Jul 12, 2013 7:24 pm

serbeer wrote:The proper way to extend for future years past 2015 is to insert new year rows between row 147 and 148 and relabel years column in rows 148+. And I guess both $B148 and $B$148 should get incremented automatically in the formula, so yes, probably does not make any difference.


Maybe you could add an empty column/row wherever new ones will need to be inserted, and label it "Insert newest year here see Note x " or some such descriptive instruction.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
 
Posts: 2024
Joined: Fri Dec 03, 2010 11:36 am

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

Postby JonoJono1 » Fri Jul 12, 2013 9:31 pm

Serbeer:

Ok. I found a few more bugs. (I'll report them after your response. See below.)

But first, I do not want to bog this thread down with tedium. So, I thought it may be beneficial to the members if we started a new thread called: "Revisions, Improvements, Updates, Bugs and Fixes for Simba's Backtester" for us all to chime in on improvements. My sense is that these bugs and fixes can be quite tedious and methodical and probably not well suited to most of our fellow members purpose in searching the threads. It could also be used for members to discuss various improvements.

When I get done working through the formulas, I would like to discuss the data series' to determine if we have the optimal data for each asset class. I noticed that some assumptions had to be made in making the backtester. (If a mistake is made here, it could be quite harrowing to find out later.) And then, I want to spend my nights and weekends getting data chains back to 1926 if this can be done reasonably well. I have already updated the portfolio comparison chart to include all 25 portfolios (instead of 5) PLUS I added a test column to compare a portfolio-in-design against the set of 25 portfolios. So, if you think the above needs to be in this thread or in a new thread, let me know.

I am new here. So, please understand I am just surfacing a thought. I'll start the new thread if and only if you tell me it is a good idea.

What say you?
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby LadyGeek » Fri Jul 12, 2013 10:00 pm

Good idea (and welcome to the forum), but I'd say to keep the posts here. It's more important to keep the information all in one spot, as most of the members will probably end up reading both threads. What you're missing is that this forum has a large contingent of members who work with spreadsheets on a daily basis (like me) and have an interest to help collaborate on this effort.

This spreadsheet is also in the wiki, which points back to this thread: Simba's backtesting spreadsheets (Any suggestions for content would be appreciated.)

If you want to separate the spreadsheet technicalities from the users, just start each post with a tag, like **Proposed Update** so that members will know if it's worthwhile to read the post. Remember that each thread has a "Search this topic..." box at the top of the thread display area. You can search the thread for the tag and keep track that way.

serbeer - If you wish to maintain the spreadsheet (and Simba is not around), would you like to keep this post updated with the latest version? I can edit Simba's post #1 and point the members to it. When Simba returns, you and he can work it out.
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: 18319
Joined: Sat Dec 20, 2008 6:34 pm
Location: Philadelphia

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

Postby JonoJono1 » Sat Jul 13, 2013 12:46 am

LadyGeek:

Got it. Thanks for the reply.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby Cyclone » Sat Jul 13, 2013 1:44 pm

I am hoping that somebody with ambition can update the LibreOffice version. I love the spreadsheet but I don't have the skills to update it.
Cyclone
 
Posts: 176
Joined: Sun Jan 10, 2010 10:48 pm

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

Postby Robert The Bruce » Sat Jul 13, 2013 6:12 pm

Man!
The coding in this spd sht is incredible! Amazing! Thanks to all who have had a hand in its creation. I use a lot of the functions embedded within, but not to the sophistication that you have used them. I bow to your awesomeness!!
The stone age didn't end for lack of stone.
User avatar
Robert The Bruce
 
Posts: 66
Joined: Fri Mar 16, 2007 8:49 pm

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

Postby Clive » Sat Jul 13, 2013 6:25 pm

Robert The Bruce wrote:Man!
The coding in this spd sht is incredible! Amazing! Thanks to all who have had a hand in its creation. I use a lot of the functions embedded within, but not to the sophistication that you have used them. I bow to your awesomeness!!

You might like to have a look at Peter's (gummy) now defunct website http://www.financialwebring.org/gummy-s ... _stuff.htm

Amongst other incredible spreadsheets It includes a reference to Simba's spreadsheet http://www.financialwebring.org/gummy-stuff/Simba.htm
Clive
 
Posts: 1065
Joined: Sat Jun 13, 2009 6:49 am

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

Postby serbeer » Mon Jul 15, 2013 11:35 am

LadyGeek wrote:serbeer - If you wish to maintain the spreadsheet (and Simba is not around), would you like to keep this post updated with the latest version? I can edit Simba's post #1 and point the members to it. When Simba returns, you and he can work it out.


Sure LadyGeek, I agree that any future revisions I make should be anchored to the same spot, similarly to the original post of Simba, and that post on page 10 of the thread is as good of a place as any.

That said, I must confess that I don't see much point in being a middleman between JonoJono1 and Google Drive. I was happy to step up to the plate when I seen no one else working on it in 2013, and I don't mind continuing to incorporate bug fixes into the spreadsheet when users without good Excel skills notice problems and post about them on the forum, but I cannot put any significant time into enhancing the spreadsheet otherwise (actually, myself, I don't use even 50% of the features there already). Since JonoJono1 clearly has both skills and interest in active development of the spreadsheet, I am not sure how I add value by re-typing the formulas he posts. Actually, it is double work for him to both make changes and posts details of them on the forum for me to copy and paste.

So, JonoJono1, what do you think about posting enhancements you have referred to in your previous post on Google Drive (or any other sharing site) direclty, providing a link to it in one of your future posts, and updating this post whenever you make the changes? I (and probably many other people on this forum), really appreciate your interest in this spreadsheet, we really needed someone like you after Simba left the scene, I just don't want to be caught in the middle of active development between you and Google Drive, and I think it will be much less work for you too when you don't have to explain details of changes for specific cells (just provide overview of the changes in History within spreasheet).
User avatar
serbeer
 
Posts: 869
Joined: Fri Dec 28, 2007 3:09 pm
Location: Chicago

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

Postby JonoJono1 » Mon Jul 15, 2013 10:15 pm

Serbeer, LadyGeek:

I am happy to help out in the way Serbeer is suggesting. I grew up (engineering school, grad school etc.) on Excel, but I am by no means an expert compared to some others that are members among us. I did go through about 1/3rd of the 50 or so array references in Simba's work so far to verify their accuracy and am learning new things. My interest stems from having decided 2 weeks ago to build the same backtester from scratch and then found Simba's work. His work saved me about 500 hours or so to build the foundation. I am saying this to say that although I have an interest and some skills, I am sure there may be others that are better suited to be the file's custodian. That said, if I fit the bill, I am more than happy to be the custodian and hope I can make a meaningful contribution to the forum.

I am toying with several ideas here, and this weekend have expanded the portfolio to simultaneously compare 26 portfolios (25 that are pre-set from Simba) and then a comparison portfolio. What I have found is intriguing, which I will post for discussion. I am honing some models and theories that I think others will find fascinating. For my personal copy I have built macros that allow us to see some things that do not appear naturally in Excel. I say all of this to say that I am excited about what this spreadsheet could become. That is, it could re-enforce the Bogelhead philosophy and provide structure from noobs to experts. I may be a bit ambitious with what can happen, but it is fascinating.

I am game for whatever is best for the group.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby LadyGeek » Mon Jul 15, 2013 11:57 pm

JonoJono1,

For some perspective, I became a wiki editor about 1 month after I joined the forum. Why? The best way to learn something is to try and teach it. I barely knew what a target retirement fund was, but I have a strong engineering background and the wiki needed help with its user interface. The members got my portfolio straightened out, and I thought this was a good way to return the favor. In parallel, I started helping with the investing content by fixing typos. Then, I eventually progressed to write complete articles.

I needed help to write those articles. So, I posted threads asking for help and received the feedback I needed (corrections and expert insight). The knowledge I learned is now passed along to the members through the wiki.

You're essentially following this path, but focused on backtesting. My point is that you don't have to worry about not being an expert. All you need to do is ask.

From your description and thoughts for improvement, it sounds like you might want to create a fork off the main development path (software speak). Rather than try to fit within the confines of this thread, why don't you start a new thread and make Post #1 your launch point, i.e. downloads and revision tracking is maintained here. We can point to your new thread from this one. If you have trouble thinking of a new title, perhaps "Spreadsheet for backtesting, revised" might work. A short intro "this is an update to Simba's thread..." would help.

Don't worry about posting a finished product, debug versions are fine. The whole point is to collaborate.
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: 18319
Joined: Sat Dec 20, 2008 6:34 pm
Location: Philadelphia

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

Postby JonoJono1 » Tue Jul 16, 2013 1:25 am

LadyGeek,

I appreciate your commentary. I intentionally want to contribute, and this could be a good way.

Ok. I will start a new thread. But isn't what you are proposing I do exactly what you said I should not do just a few posts ago (see your post: by LadyGeek » Fri Jul 12, 2013 9:00 pm)?

I am asking this question not to point this out but simply because I am concerned that I may misunderstand what you are saying now.

For convenience, here was what I said earlier:

"But first, I do not want to bog this thread down with tedium. So, I thought it may be beneficial to the members if we started a new thread called: "Revisions, Improvements, Updates, Bugs and Fixes for Simba's Backtester" for us all to chime in on improvements. My sense is that these bugs and fixes can be quite tedious and methodical and probably not well suited to most of our fellow members purpose in searching the threads. It could also be used for members to discuss various improvements."

Is the new thread you are suggesting I start different from what I was suggesting?

(Again, I am only clarifying this so I do everything correctly.)

Second, a fork is exactly what I have in mind.


"When you come to a fork in the road, take it" -Yogi Berra.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby serbeer » Tue Jul 16, 2013 11:08 am

JonoJono1,
that sounds very interesting, I am looking forward to hearing about results of your research and seeing the changes you have made.

As far as starting separate thread goes, it is obviously became merited based your latest postings, the scope of changes you are proposing, and amount of effort you you appear to be willing to put in (while normally admins prefer to keep a topic within original thread--to keep discussion serialized and spare interested people the need to track multiple threads at the same time).

I am posting update 12e that I was holding out on, with the formula fixes you provided, to agreed upon location in the thread above. Since we seem to be in agreement about it, please post any further changes directly. Thank you!
User avatar
serbeer
 
Posts: 869
Joined: Fri Dec 28, 2007 3:09 pm
Location: Chicago

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

Postby LadyGeek » Tue Jul 16, 2013 5:25 pm

First, I updated Post #1 to point to Serbeer's post here.

JonoJono1 - Yes. With your thoughts on what you want to do (create the fork), the approach changed. Rather than keep a separate maintenance thread, start a new one completely from scratch. Post #1 will contain the release details and revision history, just as it's done here.

When you're ready, I'll redirect Post #1 (again) to the new thread and update the wiki (Simba's backtesting spreadsheets).
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: 18319
Joined: Sat Dec 20, 2008 6:34 pm
Location: Philadelphia

JonoJono1: Improve, Revise, Update, Debug Simba's Backtester

Postby JonoJono1 » Fri Jul 19, 2013 2:05 am

[Thread merged into here Jan 06, 2014. See this post. --admin LadyGeek]

All,

The mission of this thread is to have a dedicated thread where we can improve Simba's backtester.

Our goal is to achieve a backtester that is 1) accurate 2) user friendly and 3) functional for Bogleheaders.

In the past, updates to Simba's backtester have suffered from a lack of organization fluidity in that updates or suggested improvements were weaved into other threads. The benefit of this thread is to have one place where we can collaborate specifically on backtester improvement.

Backtester improvement encompasses bugs, improved formulas, functional updates, better graphs and user interfaces, additional conceptual studies, and yearly data return updates.

This thread is explicitly not limited to technical Excel programming. In order to create an optimal backtester, discussion of Boglehead investment principles and proper incorporation thereof is expressly encouraged so that our backtester accurately reflects our investment principles.

-JonoJono1

LINK TO THE CURRENT BACKTESTER VERSION IS FOUND HERE: https://docs.google.com/file/d/0B6r-HIC ... sp=sharing
Last edited by JonoJono1 on Fri Jul 19, 2013 11:31 pm, edited 4 times in total.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby JonoJono1 » Fri Jul 19, 2013 2:07 am

LadyGeek:

Ready or not, here we go!

[Thread merged into here, see above. --admin LadyGeek]

Thanks for the encouragement.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby JonoJono1 » Fri Jul 19, 2013 2:23 am

What is the purpose of a portfolio backtester?

I read on this board that a backtester "has its limits," and "should be taken with a grain of salt." Ok. What does this mean? What are the limits of a backtester?

Here is where I am coming from. The typical user wants to find the best portfolio for him/her. The backtester can help achieve this goal. But can someone articulate how far one should take it?

My question is more than just "why not use an optimizer and invest." My question is how do we properly instruct a noob how best to employ the backtester and stay safe from its pitfalls.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby serbeer » Fri Jul 19, 2013 10:45 am

Glad to see this thread opened JonoJono1!
I suggest you reserve a place in the original post for posting spreadsheet revisions--similarly to how it was done in Simba's thread, so that they are not spread out through (hopefully) pages of discussions.
Looking forward seeing the changes you made so far.
User avatar
serbeer
 
Posts: 869
Joined: Fri Dec 28, 2007 3:09 pm
Location: Chicago

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

Postby LadyGeek » Fri Jul 19, 2013 11:04 am

Same here. We also have a wiki page that can be used to document the details (overview, theory, how to use, etc.): Simba's backtesting spreadsheets

Here's the original thread: Spreadsheet for backtesting (includes TrevH's data)
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: 18319
Joined: Sat Dec 20, 2008 6:34 pm
Location: Philadelphia

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

Postby Oicuryy » Fri Jul 19, 2013 11:27 am

It's been years since I looked at that spreadsheet. It sure has changed.

In a thread on an early version I suggested it needed a gummy Magic Sum (gMS) function.
viewtopic.php?p=36154#p36154

This post by grayfox describes how gMS can be useful to backtest withdrawal rates.
viewtopic.php?p=858131#p858131

Ron
Money is fungible | Abbreviations and Acronyms
User avatar
Oicuryy
 
Posts: 856
Joined: Thu Feb 22, 2007 11:29 pm

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

Postby JonoJono1 » Fri Jul 19, 2013 10:50 pm

1. The link to the current and past versions of the backtester and revision history is posted on the first entry of this thread.

2. Adding withdrawal rate calculations will be an enormous benefit. Excellent idea!

By the way, I am keeping a list of ideas (e.g. like this withdrawal rate idea) offline (and on the spreadsheet itself) and organizing them so we come up with a good list of things that need to be tackled. Once I have a good working list, we can order up each improvement by priority. Just know, I am keeping the list so none of your comments are being lost.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

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

Postby Bongleur » Sat Jul 20, 2013 7:26 pm

>If you want to separate the spreadsheet technicalities from the users, just start each post with a tag, like **Proposed Update** so that members will know if it's worthwhile to read the post.
>

Except that when you get an email notification of a new post, you MUST actually click on the URL and go to the forum in order to keep receiving the email updates. So "users" would spend time for nothing.

Maybe create a wiki page that points to the location of all the updated versions that various techies are creating & collaborating on? All techies would be given access to the page to update their comments about their latest version & enter links to new revisions, and anyone could download.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
 
Posts: 2024
Joined: Fri Dec 03, 2010 11:36 am

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

Postby rlaustin » Mon Jul 22, 2013 12:53 pm

JonoJono1,
Thanks for doing this. It is greatly appreciated.
rlaustin
 
Posts: 3
Joined: Mon Jul 22, 2013 12:45 pm

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

Postby LadyGeek » Mon Jul 22, 2013 4:04 pm

As promised, I have edited Simba's Post #1 to point to this thread: Spreadsheet for backtesting (includes TrevH's data).

I also updated the wiki: Simba's backtesting spreadsheets (which would be a good place to document the spreadsheet - overview, how to use, etc.)
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: 18319
Joined: Sat Dec 20, 2008 6:34 pm
Location: Philadelphia

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

Postby BlueEars » Tue Jul 23, 2013 7:40 pm

revised comments:
I've revised these comments because I actually bothered this time to scroll the "Portfolio" sheet and noticed some graphs I had not seen before. My use of the Simba file has been focused mainly on the "Data_72_11" which provides some interesting perspective.

It would be nice to provide some data and charts for maybe 1920 to 1972. To do this one could generate them using the data from the French-Fama web site. The charts could be driven by variables smb and hml. Then some current sbm/hml figures could be provided for typical current index offerings from Vanguard. I've actually done this for myself a bit. I found that the SP500 was pretty nicely reproduced when compared with some actual SP500 data on a monthly basis but not for a typical index offering like small cap value or large cap value. However, some reasonable fit can be had I think. Of course, this is just synthesized stuff and maybe the idea of using ancient data like in the 1930's is not relevant to today's markets.

I'll be interested to see what comes of this. :happy
Last edited by BlueEars on Thu Jul 25, 2013 10:30 pm, edited 1 time in total.
User avatar
BlueEars
 
Posts: 3148
Joined: Sat Mar 10, 2007 1:15 am
Location: West Coast

PreviousNext

Return to Investing - Theory, News & General

Who is online

Users browsing this forum: Bing [Bot], cjking, dschoenleb, Workinghard, Yahoo [Bot] and 38 guests