Creating synthetic data for leveraged ETFs

I wanted to test a strategy using leveraged ETFs but most of them have only been in existence for a few years. There wasn't enough data for proper backtesting. I found this site where JW from Trendxplorer had posted a really nice AFL that does just that:

Thank you so much JW!
http://forum.amibroker.com/badges/2/member?username=trendxplorer

1 Like

The code from the link above is great for creating synthetic data, however it will only create the close prices. I'd like to get it to create the open, high, low and close prices. I've made an amateur's attempt to modify the code. It will give the correct close prices but not the correct open high or low prices. Does anyone have any suggestions how I might be able to do it? Below is a copy of the original code and below that is my modified code:

Original Code (Creates synthetic close prices only):

// --- ATC_SyntheticSymbol.afl ---
//
// --- colophon ---
//
// code by TrendXplorer
// trendxplorer@gmail.com
// www.trendxplorer.info
// version: December 31, 2014
//
// --- instructions for usage ---
//
// 1. Set name and leverage for composite ticker on parameters setting for analysis. For SDS, Leveraged Source= SDS, Name Target Symbol = ~SDS, Leverage Factor = 1, Invert Price Data = Yes
// 2. Run scan on ticker with longest price history: base ticker, i.e. SPY, use daily setting
// 3. On Analysis, adjust Apply to *Current and Range: All quotes. Finally to create the ETF, hit Scan and the symbol is created in a blink of an eye.
//
// -------------------------------

// --- inputs ---
_levSource      = ParamStr( "Leveraged Source:"   , "SSO"                  );
_targetSymbol   = ParamStr( "Name Target Symbol:" , "$"                    );
leverage        = Param   ( "Leverage Factor:"    , 2.0, -3.0, 3.0, 0.0001 );
invert          = ParamToggle( "Invert Price Data", "No|Yes", 0            );

// --- calculations ---
invert          = 1 - invert * 2; // value for "No": 1, value for "Yes: -1
baseChange      = -1 + Close / Ref ( Close, -1 );
targetChange    =  1 + baseChange * leverage * invert;
levData         =  Foreign( _levSource, "C" );

// --- AddToComposite statements are for Analysis -> Scan ---
if ( Status("action") == actionScan )
{
	for ( bar = BarCount; bar > 0; bar-- )  // from most recent bar to first bar of base symbol
	{
		if ( NOT IsNull( levData[bar-1] ) )
			targetData[bar-1] = levData[bar-1];
		else
			targetData[bar-1] = targetData[bar] / targetChange[bar];
	}
	AddToComposite( targetData, _targetSymbol, "X", atcFlagDefaults );
	Buy = 0;  // required by scan mode
}
SetOption("RefreshWhenCompleted", True);

// --- generate columns for strategy exploration ---
AddColumn( Close                     , "Close"        , 3.3 );
AddColumn( baseChange * 100          , "BaseChange%"  , 3.3 );
AddColumn( ( targetChange - 1 ) * 100, "TargetChange%", 3.3 );
AddColumn( levData                   , "OriginalData" , 3.3 );
if ( Status( "action" ) == actionExplore ) SetSortColumns( -2 );
Filter = 1;

// --- end of code ---

Modified Code (trying to get it to create Open, High, Low and Close prices):

_levSource      = ParamStr( "Leveraged Source:"   , "SSO"                  );
_targetSymbol   = ParamStr( "Name Target Symbol:" , "$"                    );
leverage        = Param   ( "Leverage Factor:"    , 2.0, -3.0, 3.0, 0.0001 );
invert          = ParamToggle( "Invert Price Data", "No|Yes", 0            );

// --- calculations ---
invert          = 1 - invert * 2; // value for "No": 1, value for "Yes: -1
baseChangeO      = -1 + Open / Ref ( Open, -1 );
baseChangeH      = -1 + High / Ref ( High, -1 );
baseChangeL      = -1 + Low / Ref ( Low, -1 );
baseChangeC      = -1 + Close / Ref ( Close, -1 );
targetChangeO    =  1 + baseChangeO * leverage * invert;
targetChangeH    =  1 + baseChangeH * leverage * invert;
targetChangeL    =  1 + baseChangeL * leverage * invert;
targetChangeC    =  1 + baseChangeC * leverage * invert;
levDataO         =  Foreign( _levSource, "O" );
levDataH         =  Foreign( _levSource, "H" );
levDataL         =  Foreign( _levSource, "L" );
levDataC         =  Foreign( _levSource, "C" );

// --- AddToComposite statements are for Analysis -> Scan ---
if ( Status("action") == actionScan )
{
	for ( bar = BarCount; bar > 0; bar-- )  // from most recent bar to first bar of base symbol
	{
		if ( NOT IsNull( levDataO[bar-1] ) )
			targetDataO[bar-1] = levDataO[bar-1];
		else
			targetDataO[bar-1] = targetDataO[bar] / targetChangeO[bar];
			
		if ( NOT IsNull( levDataH[bar-1] ) )
			targetDataH[bar-1] = levDataH[bar-1];
		else	
			targetDataH[bar-1] = targetDataH[bar] / targetChangeH[bar];
			
		if ( NOT IsNull( levDataL[bar-1] ) )
			targetDataL[bar-1] = levDataL[bar-1];
		else
			targetDataL[bar-1] = targetDataL[bar] / targetChangeL[bar];
			
		if ( NOT IsNull( levDataC[bar-1] ) )
			targetDataC[bar-1] = levDataC[bar-1];
		else
			targetDataC[bar-1] = targetDataC[bar] / targetChangeC[bar];
	}
	AddToComposite( targetDataO, _targetSymbol, "O", atcFlagDefaults );
	AddToComposite( targetDataH, _targetSymbol, "H", atcFlagDefaults );
	AddToComposite( targetDataL, _targetSymbol, "L", atcFlagDefaults );
	AddToComposite( targetDataC, _targetSymbol, "C", atcFlagDefaults );
	Buy = 0;  // required by scan mode
}
SetOption("RefreshWhenCompleted", True);
1 Like

