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

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

Post by siamond »

I have to agree with Serbeer, 20 years of history really doesn't tell you much.
User avatar
Leif
Posts: 3705
Joined: Wed Sep 19, 2007 4:15 pm

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

Post by Leif »

Its less than I would like. But it does include two major recessions and two (three ?) major run ups. So I'll take it with a grain of salt. Its nice that they provide 9 empty slots in the funds list. I'm hoping I can add my data in the 85+ then just control the start point to 95 or later when using those funds. A lot less work then my original ambitious plan.
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

Leif wrote:
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.
Have you tried Portfolio Visualizer?

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Leif
Posts: 3705
Joined: Wed Sep 19, 2007 4:15 pm

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

Post by Leif »

Kevin M wrote:
Leif wrote:
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.
Have you tried Portfolio Visualizer?

Kevin
Thanks for the link. Yes I have. But I don't believe they have ISV or EMV either. But the Visualizer is an excellent tool. I've experimented with it a lot.

Also, it looks like the 85+ data was originally added in 2007 with data from 1985-2006. So at that time it represented 22 years of data.
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

Right, some of the Portfolio Visualizer modules allow you to enter ticker symbols, but others only use the predefined asset classes.

My understanding is that PV uses the same data as the backtesting spreadsheet. What does the spreadsheet do that PV doesn't do--just curious?

You might put in an enhancement request to see if PV will add the asset classes.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Leif
Posts: 3705
Joined: Wed Sep 19, 2007 4:15 pm

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

Post by Leif »

Kevin M wrote:Right, some of the Portfolio Visualizer modules allow you to enter ticker symbols, but others only use the predefined asset classes.

My understanding is that PV uses the same data as the backtesting spreadsheet. What does the spreadsheet do that PV doesn't do--just curious?

You might put in an enhancement request to see if PV will add the asset classes.

Kevin
Thanks Kevin for the info on entering ticker symbols. I was not aware that was available.

Of course when this was started the Visualizer did not exist. However, I think that comparing against the 25 predefined portfolios has some value.

Is the creator of PV a Boglehead? This site is referenced and your right, the data sources look similar to those of this spreadsheet for the asset classes.
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

Backtesting Google Spreadsheet

Post by Fryxell »

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

I have developed something like Simba's backtesting spreadsheet but for Google Spreadsheets. I found Simba's spreadsheet to be difficult to update with data for new years and new asset classes. I also wanted something that I could use on my iPad. I originally developed it for myself but I have improved it to the point that I thought it might be of interest to others.

Currently I have the following features:
  • 1. Can be set to display data for any rolling X-year period. You can set it to display returns for rolling 10 years, rolling 7 years, etc.

    2. Data back to 1927 for selected investment classes.

    3. It has data for some asset classes not found in Simba's or in PortfolioVisualizer.

    4. Accessible/usable on iPad, phones, etc.

    5. Returns can be displayed in nominal, real terms, or cash-adjusted terms.

    6. Easier to customize (I think).

    7. Because it is on Google Spreadsheets, it can also calculate returns for 2015 by pulling the most up-to-date data from Google Finance.

    8. Contains most of the existing Simba spreadsheet functionality, but not all. It is currently lacking estimates of unbalanced returns and portfolio cash values, but these things would be relatively easy to add.
I have been thinking about sharing this spreadsheet. But first I thought I would gauge the interest level. Is this something Bogleheads would be interested in?
User avatar
jurhill
Posts: 75
Joined: Fri Mar 05, 2010 9:49 pm
Contact:

Re: Backtesting Google Spreadsheet

Post by jurhill »

yes, one would be interested
Luck is when Preparation meets Opportunity
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

Re: Backtesting Google Spreadsheet

Post by serbeer »

Wow, that's pretty cool! Do share please.
User avatar
David Jay
Posts: 14587
Joined: Mon Mar 30, 2015 5:54 am
Location: Michigan

Re: Backtesting Google Spreadsheet

