Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

kcfin has a comprehensive case study for a UK portfolio here: Re: Some discussion from the UK [Retirement withdrawal strat
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
kcfin
Posts: 50
Joined: Tue Feb 18, 2014 9:12 am

UK data needed.

Post by kcfin »

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.
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

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.
Beliavsky
Posts: 1233
Joined: Sun Jun 29, 2014 10:21 am

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

Post by Beliavsky »

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.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

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.
The ROUND() function might be the better choice for rounding errors.

For example: "=IF(ROUND(SUM(C3:C39),0) <>100%,"ERROR","")"
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
scone
Posts: 1457
Joined: Wed Jul 11, 2012 4:46 pm

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

Post by scone »

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. :confused
"My bond allocation is the amount of money that I cannot afford to lose." -- Taylor Larimore
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

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?
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
I'll defer to the experts on this one.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
wanderer
Posts: 318
Joined: Sat Aug 16, 2014 4:09 pm
Location: Houston, Texas, USA

Possible formula error for 'Total-Unbalanced (N) values

Post by wanderer »

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
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

Re: Possible formula error for 'Total-Unbalanced (N) values

Post by serbeer »

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.
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.

Will fix it with the next release, most likely 14a--unless more serious bugs uncovered this year.
rlaustin
Posts: 26
Joined: Mon Jul 22, 2013 11:45 am
Location: Austin, Texas

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

Post by rlaustin »

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
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

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
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.
dcabler
Posts: 4482
Joined: Wed Feb 19, 2014 10:30 am

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

Post by dcabler »

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".
edge
Posts: 3833
Joined: Mon Feb 19, 2007 6:44 pm
Location: NY

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

Post by edge »

Will this spreadsheet be updated with 2014 results?
User avatar
Paul Douglas Boyer
Posts: 130
Joined: Wed Mar 07, 2007 2:19 pm
Location: Leesburg, VA

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

Post by Paul Douglas Boyer »

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!
ManuelG
Posts: 3
Joined: Wed Jan 21, 2015 2:41 pm

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

Post by ManuelG »

Hello,

My first post in this forum :happy

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
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

Posted Rev14b update to Google Drive. See the OP of this thread to find the link.
Comments are welcome.
ManuelG
Posts: 3
Joined: Wed Jan 21, 2015 2:41 pm

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

Post by ManuelG »

Another question, does the spreadsheet works in Excel for Mac?
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

ManuelG wrote:Another question, does the spreadsheet works in Excel for Mac?
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.
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

ManuelG 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 :)
serbeer
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

serbeer wrote:Posted Rev14b update to Google Drive. See the OP of this thread to find the link.
Comments are welcome.
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.

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?
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

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.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

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.
ManuelG
Posts: 3
Joined: Wed Jan 21, 2015 2:41 pm

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

Post by ManuelG »

Hi Serbeer and slamond

Thank you for your answer. Really appreciate.

Will take a look and play around.
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

LadyGeek wrote: I'm not sure what went wrong. :confused In any case, I incorporated the Portfolio chart changes to work with LibreOffice Calc: Backtest-Portfolio-returns-rev13a3.xls. Those with LibreOffice / OpenOffice (and Excel) should give it a try. I made no changes other than what was needed for the charts.
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.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Kevin M - I assume you mean the version in the post linked here?
simba wrote:[As of 06-Jan-2014, support for this spreadsheet is maintained by Serbeer in this post--admin LadyGeek]
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.

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:

Image
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

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"):
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 spreadsheet link, I see this (using Firefox):

Image

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.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

I agree, that sounds like a compatibility problem to me. Unfortunately, I don't have OpenOffice.
Kevin 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?
Have you looked at historical quotes Yahoo! Finance? Or, perhaps use GoogleFinance as described here: Using a spreadsheet to maintain a portfolio

I'm just throwing out some ideas.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Paul Douglas Boyer
Posts: 130
Joined: Wed Mar 07, 2007 2:19 pm
Location: Leesburg, VA

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

Post by Paul Douglas Boyer »

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?
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

LadyGeek wrote:
Have you looked at historical quotes Yahoo! Finance? Or, perhaps use GoogleFinance as described here: Using a spreadsheet to maintain a portfolio
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.

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.
CaptSJD
Posts: 17
Joined: Sun Apr 15, 2007 1:28 pm
Location: Thousand Oaks, CA

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

Post by CaptSJD »

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.
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

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.
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

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
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.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

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?
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.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

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.
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

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.
This one loads fine, and a with a quick glance at the sheets, it looks OK.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
tmdoherty
Posts: 3
Joined: Fri Feb 20, 2015 9:07 pm

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

Post by tmdoherty »

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
stlutz
Posts: 5585
Joined: Fri Jan 02, 2009 12:08 am

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

Post by stlutz »

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?
They used the actual price change in gold bullion minus what the expense ratio has been for the GLD ETF (.4%).
User avatar
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

I believe the spreadsheet sources are the same as used by PortfolioVisualizer. The Frequently Asked Questions lists these data sources for gold:
Gold
KITCO returns (kitco.com) 1972-2004
GLD ETF 2004-2014
Kevin
If I make a calculation error, #Cruncher probably will let me know.
tmdoherty
Posts: 3
Joined: Fri Feb 20, 2015 9:07 pm

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

Post by tmdoherty »

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
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

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
tmdoherty
Posts: 3
Joined: Fri Feb 20, 2015 9:07 pm

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

Post by tmdoherty »

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
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

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 :)
User avatar
midareff
Posts: 7711
Joined: Mon Nov 29, 2010 9:43 am
Location: Biscayne Bay, South Florida

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

Post by midareff »

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 :)
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.
paper200
Posts: 289
Joined: Sat Feb 02, 2008 10:40 am

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

Post by paper200 »

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
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.
User avatar
siamond
Posts: 6003
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

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
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:
http://www.excel-easy.com/functions/array-formulas.html
paper200
Posts: 289
Joined: Sat Feb 02, 2008 10:40 am

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

Post by paper200 »

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.
wtoner
Posts: 7
Joined: Mon Nov 29, 2010 11:07 am

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

Post by wtoner »

Thanks for the updates. This is a wonderful tool :D
User avatar
Leif
Posts: 3698
Joined: Wed Sep 19, 2007 4:15 pm

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

Post by Leif »

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.
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

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.
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.
User avatar
Leif
Posts: 3698
Joined: Wed Sep 19, 2007 4:15 pm

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

Post by Leif »

serbeer wrote:
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.
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.
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.
Post Reply