Hello Marcel
i read the original code and i can see that line

	AddToComposite( targetData, _targetSymbol, "X", atcFlagDefaults );

so if you read the manual says that "X" - updates all OHLC fields at once

field codes: "C" - close , "O" - open, "H" - high, "L" - low, "V" - volume, "I" - open interest, "1" - Aux1 field, "2" - Aux2 field,
"X" - updates all OHLC fields at once

I didn’t run or test the original code but for me has exactly what you need and you DONOT need to modify anything according your question

1 Like

Thank you so much @PanoS. It does put data in all the fields, but it puts the same close data in each field so that the Close=Open=High=Low. I'd like it to put the different respective values in the OHLC spots.

Hello
Just came back of work and I try to run your Modified code.
In my database I donot have "SSO" so I had to put other ticker (^GDAXI)

_levSource = ParamStr("Leveraged Source: " , "^GDAXI");

Our next step BEFORE run Scan….. ALLWAYS go to Analysis Parameters ( in my case I had to press the Button “Reset All” then I run the Scan.)

Magic… :grinning: :grinning: :grinning:
I did run your Modified code in One ticker and also in watchlist and I DIDNOT see any problem all O,H,L,C was ok.

have a nice time.

2 Likes

Yes, with the modified code I can get the proper OHLC up until the last bar available of the original ETF but before that it won't work. (around 2008 for SPXL) The code is basically just copying the original SPXL data back to 2008 and putting it into the synthetic composite symbol. I did some studying about it today and I realized that if you calculate the OHLC independent of each other, they'll wander from each other and you'll end up with the high lower than the open etc. You have to do something like extrapolate just the close price and then calculate the open, high and low from the extrapolated close.

I looked all over the net for any kind of calculation for leveraged ETFs and just found some very vague information. The ETF companies say that for example with 3X leverage, they try to come close to 3X the daily change of the associated index with futures etc but it isn't exactly 3X. So I couldn't find any mathematical formulae. There is also of course daily rebalancing which in most cases means over the long term, you'll lose money with a leveraged ETF.

1 Like

Marcel and others-

Did you figure out how to get historical synthetic data for 3x ETFs (daily open, high, low, close prior to ETF inception derived from index it is based on)?

I spent time following https://wiki.marketchronologix.com/kb/synthetic-data but it either makes one piece of data invalid (open), or starts wandering off and no longer makes sense after many years.

I've downloaded daily quotes from Yahoo Finance for ^NDX and TQQQ and am trying to add TQQQ synthetic data prior to 2/11/2010 when TQQQ started trading. I got perentage changes for both tickers from one day to another for open, high, low, close, plus linear regression coefficients (around 2.9, close to 3x as expected) and intercepts from Excel. If I apply a formula to each of the 4 pieces of data (open, high, low, close) independently (as opposed to having the open as a reference), the prices diverge with time, and for example after 20 years will end up with quotes that are unrealistic:
date = 2/12/1990
open = 36.31
high = 3496.16
low = 21.26
close = 41.99

-Mark

1 Like

@mdwin01, no matter what you do, it's fairly difficult to make synthetic historical data for leveraged ETFs and the slight difference of the synthetic data will not give you accurate backtests. Why not just test and optimize your system to trade the normal unleveraged ETF and even trade it for real for a while with the normal ETF. If the system works well, then you could leverage it up by trading the 2X or 3X ETF. Keep in mind that if your profit is 10% a year, it will most likely translate to less than 30% a year for a 3X ETF. The bid ask spread and slippage will most likely be higher for the 3X ETF. Also, the longer you hold a 3X ETF, the more you'll likely lose from daily rebalancing. You should compare trading leveraged ETFs to trading with margin or trading futures.

2 Likes

@mdwin01 thanks for the interesting link and to @marcel for his input. If you repeat the simple calculations from Mark’s link you can compare the single vs the double vs the triple leveraged versions. I thought it was probably worthwhile adding the overnight difference (previous Close to current Open) to illustrate the concept and since Mark has also been posting about overnight trading.

Interestingly on most days the calculations are not bad but almost never do the actual prices traded exactly match the multiple of leverage that the ETF uses. Here is a simple example of a recent day (and I found that using the actual SP500 Index rather than the SPY ETF is even more problematic because the Index opening price is often not in-sync with the ETF’s prices).
image

Out of curiosity I wanted to see if these small differences average out so here is a 1,000 bar (daily data) average of those calculations.
image

If you do not create synthetic data you are content with, in addition to @marcel’s suggestion, perhaps use the underlying SPY further back in time, and test your strategy using leverage (in AmiBroker’s AccountMargin settings)? Also the double leveraged SSO (and SDS) go back to mid-2006 so that should help evaluate your strategy too.

1 Like