Post by David Jay »

Sure. I can always use another spreadsheet :wink:
It's not an engineering problem - Hersh Shefrin | To get the "risk premium", you really do have to take the risk - nisiprius
User avatar
fetch5482
Posts: 1722
Joined: Fri Aug 15, 2014 4:55 pm

Re: Backtesting Google Spreadsheet

Post by fetch5482 »

Please share - would love an online version of Simba's spreadsheet.
(AGE minus 23%) Bonds | 5% REITs | Balance 80% US (75/25 TSM/SCV) + 20% International (80/20 Developed/Emerging)
User avatar
LadyGeek
Site Admin
Posts: 95703
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

I merged Fryxell's thread into here. Yes, there is much interest. When you're ready, please post a link to your spreadsheet.

The related wiki article: Simba's backtesting spreadsheet

Update: I added a "shortcut" to the wiki's search box. Typing in "Backtesting spreadsheet" (or just "Backtesting" to autofill the entry) will go to the wiki article. There's always room for more spreadsheets. Since "Simba" is well-known, this is the best place to consolidate them.
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
1210sda
Posts: 1865
Joined: Wed Feb 28, 2007 7:31 am

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

Post by 1210sda »

Definitely I want to see your spreadsheet. Thanks
1210
acr123
Posts: 100
Joined: Mon Jan 19, 2009 8:50 am
Location: Armonk, NY

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

Post by acr123 »

Would definitely like to see your spreadsheet. It would be good not to have to make a lot of changes each year.

Al
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Backtesting Google Spreadsheet

Post by Kevin M »

PortfolioVisualizer meets most of my needs that I would otherwise use the backtesting spreadsheet for, and for my own analyses, I just grab the data from PV (much easier for a Google Sheets user than downloading the backtesting spreadsheet). However, these items could add value to analyses I am likely to do:
Fryxell wrote: --> Google Spreadsheets.

2. Data back to 1927 for selected investment classes.

3. It has data for some asset classes not found in Simba's or in PortfolioVisualizer.

5. Returns can be displayed in nominal, real terms, or cash-adjusted terms.

7. Because it is on Google Spreadsheets, it can also calculate returns for 2015 by pulling the most up-to-date data from Google Finance.
So yes, please share.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
JasonF
Posts: 100
Joined: Sun Jan 17, 2010 7:12 am

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

Post by JasonF »

I too would love to see your spreadsheet. Thanks in advance!
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

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

Post by longinvest »

What does cash-adjusted terms mean (in contrast to nominal and inflation-adjusted terms)?
Last edited by longinvest on Sun Jul 05, 2015 7:11 am, edited 2 times in total.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
CWRadio
Posts: 649
Joined: Sat Feb 02, 2008 9:04 am
Location: SE Michigan Ionosphere

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

Post by CWRadio »

I also would like to see your backtesing spreadsheet. Paul
Call_Me_Op
Posts: 9883
Joined: Mon Sep 07, 2009 2:57 pm
Location: Milky Way

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

Post by Call_Me_Op »

Fryxell,

I am very interested!
Best regards, -Op | | "In the middle of difficulty lies opportunity." Einstein
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

Hi all,

Thanks for the interest. I'm working on it...
leasters
Posts: 4
Joined: Thu Jul 23, 2015 9:52 am

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

Post by leasters »

Anyone know where I can get monthly ST Treasury return data from 1972-1991?
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

leasters wrote:Anyone know where I can get monthly ST Treasury return data from 1972-1991?
You can pull Treasury yields from FRED (daily, monthly, weekly) and calculate the returns yourself: Treasury Constant Maturity - FRED - St. Louis Fed.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
leasters
Posts: 4
Joined: Thu Jul 23, 2015 9:52 am

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

Post by leasters »

Thanks Kevin,

I'm struggling with the conversion to return and or a price with just the yield data. Here's the formula I found...http://admainnew.morningstar.com/direct ... dology.pdf, which is a bit complex. Do you know of a more simple way to do it in excel?

