Intraday data: Quantopian vs. Bloomberg

Good morning everybody,
I'm quite new to Quantopian and first thing first, I'd like to say it's an amazing tool with an extremely helpful blog.

I'm a Bloomberg user and spend often time doing excel based analysis and strategies backtesting due to my strong familiarity with both their product and data and Excel.

This is what I've noticed. I've taken the SPY and analyzed the "daily" returns from 1/4/2011 to 7/24/2014.
With daily returns though, I mean daily return from open to close:( (daily Close Price)/(daily Open price) - 1)*100.
That would represent how much an investor would make buying at the open and closing at the close each day.
The sum of these "daily" returns using Bloomberg data over the above mentioned time period is 24.51%.

I'm attaching an algo that tries to do the same using Quantopian data.
My disappointment is to see that my variable called "context.cum_ret" at the end of the algo is = 9.69%.
I understand there are trading costs involved, but I struggle to believe that could justify such a huge difference.

My question for you is: am I doing something wrong with my algo?
Many thanks,

Angel

5
Backtest from to with initial capital
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
 Returns 1 Month 3 Month 6 Month 12 Month
 Alpha 1 Month 3 Month 6 Month 12 Month
 Beta 1 Month 3 Month 6 Month 12 Month
 Sharpe 1 Month 3 Month 6 Month 12 Month
 Sortino 1 Month 3 Month 6 Month 12 Month
 Volatility 1 Month 3 Month 6 Month 12 Month
 Max Drawdown 1 Month 3 Month 6 Month 12 Month
# Backtest ID: 5842c77afc55c2624b704e1e
There was a runtime error.
5 responses

I think you used arithmetic returns rather than log returns. One cannot sum arithmetic returns to get total returns. Use log return instead. A simple example:

