Simba's backtesting spreadsheet [a Bogleheads community project]
Re: Spreadsheet for backtesting (includes TrevH's data)
kcfin has a comprehensive case study for a UK portfolio here: Re: Some discussion from the UK [Retirement withdrawal strat
UK data needed.
After a few weeks of reading and thinking and pouring over spreadsheets, I made stab at rebalancing.
I have been a believer in the use of Gold to combat all manner of issues that happen such as devaluation of currency, inflation and general malaise in the financial world.
I backtested 5 portfolios to test my allocation using rev13c of the backtest spreadsheet over both time ranges 1972-2013 (T1) and 1985-2013 (T2). The gain and risk are shown for each.
p1 was 50% Total US, 30% Total Int, 20% Total Bond.
p2 was 50% Total Int, 50% Total Bond
p3 was 50% Total Int, 50% LTGB
p4 was 31% Total Int, 19% LTGB, 19% T Bill Money Market, 31% Gold
p5 was 25% Total Int, 25% LTGB, 25% T Bill Money Market, 25% Gold
1972-2013 (T1)
P1 P2 P3 P4 P5
Average 11.36% 10.21% 10.68% 9.99% 9.38%
Std. Dev. 14.78% 11.76% 12.30% 11.16% 9.13%
Down SD 8.70% 5.75% 5.16% 4.50% 3.58%
Up SD 7.62% 8.22% 9.34% 8.65% 7.15%
CAGR 10.31% 9.59% 10.03% 9.46% 9.02%
Sharpe 0.42 0.43 0.45 0.43 0.46
Sortino 0.73 0.91 1.10 1.11 1.22
US Mkt. Corr. 0.94 0.69 0.61 0.21 0.21
Intl. Corr. 0.87 0.97 0.88 0.58 0.56
Total - Rebalanced (N) 615266 467860 554114 444586 375384
Total-Unbalanced (N) 547241 424603 443704 333520 299929
Total - Rebalanced (Real) 108509 82513 97724 78408 66203
1985-2013 (T2).
P1 P2 P3 P4 P5
Average 11.73% 10.21% 11.36% 8.34% 8.01%
Std. Dev. 15.14% 12.79% 13.15% 10.08% 8.47%
Down SD 8.77% 6.13% 4.89% 4.85% 3.94%
Up SD 7.59% 9.13% 10.72% 7.19% 6.25%
CAGR 10.63% 9.49% 10.65% 7.89% 7.70%
Sharpe 0.52 0.50 0.57 0.44 0.49
Sortino 0.77 0.85 1.30 0.69 0.76
US Mkt. Corr. 0.93 0.69 0.57 0.37 0.34
Intl. Corr. 0.89 0.98 0.89 0.86 0.81
Total - Rebalanced 187052 138472 188011 90476 85852
Total - Unbalanced 173893 126771 148692 94739 90396
Total - Rebalanced (Real) 84519 62568 84952 40881 38792
My conclusion from the above is that the P4 portfolio offers more gain than P5 with little difference in risk, but relative to P2 offers the same gain and risk during T1 but 3% less risk with 2% less return over T2.
2008 P1 = -30.74% p2 = -19.53% p3= -10.79% p4= -7.48% p5= -3.66%
But if you look at the year 2008 above P4 has a lot less downside than P2, although twice as much as p5.
Same is true in 2011, 2000-2002 and most other years when P2 went negative.
Since I believe we are about to see a downturn in some way, I am thus going for the P4 asset mix at this time.
I am now going to look at the backtesting related to the UK where it WILL be different.
For instance the 2008 UK P5 gained 5.9% rather than lost 3.66 % like the US one. Losses in the other downturns were less also due to the buffering affect of gold on the currency change.
I found this website which illustrated well how the Permanent Portfolio reacted in different regions.
From data in that website, the UK P2 lost 17% which is 3% more than the US equivalent, so the total gap was 12.5% different in UK and US between P2 and P4 showing the greater value of Gold in the UK portfolio.
This chart shows one reason why because of the fluctuating price of gold versus currency and thus the effect that has on UK equity and bond market.
Compare the 10 year versus the 1 year charts.
Does anyone know of an historical data set or spread sheet that bases on UK pound for say Gold, UK Gilt 20+year, UK Equity , International equity, and UK cash ? The international equity is the main challenge but I also do not know what to use for UK cash. I suppose I could use a mix of the rate on 2 year and 5 year cash bonds with Building Society.
I have been a believer in the use of Gold to combat all manner of issues that happen such as devaluation of currency, inflation and general malaise in the financial world.
I backtested 5 portfolios to test my allocation using rev13c of the backtest spreadsheet over both time ranges 1972-2013 (T1) and 1985-2013 (T2). The gain and risk are shown for each.
p1 was 50% Total US, 30% Total Int, 20% Total Bond.
p2 was 50% Total Int, 50% Total Bond
p3 was 50% Total Int, 50% LTGB
p4 was 31% Total Int, 19% LTGB, 19% T Bill Money Market, 31% Gold
p5 was 25% Total Int, 25% LTGB, 25% T Bill Money Market, 25% Gold
1972-2013 (T1)
P1 P2 P3 P4 P5
Average 11.36% 10.21% 10.68% 9.99% 9.38%
Std. Dev. 14.78% 11.76% 12.30% 11.16% 9.13%
Down SD 8.70% 5.75% 5.16% 4.50% 3.58%
Up SD 7.62% 8.22% 9.34% 8.65% 7.15%
CAGR 10.31% 9.59% 10.03% 9.46% 9.02%
Sharpe 0.42 0.43 0.45 0.43 0.46
Sortino 0.73 0.91 1.10 1.11 1.22
US Mkt. Corr. 0.94 0.69 0.61 0.21 0.21
Intl. Corr. 0.87 0.97 0.88 0.58 0.56
Total - Rebalanced (N) 615266 467860 554114 444586 375384
Total-Unbalanced (N) 547241 424603 443704 333520 299929
Total - Rebalanced (Real) 108509 82513 97724 78408 66203
1985-2013 (T2).
P1 P2 P3 P4 P5
Average 11.73% 10.21% 11.36% 8.34% 8.01%
Std. Dev. 15.14% 12.79% 13.15% 10.08% 8.47%
Down SD 8.77% 6.13% 4.89% 4.85% 3.94%
Up SD 7.59% 9.13% 10.72% 7.19% 6.25%
CAGR 10.63% 9.49% 10.65% 7.89% 7.70%
Sharpe 0.52 0.50 0.57 0.44 0.49
Sortino 0.77 0.85 1.30 0.69 0.76
US Mkt. Corr. 0.93 0.69 0.57 0.37 0.34
Intl. Corr. 0.89 0.98 0.89 0.86 0.81
Total - Rebalanced 187052 138472 188011 90476 85852
Total - Unbalanced 173893 126771 148692 94739 90396
Total - Rebalanced (Real) 84519 62568 84952 40881 38792
My conclusion from the above is that the P4 portfolio offers more gain than P5 with little difference in risk, but relative to P2 offers the same gain and risk during T1 but 3% less risk with 2% less return over T2.
2008 P1 = -30.74% p2 = -19.53% p3= -10.79% p4= -7.48% p5= -3.66%
But if you look at the year 2008 above P4 has a lot less downside than P2, although twice as much as p5.
Same is true in 2011, 2000-2002 and most other years when P2 went negative.
Since I believe we are about to see a downturn in some way, I am thus going for the P4 asset mix at this time.
I am now going to look at the backtesting related to the UK where it WILL be different.
For instance the 2008 UK P5 gained 5.9% rather than lost 3.66 % like the US one. Losses in the other downturns were less also due to the buffering affect of gold on the currency change.
I found this website which illustrated well how the Permanent Portfolio reacted in different regions.
From data in that website, the UK P2 lost 17% which is 3% more than the US equivalent, so the total gap was 12.5% different in UK and US between P2 and P4 showing the greater value of Gold in the UK portfolio.
This chart shows one reason why because of the fluctuating price of gold versus currency and thus the effect that has on UK equity and bond market.
Compare the 10 year versus the 1 year charts.
Does anyone know of an historical data set or spread sheet that bases on UK pound for say Gold, UK Gilt 20+year, UK Equity , International equity, and UK cash ? The international equity is the main challenge but I also do not know what to use for UK cash. I suppose I could use a mix of the rate on 2 year and 5 year cash bonds with Building Society.
Re: Spreadsheet for backtesting (includes TrevH's data)
kcfin, et al:
I just posted new revision of the spreasheet (Backtest-Portfolio-returns-rev13d.xls) with minor bug fix recently reported to me.
Since the fix is minor, to a single formula for P5 in 1985+ portfolios, if you know enough about Excel, you may want to apply the change to your existing spreasheet without re-entering your portfolio composition in the new revision, just do the following:
There is error in the formula of cell P77 of Compare_Portfolios tab:
=P$58*SUMPRODUCT(P5:P54,TRANSPOSE(Data_85_13!$B$56:$AY$56))
should be
=P$58*SUMPRODUCT(P5:P54,TRANSPOSE(Data_85_13!$B$55:$AY$55))
You can make the 2 changes shown in green, but make sure to complete the change by pressing Shift-Ctrl-Enter combination instead of regular Enter for the change to take effect (so-called array formula). That should result in formula encompassed by { } brackets. If you are not sure what I am talking about, better simply download the new revision from the post linked in the original post of this thread.
I just posted new revision of the spreasheet (Backtest-Portfolio-returns-rev13d.xls) with minor bug fix recently reported to me.
Since the fix is minor, to a single formula for P5 in 1985+ portfolios, if you know enough about Excel, you may want to apply the change to your existing spreasheet without re-entering your portfolio composition in the new revision, just do the following:
There is error in the formula of cell P77 of Compare_Portfolios tab:
=P$58*SUMPRODUCT(P5:P54,TRANSPOSE(Data_85_13!$B$56:$AY$56))
should be
=P$58*SUMPRODUCT(P5:P54,TRANSPOSE(Data_85_13!$B$55:$AY$55))
You can make the 2 changes shown in green, but make sure to complete the change by pressing Shift-Ctrl-Enter combination instead of regular Enter for the change to take effect (so-called array formula). That should result in formula encompassed by { } brackets. If you are not sure what I am talking about, better simply download the new revision from the post linked in the original post of this thread.
Re: Spreadsheet for backtesting (includes TrevH's data)
Thanks to the people who created this neat spreadsheet. I have one suggestion.
There are formulas such as "=IF(SUM(C3:C39)<>100%,"ERROR","")" (cell D40 of Portfolio tab) to check that the portfolio is fully invested. Because of rounding errors, this condition is not robust. You can tinker with the weights and have them sum to one and still get this error. Wherever these portfolio constraint formulas appear, I suggest that they be modified to allow for numerical error. The formula could be "=IF(ABS(SUM(C3:C39)-1))>1.0e-10,"ERROR","")" instead to avoid triggering false errors.
There are formulas such as "=IF(SUM(C3:C39)<>100%,"ERROR","")" (cell D40 of Portfolio tab) to check that the portfolio is fully invested. Because of rounding errors, this condition is not robust. You can tinker with the weights and have them sum to one and still get this error. Wherever these portfolio constraint formulas appear, I suggest that they be modified to allow for numerical error. The formula could be "=IF(ABS(SUM(C3:C39)-1))>1.0e-10,"ERROR","")" instead to avoid triggering false errors.
Re: Spreadsheet for backtesting (includes TrevH's data)
The ROUND() function might be the better choice for rounding errors.Beliavsky wrote:Thanks to the people who created this neat spreadsheet. I have one suggestion.
There are formulas such as "=IF(SUM(C3:C39)<>100%,"ERROR","")" (cell D40 of Portfolio tab) to check that the portfolio is fully invested. Because of rounding errors, this condition is not robust. You can tinker with the weights and have them sum to one and still get this error. Wherever these portfolio constraint formulas appear, I suggest that they be modified to allow for numerical error. The formula could be "=IF(ABS(SUM(C3:C39)-1))>1.0e-10,"ERROR","")" instead to avoid triggering false errors.
For example: "=IF(ROUND(SUM(C3:C39),0) <>100%,"ERROR","")"
Re: Spreadsheet for backtesting (includes TrevH's data)
Hi, I just had a convo with Larry Swedroe on another thread, he says he didn't recommend the "half EM, half SCV" Minimize Fat Tails portfolio that's in the spreadsheet. This is what he says:
Don't know where that allocation came from.
But to me the choice of bonds is personal. Nominals would use Intermediate, like 5- year Treasury (CDs better usually) and TIPS can also be used, and 50-50 tips and nominals also fine
But definitely never showed 50% EM and 50% US SV. Suggest something like 50% US SV, 37.5 ISV and 12.5 EMV --3:1 is about what EM to Int'l is
Larry
It's from the thread "Is 30% SCV/ 70% Bonds really a good idea?"
Color me confused.
Don't know where that allocation came from.
But to me the choice of bonds is personal. Nominals would use Intermediate, like 5- year Treasury (CDs better usually) and TIPS can also be used, and 50-50 tips and nominals also fine
But definitely never showed 50% EM and 50% US SV. Suggest something like 50% US SV, 37.5 ISV and 12.5 EMV --3:1 is about what EM to Int'l is
Larry
It's from the thread "Is 30% SCV/ 70% Bonds really a good idea?"
Color me confused.
"My bond allocation is the amount of money that I cannot afford to lose." -- Taylor Larimore
Re: Spreadsheet for backtesting (includes TrevH's data)
Here's the link: Is 30% SCV/ 70% Bonds really a good idea?
And the post: Subject: Is 30% SCV/ 70% Bonds really a good idea?
And the post: Subject: Is 30% SCV/ 70% Bonds really a good idea?
I'll defer to the experts on this one.larryswedroe wrote:Don't know where that allocation came from.
But to me the choice of bonds is personal. Nominals would use Intermediate, like 5- year Treasury (CDs better usually) and TIPS can also be used, and 50-50 tips and nominals also fine
But definitely never showed 50% EM and 50% US SV. Suggest something like 50% US SV, 37.5 ISV and 12.5 EMV --3:1 is about what EM to Int'l is
Larry
Possible formula error for 'Total-Unbalanced (N) values
Please check formula in cells C77-G77 in the Compare_Portfolios sheet (version 14d). I believe the first cell reference should C$58 through G$58 to index each column rather than $C$58 under the "Total-Unbalanced (N)" values.
For reference, I was viewing from a downloaded spreadsheet in Excel 2010.
Thanks!
Steve
For reference, I was viewing from a downloaded spreadsheet in Excel 2010.
Thanks!
Steve
Re: Possible formula error for 'Total-Unbalanced (N) values
Confirmed (in version 13d). Sharp eye Steve. Most people will never be affected by the error since the whole point of comparing portfolios requires starting with the same amound for all 5 of them, so C58 will be the same as D-G58 anyway in most cases.wanderer wrote:Please check formula in cells C77-G77 in the Compare_Portfolios sheet (version 14d). I believe the first cell reference should C$58 through G$58 to index each column rather than $C$58 under the "Total-Unbalanced (N)" values.
Will fix it with the next release, most likely 14a--unless more serious bugs uncovered this year.
Re: Spreadsheet for backtesting (includes TrevH's data)
Hello,
Maybe someone already posted about this.
Should the Average and Std. Dev. on rows 101 and 102 be (column)$116 : (column)$144 instead of just thru (column)$143 ?
Where (column) is C thru AA.
It appears the 2013 data is being left out of the Average and Std. Dev. calculations.
Not a big deal and maybe I'm wrong.
Thanks,
RL
Maybe someone already posted about this.
Should the Average and Std. Dev. on rows 101 and 102 be (column)$116 : (column)$144 instead of just thru (column)$143 ?
Where (column) is C thru AA.
It appears the 2013 data is being left out of the Average and Std. Dev. calculations.
Not a big deal and maybe I'm wrong.
Thanks,
RL
Re: Spreadsheet for backtesting (includes TrevH's data)
RL, thanks, I confirmed this issue affecting Lazy Portfolio tab (but not other tabs). I fixed it and similar issue in Data tabs (where it did not affect anything but internal tables in the Data tabs themselves). I released revision 13e of the spreasheet where this and the previously reported issues are fixed.rlaustin wrote:Hello,
Maybe someone already posted about this.
Should the Average and Std. Dev. on rows 101 and 102 be (column)$116 : (column)$144 instead of just thru (column)$143 ?
Where (column) is C thru AA.
It appears the 2013 data is being left out of the Average and Std. Dev. calculations.
Not a big deal and maybe I'm wrong.
Thanks,
RL
Re: Spreadsheet for backtesting (includes TrevH's data)
One question I've had with Simba's spreadsheet is the source of data for short term treasuries. For the older data, it's listed as coming from IFA's website, using 2F. 2F, however, is described on their website as 2 year global fixed income index. 3G looks like it might be a better fit - it's described as "IFA Short Term government index".
Re: Spreadsheet for backtesting (includes TrevH's data)
Will this spreadsheet be updated with 2014 results?
- Paul Douglas Boyer
- Posts: 130
- Joined: Wed Mar 07, 2007 2:19 pm
- Location: Leesburg, VA
Re: Spreadsheet for backtesting (includes TrevH's data)
Here is a first cut at the updated Simba backtest spreadsheet with 2014 data.
Backtest-Portfolio-returns-rev14a.xlsx
Click and download from Dropbox. It is Excel .xlsx format for now.
Notes:
Estimated CPI at 0.8.
Substituted VTMGX for VDMIX in 2014. Still references VDMIX in most places for now...
Will work on a new version to clean up and organize some stuff...
Notify of any errors or incompatibilities. Thanks.
Enjoy!
Backtest-Portfolio-returns-rev14a.xlsx
Click and download from Dropbox. It is Excel .xlsx format for now.
Notes:
Estimated CPI at 0.8.
Substituted VTMGX for VDMIX in 2014. Still references VDMIX in most places for now...
Will work on a new version to clean up and organize some stuff...
Notify of any errors or incompatibilities. Thanks.
Enjoy!
Re: Spreadsheet for backtesting (includes TrevH's data)
Hello,
My first post in this forum
I was looking for an excel tool to do portfolio backtesting in Exceland have found this one which looks good. I am wondering how flexible in the spreadsheet. I mean:
- Can I replace the loaded funds by another ones replacing the corresponding historic data?
- Can I set different selection methods within a portfolio selected funds (absolute and relative momentum) ?
Thanks for help
My first post in this forum
I was looking for an excel tool to do portfolio backtesting in Exceland have found this one which looks good. I am wondering how flexible in the spreadsheet. I mean:
- Can I replace the loaded funds by another ones replacing the corresponding historic data?
- Can I set different selection methods within a portfolio selected funds (absolute and relative momentum) ?
Thanks for help
Re: Spreadsheet for backtesting (includes TrevH's data)
Posted Rev14b update to Google Drive. See the OP of this thread to find the link.
Comments are welcome.
Comments are welcome.
Re: Spreadsheet for backtesting (includes TrevH's data)
Another question, does the spreadsheet works in Excel for Mac?
Re: Spreadsheet for backtesting (includes TrevH's data)
Just opened the last version on my Mac, and this seems to work well. Many thanks to Serbeer and the others for this 2014 update.ManuelG wrote:Another question, does the spreadsheet works in Excel for Mac?
Re: Spreadsheet for backtesting (includes TrevH's data)
serbeerManuelG wrote:...
- Can I replace the loaded funds by another ones replacing the corresponding historic data?
I don't see why not. Of course, you need to know Excel well enough to do that. And as long as it is the case, why not simply add a fund? There are unused placeholders for up to 9 more funds in the Data tabs of spreadsheet, not 100% sure if you need to make changes to other tabs, but even if so those should be minor.
- Can I set different selection methods within a portfolio selected funds (absolute and relative momentum) ?
I am not sure where "momentum" is accounted for in the spreadsheet. So the answer is likely to be "Not in the currently maintained spreadsheet." But again, there is no copyright on it that I am aware of, it is a community efforts tool, so, as long as you retain Readme tab with credit to original creators, feel free to modify it to your liking--as long as you know what you are doing, of course. And if you produce something worthwhile, feel free to share it with the forum
Re: Spreadsheet for backtesting (includes TrevH's data)
One small thing. VDMIX is no more, as Vanguard merged its Tax-Managed International and Developed Markets Index funds. The new symbol is VDVIX for the investor's fund, while VTMGX is the admiral's fund. So cell L2 on the Data_72_13 tab should probably be corrected to say VDVIX, as the worksheet typically uses investor's funds if I am not mistaken. And then the expense ratio is currently 0.20%, this needs to be adjusted too.serbeer wrote:Posted Rev14b update to Google Drive. See the OP of this thread to find the link.
Comments are welcome.
Now what is unclear to me is if the history of the fund stays correct if we reference such new symbol. I remember that the trajectory of the tax-managed fund was very similar, but yet not exactly identical to the index fund. One brave soul may need to go recheck the entire sequence of returns?
Re: Spreadsheet for backtesting (includes TrevH's data)
siamond:
VTMGX inception date is 08/17/1999 per
https://advisors.vanguard.com/VGApp/iip ... fund/vtmgx
so it is oldest in the class, while VDVIX inception date is only 12/19/2013
https://fundresearch.fidelity.com/mutua ... /92206J107
Performance of admiral vs investors fund should be precisely identical, and if not, I'd be more comfortable with older fund. That said, if consensus emerges to the opposite, I'll make the change in line with it. Expense ratio will be corrected in the next revision regardless, thanks for bring this to my attention.
Since the spreadsheet now encompasses 42 year period, which should be close to accumulation period of lifetime of investment for majority of people who usually start after 25yo, it is little surprise that there are few funds that stay in business that long. And when funds change, so do expense ratios, but it would be too complex to track the exact dates of switchover and multiple ERs, so spreadsheet only allows for one (usually current) ER when calculating performance. It is not perfect, but it is very very close and is the best we got.
VTMGX inception date is 08/17/1999 per
https://advisors.vanguard.com/VGApp/iip ... fund/vtmgx
so it is oldest in the class, while VDVIX inception date is only 12/19/2013
https://fundresearch.fidelity.com/mutua ... /92206J107
Performance of admiral vs investors fund should be precisely identical, and if not, I'd be more comfortable with older fund. That said, if consensus emerges to the opposite, I'll make the change in line with it. Expense ratio will be corrected in the next revision regardless, thanks for bring this to my attention.
Since the spreadsheet now encompasses 42 year period, which should be close to accumulation period of lifetime of investment for majority of people who usually start after 25yo, it is little surprise that there are few funds that stay in business that long. And when funds change, so do expense ratios, but it would be too complex to track the exact dates of switchover and multiple ERs, so spreadsheet only allows for one (usually current) ER when calculating performance. It is not perfect, but it is very very close and is the best we got.
Re: Spreadsheet for backtesting (includes TrevH's data)
Serbeer, understood. Quite frankly, until a few days ago, I only knew the VTMGX ticker, not the other one! I agree that the performance should be identical between admiral and investor, but the ER would not (duh!), hence a bit of an inconsistency. VTMGX is at 0.09% nowadays, while VDVIX is at 0.20%.
Now my point was that the VDMIX past trajectory might have been a tad different than VTMGX. Again, really no big deal, maybe a simple comment in the cell with the name of the fund would help clarify that this is a bit of a mixed trajectory, due to Vanguard changes.
Now my point was that the VDMIX past trajectory might have been a tad different than VTMGX. Again, really no big deal, maybe a simple comment in the cell with the name of the fund would help clarify that this is a bit of a mixed trajectory, due to Vanguard changes.
Re: Spreadsheet for backtesting (includes TrevH's data)
Hi Serbeer and slamond
Thank you for your answer. Really appreciate.
Will take a look and play around.
Thank you for your answer. Really appreciate.
Will take a look and play around.
Re: Spreadsheet for backtesting (includes TrevH's data)
FWIW, I was unable to even load the version linked at the top of this thread into Open Office. Looks like it gets less than half way loading, then just hangs. I was able to load the version LadyGeek links to here.LadyGeek wrote: I'm not sure what went wrong. 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.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
Kevin M - I assume you mean the version in the post linked here?
Perhaps there's some confusion on how to download, as you need to ignore a download error. I updated the wiki: Simba's backtesting spreadsheet
Click on this icon in Google Drive:
Here's the direct link: Backtest-Portfolio-returns-rev14b.xlsx - Google Drive. It opens fine in LibreOffice Calc, so it should be OK in OpenOffice.simba wrote:[As of 06-Jan-2014, support for this spreadsheet is maintained by Serbeer in this post--admin LadyGeek]
Perhaps there's some confusion on how to download, as you need to ignore a download error. I updated the wiki: Simba's backtesting spreadsheet
Click on this icon in Google Drive:
Re: Spreadsheet for backtesting (includes TrevH's data)
Yes, that's the one, and it doesn't work. I don't get any download error messages, and the downloaded file is 708KB. The Open Office loading indicator gets to about 1/3 very quickly, then staggers and creeps up to somewhere between 1/2 and 2/3. Task Manager shows OpenOffice 4.1.1 (32 bit) using about 50% CPU.
By contrast, the older Backtest-Portfolio-returns-rev13a3.xls file downloads fine, is much larger at 2,360 KB, and loads in about 5-10 seconds. There seem to be three stages of loading; the second shows message "adapting row height", and the third shows message "calculating".
Note that Google drive has a relatively new version, which I'm using, which may be why I'm not seeing any "Whoops" message. With my version, these instructions don't seem to apply exactly (other than "click on the Download button"):
When I click on the download icon, I see a pop-up asking to save or open the file. Doesn't matter whether I save first or open directly with OpenOffice Calc; same hang on loading.
As a side note, I'm mainly interested in just getting the returns data to use in Google Sheets, and this is easy to grab from the PortfolioVisualizer site. I thought maybe the backtesting spreadsheet had data for more funds, but with a quick scan it doesn't look like it has many more if any.
The best resource I've found for getting older returns data on Vanguard funds is the set of Vanguard fund statistics spreadsheets in the Wiki. What would be ideal for my typical needs would be a single Google spreadsheet with a year column and annual returns for each fund in a column. We don't have that, right?
I'm mainly just posting this so you know what works and what doesn't.
Kevin
By contrast, the older Backtest-Portfolio-returns-rev13a3.xls file downloads fine, is much larger at 2,360 KB, and loads in about 5-10 seconds. There seem to be three stages of loading; the second shows message "adapting row height", and the third shows message "calculating".
Note that Google drive has a relatively new version, which I'm using, which may be why I'm not seeing any "Whoops" message. With my version, these instructions don't seem to apply exactly (other than "click on the Download button"):
When I click on the spreadsheet link, I see this (using Firefox):serbeer wrote: ****************************************************************************************************************************************
Reminder: Once Google Drive page with images of tabs of spreadsheet loads, you have to go to File menu and click on Download menu item (or simply click on Download button) to get the entire spreasdheet in .xlsx format which you can open locally on your PC if you have MS Excel installed.
When I click on the download icon, I see a pop-up asking to save or open the file. Doesn't matter whether I save first or open directly with OpenOffice Calc; same hang on loading.
As a side note, I'm mainly interested in just getting the returns data to use in Google Sheets, and this is easy to grab from the PortfolioVisualizer site. I thought maybe the backtesting spreadsheet had data for more funds, but with a quick scan it doesn't look like it has many more if any.
The best resource I've found for getting older returns data on Vanguard funds is the set of Vanguard fund statistics spreadsheets in the Wiki. What would be ideal for my typical needs would be a single Google spreadsheet with a year column and annual returns for each fund in a column. We don't have that, right?
I'm mainly just posting this so you know what works and what doesn't.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
I agree, that sounds like a compatibility problem to me. Unfortunately, I don't have OpenOffice.
I'm just throwing out some ideas.
Have you looked at historical quotes Yahoo! Finance? Or, perhaps use GoogleFinance as described here: Using a spreadsheet to maintain a portfolioKevin M wrote:The best resource I've found for getting older returns data on Vanguard funds is the set of Vanguard fund statistics spreadsheets in the Wiki. What would be ideal for my typical needs would be a single Google spreadsheet with a year column and annual returns for each fund in a column. We don't have that, right?
I'm just throwing out some ideas.
- Paul Douglas Boyer
- Posts: 130
- Joined: Wed Mar 07, 2007 2:19 pm
- Location: Leesburg, VA
Re: Spreadsheet for backtesting (includes TrevH's data)
Here is a differently-saved version of the Excel spreadsheet (Old Excel style).
For those of you having problems opening the spreadsheet in OpenOffice, does this version fix it?
https://www.dropbox.com/s/awrb77zcyjuwd ... b.xls?dl=0
And if so, are there any features that no longer work?
For those of you having problems opening the spreadsheet in OpenOffice, does this version fix it?
https://www.dropbox.com/s/awrb77zcyjuwd ... b.xls?dl=0
And if so, are there any features that no longer work?
Re: Spreadsheet for backtesting (includes TrevH's data)
Yes. I use the GoogleFinance function extensively in portfolio management spreadsheets and market overview spreadsheets, but mainly to get current quotes. The historical prices are not adjusted for distributions, so no good for calculating total returns.LadyGeek wrote:
Have you looked at historical quotes Yahoo! Finance? Or, perhaps use GoogleFinance as described here: Using a spreadsheet to maintain a portfolio
The Yahoo Finance historical prices provide adjusted price, which is good for calculating total return, but the problem is data integrity. I was using YF for some recent analysis of returns back to 1993 before stumbling on the Wiki Vanguard fund data statistics sheets, and then I found a couple of annual return numbers calculated from the Yahoo adjusted prices to be way off compared to what's in the Wiki spreadsheets, in this case specifically for VBMFX. Incidentally, you can use the Google Sheet ImportData function to pull the Yahoo historical quotes directly into a spreadsheet.
So again, the Wiki fund statistics spreadsheets are the best source I've found so far for Vanguard fund returns, although they are missing a few funds. It would be easy to simply grab the return numbers from each of those spreadsheets and put them all into one spreadsheet, but I'm not motivated to do that (yet), so was just wondering if it might be already done, lurking in the Wiki somewhere.
Thanks,
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
I just downloaded the spreadsheet to my Apple Macbook Air. I have the numbers program to open the spreadsheet. I can't seem to get the data to change/update when I compare 5 portfolios. The information stays the same as when I first open the spreadsheet. Any ideas how to get it to work?
Thanks,
Scott D.
Thanks,
Scott D.
Re: Spreadsheet for backtesting (includes TrevH's data)
Unfortunately, the spreadsheet is only supported in Microsoft Excel, and, with LadyGeek's efforts, in LibreOffice.
If unpacked xls type version works better than xlsx for OpenOffice, it can continue to be posted in that format.
I can see Paul Douglas Boyer had the same idea yesterday, I uploaded similar xls save of Backtest-Portfolio-returns-rev14b.xls to Google Drive at
https://drive.google.com/open?id=0B6rEn ... authuser=0
before I've seen his post .
So, Kevin M. please try that in Open Office. Scott D, feel free to do the same in Numbers. And let us know the results please.
But that's the extent of the efforts to make it cross-platform compatible I am afraid, unless someone who have above-mentioned tools and expertise is willing to work on it.
If unpacked xls type version works better than xlsx for OpenOffice, it can continue to be posted in that format.
I can see Paul Douglas Boyer had the same idea yesterday, I uploaded similar xls save of Backtest-Portfolio-returns-rev14b.xls to Google Drive at
https://drive.google.com/open?id=0B6rEn ... authuser=0
before I've seen his post .
So, Kevin M. please try that in Open Office. Scott D, feel free to do the same in Numbers. And let us know the results please.
But that's the extent of the efforts to make it cross-platform compatible I am afraid, unless someone who have above-mentioned tools and expertise is willing to work on it.
Re: Spreadsheet for backtesting (includes TrevH's data)
Looks like the wrong link. Still downloading Backtest-Portfolio-returns-rev14b.xlsx, which is 708KB (much smaller than I guess what you mean by the "unpacked" version, like the one linked by LG that worked), and still does not load.serbeer wrote:I uploaded similar xls save of Backtest-Portfolio-returns-rev14b.xls to Google Drive at
https://drive.google.com/open?id=0B6rEn ... authuser=0
before I've seen his post .
So, Kevin M. please try that in Open Office
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
Yes, this version loads into OpenOffice Calc fine. I didn't try any features, but looked at a couple of the sheets, and they looked fine. Graphs there, etc.Paul Douglas Boyer wrote:Here is a differently-saved version of the Excel spreadsheet (Old Excel style).
For those of you having problems opening the spreadsheet in OpenOffice, does this version fix it?
https://www.dropbox.com/s/awrb77zcyjuwd ... b.xls?dl=0
And if so, are there any features that no longer work?
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
Yeap, wrong link. https://drive.google.com/file/d/0B6rEnG ... sp=sharing is the one for xls version 14b.
All right, since you state that it works with OpenOffice, I'll add the link to my main post with that comment. I'll keep xlsx version for now too--in case someone discovers deficiency in xls.
All right, since you state that it works with OpenOffice, I'll add the link to my main post with that comment. I'll keep xlsx version for now too--in case someone discovers deficiency in xls.
Re: Spreadsheet for backtesting (includes TrevH's data)
This one loads fine, and a with a quick glance at the sheets, it looks OK.serbeer wrote:Yeap, wrong link. https://drive.google.com/file/d/0B6rEnG ... sp=sharing is the one for xls version 14b.
All right, since you state that it works with OpenOffice, I'll add the link to my main post with that comment. I'll keep xlsx version for now too--in case someone discovers deficiency in xls.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
Hi Folks,
I saw your spreadsheet (Rev14b). This is tremendous work; very impressive. Thanks so much for making this available.
I have a question:
If I plug in GLD, I get returns dating back to 1972. But GLD didn't exist until November 2004. So how can you get returns for the years 1972 to 2004? Did you create a synthetic price series prior to 2004? Do you use a mutual fund proxy, and if so, which one?
Thanks very much,
Terence
I saw your spreadsheet (Rev14b). This is tremendous work; very impressive. Thanks so much for making this available.
I have a question:
If I plug in GLD, I get returns dating back to 1972. But GLD didn't exist until November 2004. So how can you get returns for the years 1972 to 2004? Did you create a synthetic price series prior to 2004? Do you use a mutual fund proxy, and if so, which one?
Thanks very much,
Terence
Re: Spreadsheet for backtesting (includes TrevH's data)
They used the actual price change in gold bullion minus what the expense ratio has been for the GLD ETF (.4%).If I plug in GLD, I get returns dating back to 1972. But GLD didn't exist until November 2004. So how can you get returns for the years 1972 to 2004? Did you create a synthetic price series prior to 2004? Do you use a mutual fund proxy, and if so, which one?
Re: Spreadsheet for backtesting (includes TrevH's data)
I believe the spreadsheet sources are the same as used by PortfolioVisualizer. The Frequently Asked Questions lists these data sources for gold:
KevinGold
KITCO returns (kitco.com) 1972-2004
GLD ETF 2004-2014
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
Thanks very much folks. Impressive work, to say the least.
Three more questions:
1) I got on the Kitco site and found historical prices for gold, but older data does not seem to be in a format that allows downloading. Did you folks just obtain the data by hand, or did you find a way to download digital prices?
2) The data on the spreadsheets shows annual returns data, but this does not allow one to calculate things like maximum drawdown, Calmar ratio, etc. Do you have any versions that include daily prices (adjusted closing prices that is)?
3) It appears that you did NOT obtain data from Yahoo Finance or Google. Looks like the source was tamasset, fpanet, Vanguard, and mscibarra. Do you have any idea as to the accuracy of the data? The reason I ask is that we have found that Yahoo/Google data has lots of errors, particularly in older price data. These are often not trivial errors at all. Typically, they involve missing dividends and other distributions, so the adjusted closing prices are incorrectly calculated. And, the problem worsens if the errors are relatively recent (last 10 or 15 years or so), because of the effects of compounding. We have seen a discordance of 40% or more between Yahoo data and more accurate, curated data feeds. Another source of systematic error that creeps into Yahoo data is rounding error, but that is less serious. We have found that data from Stockcharts is far more accurate, and we have yet to discover an error in their data. Also, ETF Replay evidently gets an accurate feed, since we've never found an error in their data either.
Thanks very much,
Terence
Three more questions:
1) I got on the Kitco site and found historical prices for gold, but older data does not seem to be in a format that allows downloading. Did you folks just obtain the data by hand, or did you find a way to download digital prices?
2) The data on the spreadsheets shows annual returns data, but this does not allow one to calculate things like maximum drawdown, Calmar ratio, etc. Do you have any versions that include daily prices (adjusted closing prices that is)?
3) It appears that you did NOT obtain data from Yahoo Finance or Google. Looks like the source was tamasset, fpanet, Vanguard, and mscibarra. Do you have any idea as to the accuracy of the data? The reason I ask is that we have found that Yahoo/Google data has lots of errors, particularly in older price data. These are often not trivial errors at all. Typically, they involve missing dividends and other distributions, so the adjusted closing prices are incorrectly calculated. And, the problem worsens if the errors are relatively recent (last 10 or 15 years or so), because of the effects of compounding. We have seen a discordance of 40% or more between Yahoo data and more accurate, curated data feeds. Another source of systematic error that creeps into Yahoo data is rounding error, but that is less serious. We have found that data from Stockcharts is far more accurate, and we have yet to discover an error in their data. Also, ETF Replay evidently gets an accurate feed, since we've never found an error in their data either.
Thanks very much,
Terence
Re: Spreadsheet for backtesting (includes TrevH's data)
tmdoherty wrote:Thanks very much folks. Impressive work, to say the least.
Three more questions:
1) I got on the Kitco site and found historical prices for gold, but older data does not seem to be in a format that allows downloading. Did you folks just obtain the data by hand, or did you find a way to download digital prices?
Original spreadsheet developer (Simba) is no longer active. The only history on data sources you are going to find is a) from Data_Sources tab of the spreadsheet and b) from previous discussions in this thread, you may want to read the whole thing if keyword search does not come up with anything.
2) The data on the spreadsheets shows annual returns data, but this does not allow one to calculate things like maximum drawdown, Calmar ratio, etc. Do you have any versions that include daily prices (adjusted closing prices that is)?
We only have what is posted. That said, maximum drawdown is there--look at the charts of Portfolio tab.
3) It appears that you did NOT obtain data from Yahoo Finance or Google. Looks like the source was tamasset, fpanet, Vanguard, and mscibarra. Do you have any idea as to the accuracy of the data? The reason I ask is that we have found that Yahoo/Google data has lots of errors, particularly in older price data. These are often not trivial errors at all. Typically, they involve missing dividends and other distributions, so the adjusted closing prices are incorrectly calculated. And, the problem worsens if the errors are relatively recent (last 10 or 15 years or so), because of the effects of compounding. We have seen a discordance of 40% or more between Yahoo data and more accurate, curated data feeds. Another source of systematic error that creeps into Yahoo data is rounding error, but that is less serious. We have found that data from Stockcharts is far more accurate, and we have yet to discover an error in their data. Also, ETF Replay evidently gets an accurate feed, since we've never found an error in their data either.
See the answer to question #1. If you find any errors, please let me know with reference to the source, and I'll correct it.
Thanks very much,
Terence
Re: Spreadsheet for backtesting (includes TrevH's data)
Thanks very much serbeer.
However, the maximum drawdown is annual drawdown only (i.e., end of year). There are no data showing drawdowns within any particular year. I suspect that the actual (daily) drawdowns were significantly greater than the annual drawdowns. We've certainly seen that phenomena in the past.
I think what is needed is the daily data and equity curves calculated from those. This is the only way I know of to compute daily drawdowns, drawdown durations, Calmar ratios, etc. I realize that's easier said than done of course, and I don't mean to minimize the tremendous work that has gone into this project.
Thanks,
Terence
However, the maximum drawdown is annual drawdown only (i.e., end of year). There are no data showing drawdowns within any particular year. I suspect that the actual (daily) drawdowns were significantly greater than the annual drawdowns. We've certainly seen that phenomena in the past.
I think what is needed is the daily data and equity curves calculated from those. This is the only way I know of to compute daily drawdowns, drawdown durations, Calmar ratios, etc. I realize that's easier said than done of course, and I don't mean to minimize the tremendous work that has gone into this project.
Thanks,
Terence
Re: Spreadsheet for backtesting (includes TrevH's data)
Yes, only annual data supported. I believe that's enough for most forum member, not to mention that only a minority on this board is interested in back-testing to begin with. In fact, there is significant percentage who don't even bother to calculate their own annual returns, on assumption that they are doing everything they could think of anyway so whats' the point, they float on the waves of the market not going under and not raising much above its surface.
Plus this forum's philosophy is much closer to buy and hold than to tactical trading. So annual data is all that most people here interested in.
In short, yes, the initial work was tremendous, limited to annual returns to be made possible to begin with, and now we are quite content with simple maintenance, though would, of course, welcome anyone who is interested in adding new features and has skills to do so.
Now, I confess that I personally have no idea what Calmar ratio is, though I know how to use Google if I feel I need to find out (technical analysis is another are that has few followers on this particular forum).
Anyway, welcome to the forum, it really may change your life if you care to follow it--regardless of what draw you here initially. But beware: if you spend enough time here you may actually stop caring about daily drawdowns
Plus this forum's philosophy is much closer to buy and hold than to tactical trading. So annual data is all that most people here interested in.
In short, yes, the initial work was tremendous, limited to annual returns to be made possible to begin with, and now we are quite content with simple maintenance, though would, of course, welcome anyone who is interested in adding new features and has skills to do so.
Now, I confess that I personally have no idea what Calmar ratio is, though I know how to use Google if I feel I need to find out (technical analysis is another are that has few followers on this particular forum).
Anyway, welcome to the forum, it really may change your life if you care to follow it--regardless of what draw you here initially. But beware: if you spend enough time here you may actually stop caring about daily drawdowns
Re: Spreadsheet for backtesting (includes TrevH's data)
I agree but it is a wonderful tool. My only suggestion would be to add an Intermediate Term Investment Grade Bond Fund. Other than that it's close to perfect.serbeer wrote:Yes, only annual data supported. I believe that's enough for most forum member, not to mention that only a minority on this board is interested in back-testing to begin with. In fact, there is significant percentage who don't even bother to calculate their own annual returns, on assumption that they are doing everything they could think of anyway so whats' the point, they float on the waves of the market not going under and not raising much above its surface.
Plus this forum's philosophy is much closer to buy and hold than to tactical trading. So annual data is all that most people here interested in.
In short, yes, the initial work was tremendous, limited to annual returns to be made possible to begin with, and now we are quite content with simple maintenance, though would, of course, welcome anyone who is interested in adding new features and has skills to do so.
Now, I confess that I personally have no idea what Calmar ratio is, though I know how to use Google if I feel I need to find out (technical analysis is another are that has few followers on this particular forum).
Anyway, welcome to the forum, it really may change your life if you care to follow it--regardless of what draw you here initially. But beware: if you spend enough time here you may actually stop caring about daily drawdowns
Re: Spreadsheet for backtesting (includes TrevH's data)
I am curious if someone else ran into this problem.
The below noted formula when cut and paste from Simba spreadsheet (Excel 1997-2003) does not work in Excel 2010. I am reasonably familiar with Excel formulas but now 'am lost. Even just the product function with the % added or just PRODUCT(1+B8:B20) results in "VALUE!" error. The downloaded Simba spreadsheet works in Excel 2010 version without an issue
=IF(ISERROR((PRODUCT(1+B8:B36%)^(1/COUNT(B8:B36))-1)*100),"",(PRODUCT(1+B8:B36%)^(1/COUNT(B8:B36))-1)*100)
Thanks
The below noted formula when cut and paste from Simba spreadsheet (Excel 1997-2003) does not work in Excel 2010. I am reasonably familiar with Excel formulas but now 'am lost. Even just the product function with the % added or just PRODUCT(1+B8:B20) results in "VALUE!" error. The downloaded Simba spreadsheet works in Excel 2010 version without an issue
=IF(ISERROR((PRODUCT(1+B8:B36%)^(1/COUNT(B8:B36))-1)*100),"",(PRODUCT(1+B8:B36%)^(1/COUNT(B8:B36))-1)*100)
Thanks
Having freedom, food and roof is being 90% lucky in life and so is index investing. So, don't let the remaining 10% bother you.
Re: Spreadsheet for backtesting (includes TrevH's data)
This is intended to be an array formula. When you edit it, you need to press ctrl/shift/enter. Check this short tutorial for more details:paper200 wrote:I am curious if someone else ran into this problem.
The below noted formula when cut and paste from Simba spreadsheet (Excel 1997-2003) does not work in Excel 2010. I am reasonably familiar with Excel formulas but now 'am lost. Even just the product function with the % added or just PRODUCT(1+B8:B20) results in "VALUE!" error. The downloaded Simba spreadsheet works in Excel 2010 version without an issue
=IF(ISERROR((PRODUCT(1+B8:B36%)^(1/COUNT(B8:B36))-1)*100),"",(PRODUCT(1+B8:B36%)^(1/COUNT(B8:B36))-1)*100)
Thanks
http://www.excel-easy.com/functions/array-formulas.html
Re: Spreadsheet for backtesting (includes TrevH's data)
Fantastic! Many Thanks. Learnt something new
Having freedom, food and roof is being 90% lucky in life and so is index investing. So, don't let the remaining 10% bother you.
Re: Spreadsheet for backtesting (includes TrevH's data)
Thanks for the updates. This is a wonderful tool
Re: Spreadsheet for backtesting (includes TrevH's data)
There are a number of funds that I would like to track with this spreadsheet, but they are too new for the existing 2 categories (1972+ and 1985+).
I would like to create a third category from 1995. It looks to be a lot of work, but perhaps not as bad as I think. I would need to copy the existing 1985 and to that add new funds from 1995. I'm wondering if there is any write-up that may walk me through the steps.
Is this something that others would find useful? The funds I'm planning on adding are DFA funds.
I would like to create a third category from 1995. It looks to be a lot of work, but perhaps not as bad as I think. I would need to copy the existing 1985 and to that add new funds from 1995. I'm wondering if there is any write-up that may walk me through the steps.
Is this something that others would find useful? The funds I'm planning on adding are DFA funds.
Re: Spreadsheet for backtesting (includes TrevH's data)
Sorry, there is not. I am sure some people will find it useful. Majority on this forum... probably not. See my comments a few posts above regarding history of the spreadsheet and its usefulness for forum members. Few people use DFA here. Even fewer would think 20yo history is sufficient for any back-testing. Just my 2c.Leif wrote: I'm wondering if there is any write-up that may walk me through the steps. Is this something that others would find useful? The funds I'm planning on adding are DFA funds.
Re: Spreadsheet for backtesting (includes TrevH's data)
They have some classes not readily found, such as ISV and EMV. However, if there is not much interest, or people believe the time frame is too short, I 'll see if I can do a simple update (hack) for my own use. That would certainly be easier in the short run. Thanks.serbeer wrote:Sorry, there is not. I am sure some people will find it useful. Majority on this forum... probably not. See my comments a few posts above regarding history of the spreadsheet and its usefulness for forum members. Few people use DFA here. Even fewer would think 20yo history is sufficient for any back-testing. Just my 2c.Leif wrote: I'm wondering if there is any write-up that may walk me through the steps. Is this something that others would find useful? The funds I'm planning on adding are DFA funds.