Thanks again.

Lloyd
leasters
Posts: 4
Joined: Thu Jul 23, 2015 9:52 am

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

Post by leasters »

Okay, I think I got it. Was making it way too complicated. Thanks for your help.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

leasters wrote:Anyone know where I can get monthly ST Treasury return data from 1972-1991?
This might not exactly answer your question, but I'd strongly suggest you buy an old edition of the Ibbotson SBBI Classic Yearbook (on eBay or Amazon or similar). This will be well worth the money, for the data you're looking for as well as for many other reasons. You'll find 1926+ returns (annual and monthly) for US treasury bills, IT government bonds and LT government bonds. Oh, and corporate bonds too. And various types of equity, of course.
leasters
Posts: 4
Joined: Thu Jul 23, 2015 9:52 am

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

Post by leasters »

Thank you!
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

Bactetst27 Google Spreadsheet

Post by Fryxell »

Hi all,

Here is a beta version of my backtesting spreadsheet:

Instructions/Notes:
https://docs.google.com/document/d/1FI1 ... LzR7hy0oXg

Spreadsheet: Down for revisions

Just some quick notes:
  • At this time this is intended for use on Google Spreadsheets/Google Drive. It is not intended to be ported/downloaded into formats such as LibreOffice or Excel. In part this is because the current version uses some real time functions, such as GoogleFinance.

    You define your portfolios in the portfolio tab.

    To view pre-1972 data, expand the relevant rows. By default only the 1972+ rows display. To use the pre-1972 data (or the 2015 data) simply enter in the relevant years in the Start Year and End Year cells.

    For testing purposes only.
I welcome feedback.

Enjoy.
Last edited by Fryxell on Wed Sep 16, 2015 9:31 pm, edited 2 times in total.
User avatar
LadyGeek
Site Admin
Posts: 95703
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Nice, but the spreadsheet is published as an HTML document. I can't do anything with it.

Can you change access to "anyone with the link can view"? I'd like to make a copy for evaluation.
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.
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

LadyGeek wrote:Nice, but the spreadsheet is published as an HTML document. I can't do anything with it.

Can you change access to "anyone with the link can view"? I'd like to make a copy for evaluation.
I updated the links. Hope they work now.
User avatar
LadyGeek
Site Admin
Posts: 95703
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

The links do work and I've copied the file to my own Google Drive account. That's one impressive spreadsheet. I like the scatter chart presentations.

Before I start looking at the formulas, my first approach is to make this work offline.

First, I replaced the GoogleFinance formula cells in the Data tab (rows 6, 7) with their values. Next, I downloaded the spreadsheet as MS Excel, but opened with LibreOffice Calc. This is the approach which seems to be the "most" compatible between LibreOffice and MS Excel - work in .xlsx format, from LibreOffice Calc.

I then ran into a problem with the IFERROR() function. LibreOffice Calc doesn't appear to implement this function, so I tried a work-around. For example, in Correlations cell B12 (value = 0.67):

Code: Select all

=IfError(Correl(Data!$C$56:$C,Data!B$56:B))
becomes:

Code: Select all

=IF(ISERROR(CORREL($Data.$C$56:$C1001,$Data.B$56:B1001)),0,CORREL($Data.$C$56:$C1001,$Data.B$56:B1001))
I'll probably work in MS Excel to see how far I can get. After the formulas are working, the next step would be to recreate the charts.

While I work on compatibility, can someone with a Google Drive account review the data and formulas?

Of course, everyone is welcome to work on the compatibility problems. Whoever can get there first is fine by me. I just wanted to take a crack at this challenge.
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: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

LadyGeek wrote: Before I start looking at the formulas, my first approach is to make this work offline.
Why? Anyone can use a Google spreadsheet for free, and on just about any platform, including a Chromebook. I use a Chromebook for 95% of everything I do, so almost anything on a platform requiring a heavy OS is useless to me, which is one reason I've pretty much ignored the backtesting spreadsheet.