start with $100 gain 10% so now there is$110 ($100 +$10)
loose 10%
so now there is $99 ($110 - $11) If one simply added a 10% gain to a -10% gain then one would expect a net 0% gain. Wrong. Now do this with log returns... log(1 + .1) + log(1-.1) = .041 + (-.046) = -.0047 return = 1 - 10^(.0047) = 1 - 1.01 = -.01 That's the correct answer. -1% Maybe watch this video https://www.youtube.com/watch?v=e-C_QwBl3-8 Using the SPY returns from 1/4/2011 - 7/24/2014 from Yahoo, the sum of the arithmetic daily returns is 24% which is probably what you calculated. However, the correct way is to use log returns which yields 10%. Attached is a simple algorithm which buys SPY at the open each day and sells it at the close. The backtest results for that period are 10.19% total return. Pretty good agreement with what was expected (using log returns). Note, the backtest subtracts a little each day for commissions but then also adds in dividends which is one reason why this doesn't match exactly (also can't buy at exactly the open and close). As a side note, use the "schedule" function rather than comparing the times. Your algorithms will run faster and be easier to read. Dan 2 Loading... Backtest from to with initial capital Total Returns -- Alpha -- Beta -- Sharpe -- Sortino -- Max Drawdown -- Benchmark Returns -- Volatility --  Returns 1 Month 3 Month 6 Month 12 Month  Alpha 1 Month 3 Month 6 Month 12 Month  Beta 1 Month 3 Month 6 Month 12 Month  Sharpe 1 Month 3 Month 6 Month 12 Month  Sortino 1 Month 3 Month 6 Month 12 Month  Volatility 1 Month 3 Month 6 Month 12 Month  Max Drawdown 1 Month 3 Month 6 Month 12 Month # Backtest ID: 5843155da15563623c07cc4c There was a runtime error. Hi Dan, Many thanks for your reply much appreciated. I understand what you mean but, I apologize if I'm wrong, my code reinvests the same amount every day. That's why for the time being, I sort of ignore the return displayed on the top hand side. Right now the logic is: Day 1 invest$10000 at market open (or any other amount) and sell all the stocks at the end of the day. Say it makes 1% so you have a +$100 pnl. Day 2 invest again$10000. If today the open/close return is -1%
Then the amount of dollar money made so far is zero.

Even if $10000 doesn't match the total cash available in the portfolio, constantly "investing"$10000 every day should give me a return equal to the sum of daily open/close returns, no?

And that's where I'm concerned. If this is correct then we should be much closer to 24% than 10%.

Thank you all in advance for your time and suggestions,

Angel

Attached is an algorithm which constantly invest \$10,000 a day in SPY. Both the running return (see the logs) and the backtest results are 21%. This is closer to your 24% (and not entirely sure why they aren't exactly 24% except maybe rounding or not getting the exact open and close prices).

The big problem (and I don't exactly get what you are trying to do) is that you are constantly buying context.order_amount_aa (which you set at 20,000). Whether you have the cash or not, this amount is being ordered. If you don't have that much in cash then the backtest engine assumes this is somehow being added to your account. The backtest engine uses this added cash in it's portfolio value calculation. It actually thinks of it as negative because you are "borrowing" that amount. I think therefore part of the problem might start in line 98

    context.pnl_val = context.portfolio.portfolio_value - context.portfolio_prev_val


You use the portfolio_value to then go on to calculate return. However, the portfolio value includes the cash you put in . Rather than use portfolio_value use the actual sell price. This is sort of hard to get (believe it or not). In my code I assumed the sell price was the last sale price of SPY when I placed the order (context.portfolio.positions[context.security].last_sale_price).

Also, note however that a buy and hold strategy would have netted 66% return.

2
Backtest from to with initial capital
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
 Returns 1 Month 3 Month 6 Month 12 Month
 Alpha 1 Month 3 Month 6 Month 12 Month
 Beta 1 Month 3 Month 6 Month 12 Month
 Sharpe 1 Month 3 Month 6 Month 12 Month
 Sortino 1 Month 3 Month 6 Month 12 Month
 Volatility 1 Month 3 Month 6 Month 12 Month
 Max Drawdown 1 Month 3 Month 6 Month 12 Month
# Backtest ID: 58434279301cd26242177f00
There was a runtime error.

Thank you Dan, you've been incredibly helpful.
Reason why I've been so focused on perfectly capturing daily opens and closes is the following and perhaps you may be interested in helping me to write the code of this strategy.
Using historical data in Excel, I've found this very interesting strategy (returns below exclude commission and slippage costs).

Security analyzed: SPY. Time range 12/31/02 - today.
Every day I check the prior day's High/Open return. (High/Open -1)*100.
If it was not equal or above 0.6% (I found this value through tons of VBA/Excel based simulations), then today I trade.
This means I buy today at the market open and sell at the close.

Now, today though is not based on a simple buy at open and sell at close.
Today I have in place a stop loss at -2% so that if the price crosses that threshold, I sell and close the position.
Also, if the price crosses (equal or greater than) 0.6% then, I move my stop loss to 0.6%.

So if the price will never revert back below 0.6%, I will make a profit equal to wherever the price will close for the day (above 0.6%), otherwise I should get 0.6%.

Last scenario is when the strategy goes long and that day the price doesn't cross neither the stop loss or the 0.6% threshold (that I call take profit level).

If today the price crosses the 0.6% level, then next day we won't trade anymore and the strategy will observe the market.
The strategy will be launched again right after one day when the high doesn't cross the 0.6% barrier.

Over that time period, SPY made 228% and this strategy made 163% however never going underwater.

If you change the time period from 12/31/07 to today:
SPY return: 81% ish
Strategy return 132%.

The strategy seems to be very consistent and less volatile.
The problem is that it relies a lot on precise intraday prices and I'm having really a hard time to implement it.

When comparing the same strategy between Quantopian and other data sources, keep in mind that Quantopian's "close" is not the "official close" that most other data sources report. Rather, it is the price of the last trade before 16:00 NYC time. In other words, Quantopian models a market order executed at 15:59:59, as opposed to participation in the closing auction.