Simba's backtesting spreadsheet [a Bogleheads community project]
Re: Spreadsheet for backtesting (includes TrevH's data)
I have to agree with Serbeer, 20 years of history really doesn't tell you much.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
Have you tried Portfolio Visualizer?Leif wrote: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.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.Kevin M wrote:Have you tried Portfolio Visualizer?Leif wrote: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.
Kevin
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
Thanks Kevin for the info on entering ticker symbols. I was not aware that was available.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
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.
Backtesting Google Spreadsheet
[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:
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.
Re: Backtesting Google Spreadsheet
yes, one would be interested
Luck is when Preparation meets Opportunity
Re: Backtesting Google Spreadsheet
Wow, that's pretty cool! Do share please.
Re: Backtesting Google Spreadsheet
Sure. I can always use another spreadsheet
It's not an engineering problem - Hersh Shefrin | To get the "risk premium", you really do have to take the risk - nisiprius
Re: Backtesting Google Spreadsheet
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)
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
Definitely I want to see your spreadsheet. Thanks
1210
1210
Re: Spreadsheet for backtesting (includes TrevH's data)
Would definitely like to see your spreadsheet. It would be good not to have to make a lot of changes each year.
Al
Al
Re: Backtesting Google Spreadsheet
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:
Kevin
So yes, please share.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.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
I too would love to see your spreadsheet. Thanks in advance!
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: Spreadsheet for backtesting (includes TrevH's data)
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)
Re: Spreadsheet for backtesting (includes TrevH's data)
I also would like to see your backtesing spreadsheet. Paul
-
- Posts: 9883
- Joined: Mon Sep 07, 2009 2:57 pm
- Location: Milky Way
Re: Spreadsheet for backtesting (includes TrevH's data)
Fryxell,
I am very interested!
I am very interested!
Best regards, -Op |
|
"In the middle of difficulty lies opportunity." Einstein
Re: Spreadsheet for backtesting (includes TrevH's data)
Hi all,
Thanks for the interest. I'm working on it...
Thanks for the interest. I'm working on it...
Re: Spreadsheet for backtesting (includes TrevH's data)
Anyone know where I can get monthly ST Treasury return data from 1972-1991?
Re: Spreadsheet for backtesting (includes TrevH's data)
You can pull Treasury yields from FRED (daily, monthly, weekly) and calculate the returns yourself: Treasury Constant Maturity - FRED - St. Louis Fed.leasters wrote:Anyone know where I can get monthly ST Treasury return data from 1972-1991?
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
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
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
Re: Spreadsheet for backtesting (includes TrevH's data)
Okay, I think I got it. Was making it way too complicated. Thanks for your help.
Re: Spreadsheet for backtesting (includes TrevH's data)
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 wrote:Anyone know where I can get monthly ST Treasury return data from 1972-1991?
Bactetst27 Google Spreadsheet
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:
Enjoy.
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.
Enjoy.
Last edited by Fryxell on Wed Sep 16, 2015 9:31 pm, edited 2 times in total.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
Can you change access to "anyone with the link can view"? I'd like to make a copy for evaluation.
Re: Spreadsheet for backtesting (includes TrevH's data)
I updated the links. Hope they work now.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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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):
becomes:
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.
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))
Code: Select all
=IF(ISERROR(CORREL($Data.$C$56:$C1001,$Data.B$56:B1001)),0,CORREL($Data.$C$56:$C1001,$Data.B$56:B1001))
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.LadyGeek wrote: Before I start looking at the formulas, my first approach is to make this work offline.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
You probably also want to replace them in row 99.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 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: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):
becomes:Code: Select all
=IfError(Correl(Data!$C$56:$C,Data!B$56:B))
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.Code: Select all
=IF(ISERROR(CORREL($Data.$C$56:$C1001,$Data.B$56:B1001)),0,CORREL($Data.$C$56:$C1001,$Data.B$56:B1001))
Code: Select all
IfError(FORMULA,"")
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.While I work on compatibility, can someone with a Google Drive account review the data and formulas?
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.Kevin M wrote: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.LadyGeek wrote: Before I start looking at the formulas, my first approach is to make this work offline.
I did have in the back of my head the idea of porting it as insurance in case Google mucked with its service.
Re: Spreadsheet for backtesting (includes TrevH's data)
The crux of the matter is the credibility of the source and the accuracy of transferring the info to the spreadsheet.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.
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.
Thanks for the explanation. I agree. It's much better to see that an error exists because something broke.Fryxell wrote:I use IfError to...
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.LadyGeek wrote: The crux of the matter is the credibility of the source and the accuracy of transferring the info to the spreadsheet.
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
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=2559I 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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Spreadsheet for backtesting (includes TrevH's data)
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.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.
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.
Re: Spreadsheet for backtesting (includes TrevH's data)
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...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.
"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,
Code: Select all
If(B32:B154*1>$B$12,
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)?
Re: Spreadsheet for backtesting (includes TrevH's data)
Thanks for pointing that out. I had not thoroughly checked this part of the spreadsheet since I had other unresolved issues here.LadyGeek wrote:
"Upside" and "downside" deviations are based on the Minimum Acceptable Rate. Please check your logic as you're missing one case.
Downside:Upside:Code: Select all
If(B32:B154<$B$12,
What happens when (B32:B154 = $B$12)? One of the above should include "=", e.g. "<=".Code: Select all
If(B32:B154*1>$B$12,
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)))
Re: Spreadsheet for backtesting (includes TrevH's data)
In Adjusted, I replaced B23 and B24 with the following (IfError removed):
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).
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)))
B24 = 0.0264
Column Z (last column) is showing #DIV/0 errors for Sharpe (Z20), Upside (Z24), and Time to Double (Z28).
Re: Spreadsheet for backtesting (includes TrevH's data)
I'm addressing this in a private message because I can't replicate the error you got.LadyGeek wrote:In Adjusted, I replaced B23 and B24 with the following (IfError removed):
B23 = 0.0232Code: 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)))
B24 = 0.0264
Column Z (last column) is showing #DIV/0 errors for Sharpe (Z20), Upside (Z24), and Time to Double (Z28).
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)
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)) )
If we want to divide by the total number of returns we should be dividing by:
Code: Select all
Count(B32:B)
CountIf(B32:B,"<"&$B$12)
Re: Spreadsheet for backtesting (includes TrevH's data)
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.Kevin M wrote: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.LadyGeek wrote: Granted, I lose the realtime GoogleFinance functions, but it doesn't seem that important for backtesting.
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 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?
Re: Spreadsheet for backtesting (includes TrevH's data)
Don't forget: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.
5. Usable on a Chromebook.
6. In general, usable on a much broader set of platforms.
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.2. Annual data back to the 1920s.
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.
Re: Spreadsheet for backtesting (includes TrevH's 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.Fryxell wrote:I'm actually a bit surprised no one had modified Simba's spreadsheet to incorporate the pre-1972 data.
In any case, I love your work, this is terrific.
Re: Spreadsheet for backtesting (includes TrevH's data)
Is the Google Sheets backtesting spreadsheet available to download. Seems like the link was removed?
Re: Spreadsheet for backtesting (includes TrevH's data)
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.cully wrote:Is the Google Sheets backtesting spreadsheet available to download. Seems like the link was removed?