The only reason I even turn on my windows-based PC is to run the desktop version of TurboTax, which has enough advantages to make it worth dealing with a heavy-OS system.

So what's the point of taking a perfectly fine solution that everyone can use on almost any platform for free, and converting it to a platform with less-widespread usability?

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

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

Post by LadyGeek »

Simply because I like having my data offline and not shared with anyone else. If you want to use online data, that's fine by me. Offline is my preference.

Additionally, LibreOffice and MS Excel have more capability than Google Sheets. If I want to do something fancy, I have the flexibility to do so.

Granted, I lose the realtime GoogleFinance functions, but it doesn't seem that important for backtesting.
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.
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

LadyGeek wrote:The links do work and I've copied the file to my own Google Drive account. That's one impressive spreadsheet. I like the scatter chart presentations.
Thanks for the compliments. I was trying to get better charts but those are the best I could do, unless I'm missing something. Google spreadsheets is not as powerful with graphs/charts as Excel.
Before I start looking at the formulas, my first approach is to make this work offline.

First, I replaced the GoogleFinance formula cells in the Data tab (rows 6, 7) with their values. Next, I downloaded the spreadsheet as MS Excel, but opened with LibreOffice Calc. This is the approach which seems to be the "most" compatible between LibreOffice and MS Excel - work in .xlsx format, from LibreOffice Calc.
You probably also want to replace them in row 99.
I then ran into a problem with the IFERROR() function. LibreOffice Calc doesn't appear to implement this function, so I tried a work-around. For example, in Correlations cell B12 (value = 0.67):

Code: Select all

=IfError(Correl(Data!$C$56:$C,Data!B$56:B))
becomes:

Code: Select all

=IF(ISERROR(CORREL($Data.$C$56:$C1001,$Data.B$56:B1001)),0,CORREL($Data.$C$56:$C1001,$Data.B$56:B1001))
I'll probably work in MS Excel to see how far I can get. After the formulas are working, the next step would be to recreate the charts.
I use IfError to: (a) eliminate ugly #N/A errors and (b) to allow some calculations to proceed when some data is missing. Since I am only using IfError for cosmetic purposes or to deal when data is missing, you could probably safely eliminate the IfError calls for testing purposes. In fact, for testing it sis ometimes better to not have IfError calls because the error codes give you hints as to what caused the error. Excel does implement the function. In Excel you could simply do a call like this:

Code: Select all

IfError(FORMULA,"")
I don't have LibreOffice experience so I don't think I can help you there until I have more free time.
While I work on compatibility, can someone with a Google Drive account review the data and formulas?
I imagine you may have questions on where I got the data since I was a bit brief in my document. it is quite time consuming to also document things.
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

LadyGeek wrote:Simply because I like having my data offline and not shared with anyone else. If you want to use online data, that's fine by me. Offline is my preference.

Additionally, LibreOffice and MS Excel have more capability than Google Sheets. If I want to do something fancy, I have the flexibility to do so.

Granted, I lose the realtime GoogleFinance functions, but it doesn't seem that important for backtesting.
I thought the backtesting spreadsheets were just to backtest generic portfolios, in which case, who cares if it's online or offline? It's not personal finance data. Or am I misunderstanding the way you intend to use a backtesting spreadsheet? Seems unlikely, since you mention that GoogleFinance is not relevant for backtesting.

LibreOffice and MS Excel have zero compatibility with a Chromebook; simply not usable at all, so about as little compatibility as you can get. But I understand if you personally are looking for compatibility between software products that you use, and want to do things that Google Sheets does not offer for your personal use, or that only has benefit to people using a platform that is compatible with the one you use (i.e., not me or anyone else who primarily or exclusively uses a Chromebook).

Hope you understand that this is coming from the point of view of a Chromebook user, or anyone who doesn't want to install huge, bloated programs, for whom any fancy stuff you do in Excel and similar programs is useless. Again, a Google spreadsheet can be used by anyone, but an Excel spreadsheet cannot. I'm basically just encouraging more use of Google Sheets and less Excel (etc.) for things we share on BH, for purely selfish reasons.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

Kevin M wrote:
LadyGeek wrote: Before I start looking at the formulas, my first approach is to make this work offline.
Why? Anyone can use a Google spreadsheet for free, and on just about any platform, including a Chromebook. I use a Chromebook for 95% of everything I do, so almost anything on a platform requiring a heavy OS is useless to me, which is one reason I've pretty much ignored the backtesting spreadsheet.
For me the main concern with Google is that Google could unilaterally kill the service or break the spreadsheet. I'm not as concerned with data privacy in this particular case since it's just backtesting data. I went with this format because I wanted something I could use on an iPad and that could use real time functions. Those were the killer features that got me into using the Google platform. I also figured, as you do, that anyone with a browser can use this spreadsheet.

I did have in the back of my head the idea of porting it as insurance in case Google mucked with its service.
User avatar
LadyGeek
Site Admin
Posts: 95703
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Fryxell wrote:...I imagine you may have questions on where I got the data since I was a bit brief in my document. it is quite time consuming to also document things.
The crux of the matter is the credibility of the source and the accuracy of transferring the info to the spreadsheet.

On the first part, you've listed Portfolio Visualizer which has an extensive list of data sources. The developer is a member here (pvguy), so I wanted to make sure that pvguy didn't reference the same spreadsheet you're modifying - which would be circular reference.

On the second part, copy-n-paste errors can occur where you least expect them. Especially hard to find are "one off" errors where you get everything except the last data point, or perhaps an entire row / column is shifted by one.

I consider documentation quality a key indicator of the level of effort put into development. It's well written and I don't see any typos (at the moment). Very nicely done.
Fryxell wrote:I use IfError to...
Thanks for the explanation. I agree. It's much better to see that an error exists because something broke.

It's important to see if a data entry error was made, or that something was missed. Forcing a stop until it's fixed is the right approach.

Additionally, removal of the if() function will speed things up. At least by a little, as I suspect the correlation function Correl() is the heavy hitter here.
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.
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

LadyGeek wrote: I consider documentation quality a key indicator of the level of effort put into development. It's well written and I don't see any typos (at the moment). Very nicely done.
I actually have the data I used in an Excel spreadsheet. It's not meant for sharing because it is not completely "clean." That is, I do stuff like reference other data spreadsheets (some of which have notes and messy stuff in them). But I do have the capability to backtrack all of the data I used. I even saved the PDFs for the mutual fund return data I used from Yahoo, iShares, etc., in case the funds are delisted.
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

LadyGeek wrote: The crux of the matter is the credibility of the source and the accuracy of transferring the info to the spreadsheet.
I'm asking myself this question about some of the data sources included in Simba's Spreadsheet and PortfolioVisualizer. I did not include the pre-1988 emerging markets data in my spreadsheet because I don't believe the IFA methodology of estimating Emerging Markets with 50% International Developed Small and 50% International Developed Value to be a good methodology.

I was looking at where the synthetic TIPS data came from. I had assumed it was from a reliable academic paper. But now that I dig deeper I find that the Kothari paper can no longer be found online and that a boglehead obtained the data by eyeballing a graph on that paper (which can no longer be found). So I have no way of verifying that the methodology used by Kothari is reasonable. I can't even verify that the boglehead correctly eyeballed the data. This all seems rather questionable, to put it mildly.

Link: viewtopic.php?t=40588
I assembled the synthetic TIPS dataset in '07 by eyeballing an enlargement of a graph from a 2004 academic paper on TIPS by Kothari & others.

Ibbotson has assembled a syntheticTIPS dataset more recently, but I haven't been able to find a table or graph online of the data described in their paper.
It seems that the pre-2003 commodities return data is also simulated. The link to the papers and files are also broken in this thread: viewtopic.php?t=2559
User avatar
LadyGeek
Site Admin
Posts: 95703
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Fryxell wrote:I use IfError to: (a) eliminate ugly #N/A errors and (b) to allow some calculations to proceed when some data is missing. Since I am only using IfError for cosmetic purposes or to deal when data is missing, you could probably safely eliminate the IfError calls for testing purposes. In fact, for testing it sis ometimes better to not have IfError calls because the error codes give you hints as to what caused the error. Excel does implement the function.
I did a global removal of IfError, which is to do a Find and Replace of "IfError" with (nothing) in all sheets. Since the function wraps the formulas with parentheses, all you need to to is replace the function name itself. Be sure to check the "Also search in formulas". box.

The Correlations sheet kicked out some errors, which I traced to extra cell formulas in Data!C102:C103 - now showing as #DIV/0 errors. Deleting the cell content fixed the correlation errors.

I see why you used IfError() for "cosmetic" purposes, as the unused rows in Correlations are chock full of #DIV/0 and #ERROR messages. There's no impact, but it might be somewhat intimidating to anyone who doesn't understand what this means.

I also experimented with changing formulas containing blank content with 0. Don't do that, as the interpretation of a blank cell is very much different than a cell holding 0 as a value. I'm mentioning this as I thought I could do some housecleaning, but the CAGR and STDDEV calculations changed values. Realizing what happened in hindsight, I thought I should pass along what not to do.
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: 15789
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 mentioning this as I thought I could do some housecleaning, but the CAGR and STDDEV calculations changed values. Realizing what happened in hindsight, I thought I should pass along what not to do.
I use IFERROR and related conditional checks (e.g., IF(ISNA(...)) ) extensively in my Google Sheets. As you've discovered, a blank cell will not adversely affect functions such as AVERAGE, but a zero value will. Also, functions such as SUM will fail if there are any #NA values in any cells in the target range, so using error trapping to replace such values with blanks is not always just cosmetic.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

LadyGeek wrote: I see why you used IfError() for "cosmetic" purposes, as the unused rows in Correlations are chock full of #DIV/0 and #ERROR messages. There's no impact, but it might be somewhat intimidating to anyone who doesn't understand what this means.
I see. I had put those in to estimate the current rate of inflation but didn't think that doing it for future years could mess up the correlation calculations. Thanks for the tip.

Come to think of it, I think I uncovered why my downside deviation is so different from the upside. I think I found an error in how I'm calculating it, which should also be impacting the sortino ratio. It seems some of the array formulas I have for that are counting the null values, thus altering the calculations.

And as Kevin noted, it is possible I used some IfError calls to make functions like Average() work. I can't always remember exactly why I did things.
User avatar
LadyGeek
Site Admin
Posts: 95703
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

Fryxell wrote:Come to think of it, I think I uncovered why my downside deviation is so different from the upside. I think I found an error in how I'm calculating it, which should also be impacting the sortino ratio. It seems some of the array formulas I have for that are counting the null values, thus altering the calculations.
That is my next area of focus, as array formulas are not exported by Google Sheets. LibreOffice Calc complained about a #VALUE! error. After some investigation, the answer was in the Google Sheets help file small print: "note that array formulas cannot be exported." IOW, it needs more work...

"Upside" and "downside" deviations are based on the Minimum Acceptable Rate. Please check your logic as you're missing one case.

Downside:

Code: Select all

If(B32:B154<$B$12,
Upside:

Code: Select all

If(B32:B154*1>$B$12,
What happens when (B32:B154 = $B$12)? One of the above should include "=", e.g. "<=".

What are the formulas you're using (so I don't misinterpret the spreadsheet)? What does the " *1" do in the upside case "B32:B154*1>$B$12" -force a unitary multiply (vs. array)?
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.
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

LadyGeek wrote:
"Upside" and "downside" deviations are based on the Minimum Acceptable Rate. Please check your logic as you're missing one case.
Thanks for pointing that out. I had not thoroughly checked this part of the spreadsheet since I had other unresolved issues here.
Downside:

Code: Select all

If(B32:B154<$B$12,
Upside:

Code: Select all

If(B32:B154*1>$B$12,
What happens when (B32:B154 = $B$12)? One of the above should include "=", e.g. "<=".

What are the formulas you're using (so I don't misinterpret the spreadsheet)? What does the " *1" do in the upside case "B32:B154*1>$B$12" -force a unitary multiply (vs. array)?

What I was trying to do is sum up all the instances where the cell value was above (below) the MAR and calculate the deviation for those as upside (downside) deviation. I was having problems because the formula in Google Sheets was actually counting the null values as zeros, thus messing up the deviation calculations. I multiplied by 1 because it seemed to help in that particular case.

I have played around with it more and I think I have a better solution now. I think the following works better for cells B23 and B24, respectively, of the 'Adjusted' tab:

Code: Select all

=IfError(SQRT(ArrayFormula(Sum(If(B32:B<>"",If(B32:B<$B$12,(B32:B-$B$12)^2,0),"")))/CountIf(B32:B,"<"&$B$12)))

=IfError(SQRT(ArrayFormula(Sum(If(B32:B<>"",If(B32:B>=$B$12,(B32:B-$B$12)^2,0),"")))/CountIf(B32:B,">="&$B$12)))
Of course, this is using the Google Sheets notation/language. It will be different in LibreOffice.
User avatar
LadyGeek
Site Admin
Posts: 95703
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

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

Post by LadyGeek »

In Adjusted, I replaced B23 and B24 with the following (IfError removed):

Code: Select all

=SQRT(ArrayFormula(Sum(If(B32:B<>"",If(B32:B<$B$12,(B32:B-$B$12)^2,0),"")))/CountIf(B32:B,"<"&$B$12))

=(SQRT(ArrayFormula(Sum(If(B32:B<>"",If(B32:B>=$B$12,(B32:B-$B$12)^2,0),"")))/CountIf(B32:B,">="&$B$12)))
B23 = 0.0232
B24 = 0.0264

Column Z (last column) is showing #DIV/0 errors for Sharpe (Z20), Upside (Z24), and Time to Double (Z28).
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.
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

LadyGeek wrote:In Adjusted, I replaced B23 and B24 with the following (IfError removed):

Code: Select all

=SQRT(ArrayFormula(Sum(If(B32:B<>"",If(B32:B<$B$12,(B32:B-$B$12)^2,0),"")))/CountIf(B32:B,"<"&$B$12))

=(SQRT(ArrayFormula(Sum(If(B32:B<>"",If(B32:B>=$B$12,(B32:B-$B$12)^2,0),"")))/CountIf(B32:B,">="&$B$12)))
B23 = 0.0232
B24 = 0.0264

Column Z (last column) is showing #DIV/0 errors for Sharpe (Z20), Upside (Z24), and Time to Double (Z28).
I'm addressing this in a private message because I can't replicate the error you got.

However, I wanted to add a note on the calculation of Sortino Ratios and Downside Deviation. The Sortino Ratio does not seem to be as clearly defined as the Sharpe. I found some debate on the issue:

The more common method of calculating the Sortino ratio divides the (R - Rf) the standard deviation of negative returns: http://www.investopedia.com/terms/s/sortinoratio.asp

Thus calculating it as:

Code: Select all

(R-Rf)/(STDEV of Negative Returns)
http://managed-futures-blog.attaincapit ... -it-wrong/

I used the Red Rock capital method. They define it here: http://www.sunrisecapital.com/wp-conten ... o_0213.pdf

Thus they obtain:

Code: Select all

(R-MAR)/(1/N * SUMMATION(Min(0,Return - MAR)) )
They argue that the downside deviation should count only the values where the return is below the Minimum Acceptable Rate (MAR). The way they define it, they seem to also divide by the total number of returns, and not just the number of negative returns.

If we want to divide by the total number of returns we should be dividing by:

Code: Select all

Count(B32:B)
Instead of:
CountIf(B32:B,"<"&$B$12)
Fryxell
Posts: 183
Joined: Sat Mar 02, 2013 9:08 pm
Location: Dallas

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

Post by Fryxell »

Kevin M wrote:
LadyGeek wrote: Granted, I lose the realtime GoogleFinance functions, but it doesn't seem that important for backtesting.
I thought the backtesting spreadsheets were just to backtest generic portfolios, in which case, who cares if it's online or offline? It's not personal finance data. Or am I misunderstanding the way you intend to use a backtesting spreadsheet? Seems unlikely, since you mention that GoogleFinance is not relevant for backtesting.
I tend to agree with you here. I don't see what personal financial information is exposed. It would seem strange to me to put personal financial information on a backtesting spreadsheet. But maybe some people do this.

I created this because I wanted something that was easier to modify than Simba's and that I could use on my iPad. I like to tinker with things like these but I get tired of being in front of a computer. I originally created this for myself, so I was thinking about meeting my own needs. As the tool got better and better, I realized it may be of use to others.

In this respect I thought the following differentiates mine from Simba's spreadsheet:
  • 1. Usable in mobile devices.
    2. Annual data back to the 1920s.
    3. Easier to customize.
    4. As a bonus it opened up the possibility of using live GoogleFinance data.
I'm actually a bit surprised no one had modified Simba's spreadsheet to incorporate the pre-1972 data.

I posted this after LadyGeek suggested I do so in the forum. I'm not opposed to porting it to more traditional spreadsheets, and thought of doing so myself as a backup in case Google killed or broke the spreadsheets. But it is a project that will take some time. Is this something that others want?

Do others feel more like LadyGeek or like Kevin?
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

Post by Kevin M »

Fryxell wrote: In this respect I thought the following differentiates mine from Simba's spreadsheet:
  • 1. Usable in mobile devices.
    2. Annual data back to the 1920s.
    3. Easier to customize.
    4. As a bonus it opened up the possibility of using live GoogleFinance data.
Don't forget:

5. Usable on a Chromebook.
6. In general, usable on a much broader set of platforms.
2. Annual data back to the 1920s.
Yes! One of my frustrations with the Simba spreadsheet is that it's too big to load into Google Sheets (at least when I've tried in the past), so a Google Sheets user can't even load it to access the data, which is all I really wanted from it. I was thrilled when Portfolio Visualizer came online, and I was able to quickly and easily load the data from that site.

Whether or not I use the backtesting capabilities of your spreadsheet much, I definitely see it as a nice, consolidated, set of broader historical data that anyone can load. It would be a shame to make this available only in a platform that users such as myself could not easily access.

One suggestion: in the documentation, provide the descriptive names of the data sets from the Ken French web site, not just the file names, as it's the descriptive names we see when perusing the web site (unless I'm missing something).

I am really happy that you've done this work in Google Sheets, and hope others will follow your lead in creating and sharing spreadsheets that are usable by a wider audience on a broader set of platforms!

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Fryxell wrote:I'm actually a bit surprised no one had modified Simba's spreadsheet to incorporate the pre-1972 data.
I started to do so, then realized that there might be an issue with that, and ended up doing my own private Excel spreadsheet for various types of historical backtesting models. Please check your PMs, I'll explain the issue.

In any case, I love your work, this is terrific.
cully
Posts: 2
Joined: Tue Oct 20, 2015 2:39 pm

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

Post by cully »

Is the Google Sheets backtesting spreadsheet available to download. Seems like the link was removed?
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

cully wrote:Is the Google Sheets backtesting spreadsheet available to download. Seems like the link was removed?
The author removed it due to IPR concerns on some of the data. He wanted to think more about it, don't know what happened since then.
Post Reply