A reader comments on trading using Excel VBA and Factor Model

Thoughtful comments from a reader John S. from the UK on his experience with trading technology and models:

"I have been developing my own personal automatic trading systems using Excel VBA and based on rules I have developed over the years as an active private trader investor using both technical and fundamental data analysis.

One of the key merits in adopting an automatic trading system approach that has helped me is to avoid the temptation for manual interference and thereby improving profitability by maintaining consistency. I have found the challenge of developing a successful system very rewarding from a personal perspective as I recognise that there are many that have tried and failed. However one problem I have encountered is my ongoing desire to regularly modify and improve the system which I have found can become counter productive as there is a real danger that system development becomes an end in itself! I just can't seem to stop tinkering as soon as I come up with a new idea or feature!

One advantage of using Excel VBA that I have found is that it is inherently flexible as it facilitates the processing of data which can be important especially when using fundamental data as part of the system. In this respect I recognise that every trader is trying to build in an edge that will make the system more profitable. I have noticed that many traders seem to only focus on price by trying to seek an edge by looking at special indicators or combination of indicators etc. Combining price data analysis with a Factor Model approach is a challenge which is ideally suited Excel VBA as it can be easily used to process both fundamental and macroeconomic data into a form that can be integrated with price data analysis.

I recognise from your book that Matlab is more powerful than Excel VBA and may be just as flexible in integrating fundamental and macroeconomic data but I just wanted to draw your attention to benefits I have found using Excel VBA which may suit those who like myself are more comfortable in using Excel VBA and are reluctant to change. Other features that can be exploited that I have found helpful when back testing are automatically producing Price Charts that incorporate Entry and Exit points which provides visual reassurance that the system is working as intended as well as generating automatic Word reports recording key output for future reference.

I am sorry if I sound too much like an advert for Microsoft!"

Mean-reversion is getting stronger

As I mentioned in various previous blog posts, (e.g. see here), I believe mean-reversion strategies have been performing very well in the last year. Now here is an article (hat tip: Laurence) that provides concrete analysis to support this hypothesis. In fact, the author points out that most of the mean-reversion in recent years comes from the overnight close-to-open reversal.

A new service for retail investors

Here is a new low-cost service called Alerts4All that offers technical trading signals for retail investors. You can, for example, have an alert sent to you every time a "Double bottom" pattern occurs.

A much more advanced version of the service will be rolled out soon -- I saw a demo today where you can backtest your strategies online, combining different fundamental and/or technical variables as entry or exit signals. They also have some built-in models for you to adapt (e.g. a model based on The Little Book that Beats the Market by Joel Greenblatt.) More interestingly, you can look at other people's trading models and their historical and/or real-time performance.

Matlab or Alphacet it is not, but I think it will be quite useful for many retail traders. It might even be useful to professional traders who want a quick-and-dirty way to test out ideas.

Trader tax proposal will be the death knell for statistical arbitrage

U.S. Congressman Peter DeFazio, introduced H.R. 1068: “Let Wall Street Pay for Wall Street's Bailout Act of 2009”, which aims to impose a 0.25% transaction tax on the “sale and purchase of financial instruments such as stock, options, and futures.

Ladies and gentlemen, 0.25% is 50 basis points round-trip. Few if any statistical arbitrage strategies can survive this transaction tax.

And no, this is not "Wall Street paying for Wall Street's Bailout". This is small-time independent trader-entrepreneur like ourselves paying for Wall Street's Bailout.

Furthermore, this tax will drain the US market of liquidity, and ultimately will cost every investor, long or short term, a far greater transaction cost than 0.25%.

If you want to stop this insanity, please sign this online petition.

Finding seasonal spreads

I am pleased to introduce guest blogger Paul Teetor for today's article.

----------

Finding Seasonal Spreads

By Paul Teetor

A seasonal spread is a spread which follows a regular pattern from year to year, such as generally falling in the Spring or generally rising in October. To find seasonal spreads, I've been using ANOVA, which stands for analysis of variance. ANOVA is a well-established statistical technique which, given several groups of data, will determine if the groups have different averages. Importantly, it determines if the differences are statistically significantly.

I start with several years of spread data, compute the spread's daily changes, then group the daily changes by their calendar month, giving me 12 groups. The ANOVA analysis tells me if the groups (months) have significantly different averages. If so, I know the spread is seasonal since it is consistently up in certain months and consistently down in others.

The beauty is that I can automate the process, scanning my entire database for seasonal spreads. A recent scan identified the spread between crude oil (CL) and gasoline (RB), for example. The initial ANOVA analysis indicated the CL/RB spread is very likely to be seasonal. This bar chart of each month's average daily change demonstrates the seasonality. (Click on the graph to enlarge it.)

The lines show the confidence interval for each month's average. Notice how May and June are definitely "up" months because their confidence interval is entirely positive (above the axis). Likewise, November and December are definitely "down" months. For all other months, we cannot be certain because the confidence interval crosses zero, so the true average change could be either negative or positive. The conclusion: Be long the spread during May and June; be short during November and December.

For more details, please see my on-line paper regarding ANOVA and seasonal spreads.

- Paul Teetor

The limitation of Sharpe ratio

Just as one should not trust VaR completely, one should also beware of high Sharpe ratio strategies. As this Economist article pointed out, a strategy may have a high Sharpe ratio because it has so far been accumulating small gains quite consistently, but it could still be subject to a large loss when black-swan events strike.

Personally, I am more comfortable with strategies that do the opposite: those that seldom generate any returns, but always earn a large profit when financial catastrophes occur.

The peril of VaR

This Quebec pension fund lost some $25 billion due to non-bank asset-backed commercial paper (ABCP). Their Value-at-Risk (VaR) model did not take into account liquidity risk. As usual, the quants got the blame. But can someone tell me a better way to value risk than to run historical simulations? Can we really build risk models on disasters we have not seen before and cannot imagine will happen?

(Hat tip: Ray)

Kelly formula revisited

Some discussions on Kelly's formula with a reader Steven L:

Q:

"I am more than half way through your book and am stuck at a concept that I can't seem to find an answer in any other forum.

I have read Ralph Vince's "Portfolio Management Formulas," which uses Kelly's formula to calculate an optimal "fraction" of the bankroll to bet on each trial. So a trader can calculate a fraction of his total trading account value to risk on each trade. What I am referring to is the so-called "fixed-fractional" trading. There exists an optimal fraction that will maximize the geometric growth rate of the trading equity, in theory anyway.

However, in the money management chapter of your book, you use Kelly's formula to derive an optimal "leverage." This seems to be in conflict with what I learned from Ralph Vince, since leverage is usually great than unity and fraction is usually less than unity. I can't seem to make a connection between these two concepts. I have also seen the same optimal leverage formula in Lars Kestner's Quantitative Trading Strategies and asked the same question on some forums, but no one was able to give me a clear satisfactory answer. It would be greatly helpful if you can help me sort out the confusion."

A:

I don't have Ralph Vince's book with me, but if I recall correctly, his formulation is based on discrete bets (win or lose, no intermediate outcome), much like horse-betting or in a casino game. My approach, or rather, Professor Ed Thorp's approach, is based on continuous finance, assuming that every second, your P&L could fluctuatate in a Gaussian ("log-normal") fashion.

For discrete bets where you could have lost all of your equity in one bet, surely one should only bet a fraction of your total equity. For continuous finance, there is very little chance one could have lost all of the equity in one time period, due to the assumed log-normal distribution of prices. Hence one should bet more than your equity, i.e. use leverage.

Q:

In example 6.2 in your book, the portfolio consists of only long SPY, which has little chance of going to zero. So I can see how it is reasonable that you use the continuous finance approach and apply the optimal leverage to scale up the return.

But let's assume that the portfolio consists of a single strategy that buys options. Suppose this strategy will lose most of the time due to time decay but will make profit once in a while due to black-swan events. I don't think it's a good idea to bet the entire portfolio equity on each trade for this strategy. Can you still apply the continuous finance approach in this case, since in reality trading is like making discreet bets? Should we expect the mean and variance of this strategy automatically result in an Optimal Leverage that is less than one? So that we actually need to risk a fraction of the account equity per trade?

A:

The formula I depicted in the book is valid only if the P&L distributions are Gaussian. If one expects a fat-tailed distribution due to black-swan events, a different mathematical model needs to be used, though it can still be within the continuous finance framework. However, for simplicity's sake, if the distribution looks multinomial (e.g. high probability of "Win a lot" v "Lose a lot"), then you may model it with fractional betting just like a casino game.

Algorithmic Trading Technology Update

Lately a number of new (at least to me) technologies useful to the algorithmic trader came to my attention:

1) Matlab2IB API

I said in my book that it is difficult to use Matlab as an execution platform. As Max has pointed out, this is no longer true. This inexpensive API connects Matlab to your Interactive Brokers' account. It allows you to retrieve historical data, get real-time quotes, and send orders. In other words, all the basic functions you need to create your own execution engine.

2) R

Many people (hat tip: Steve H.) know that R is an open-source (i.e. free) alternative to Matlab. I find that there is also an API that connects R to Interactive Brokers, though I have not tried it myself.

3) Trade Ideas

Trade Ideas (hat tip: Russell M.) is a complete automated trading platform that provides connections to different brokerages (scottrade, IB, TD Ameritrade, etc.)

4) Amazon EC2 cloud computing platform

Running out of PC's to run your myriad strategies? Try Amazon's EC2 cloud computing platform. For a modest hourly fee, you get access to an instance of either Linux or Windows environment, and you can add as many instances as you want. The connection speed is supposed to be at least 10x T-1 line, well-suited to high frequency traders . Here is some other performance benchmarks.

Hedge funds move to "easy-to-understand liquid strategies"

See this interesting article (registration required) on FT on the state of the hedge fund industry. Paul Tudor Jones, Citadel, and Fortress Investment Group are all said to be moving to "easy-to-understand liquid strategies", otherwise known as "statistical arbitrage".

(By the way, I have been urging traders to do just that in my book.)

How is the job market for quants these days?

Felix Salmon claimed in this post (hat tip: J. Rigg) that the quant job market is alive and well. However, I haven't heard much from the usually diligent headhunters in the last few months, which doesn't bode well. Maybe some of our readers can comment on the current state of the quant job market?

In that same post, Felix wondered whether to incorporate the extraordinary period of 2008 as part of backtesting data. Actually, I don't see much of a problem here -- of course one should include 2008. The only reason a trading model would have performed poorly in 2008, as opposed to 2006, 2007 or 2009, would be that its parameters are fitted too tightly to historical data. If you try out some parameterless trading models like I advocated, 2008 is not that unusual except for its higher volatility.

The enduring profitability of mean-reversion strategies

Some readers have doubts about my assertion that mean-reversal models continue to be very profitable during this whole year of financial and economic disasters. So I backtested the mean-reversion strategy in Example 3.8 of my book with the most recent one-year SP1500 data. Without transaction cost, the Sharpe ratio is 4.8. Even after subtracting 10 b.p. round-trip transaction cost, it is still at 3.5.

Since the strategy was constructed over a year ago while I was writing the book, this most recent backtest is done on unseen data, with absolutely no look-ahead bias!

Josh Brolin on day trading

Actor Josh Brolin ("Milk", "W", "No Country for Old Man") said on Charlie Rose that his trading portfolio had a 57% return this year. His strategy is based on entering on reversal from a trend. Sounds pretty reasonable to me: maybe some of our readers can backtest this.

Below is the full interview, beginning with Sean Penn, then goes on to Gus Van Sant, then finally Josh Brolin mentioned his day-trading at the very end of the 1 hour show.



Interested? He is starting a multi-million dollar hedge fund to manage your money.

My book on Quantitative Trading is published

My book on Quantitative Trading has been published and is now available from Amazon.com. Many thanks to all of you for your ideas, comments, and support!

Some riskless profit, and why it exists

Numerous commentators have pointed out the enormous yield spread between agencies debt (Fannie/Freddie) and US Treasuries.

Here are some links kindly provided by a reader: 10 yr Fannie/Treasury, 5 yr Fannie/Treasury, 10 yr Freddie/Treasury, and 5 yr Freddie/Treasury.

Currently their spreads are above 150 bp. Since the US government has nationalized Fannie and Freddie, this 150 bp is a riskless profit. As the blog Accrued Interest has pointed out, one reason this riskless profit exists is hedge fund deleveraging: nobody has the risk appetite to arbitrage this spread at a meaningful scale.

Brad Setser, a blogger at the Council of Foreign Relations, suggests that the Chinese government, who does have a lot of cash to benefit from this high yield, should go ahead and buy up these agencies debt. However, if you read the Chinese blogs and online comments, there is enormous internal pressure for the government to spend some of this money on infrastructure projects, social security, health care, etc., so I doubt that the Chinese government will have stabilizing the US mortgage market at the top of its agenda. As a result, arbitrageurs out there should have no fear that this opportunity will disappear any time soon.

How does the financial crisis affect quantitative trading?

Now that we are reasonably sure the financial world is not coming to an end yet, it is reasonable to ask how quantitative strategies have been faring under this extreme market stress. Despite reports of massive hedge fund deleveraging and negative YTD returns, I believe quantitative strategies, especially statistical arbitrage, have survived the period relatively unscathed. But here are a few of my thoughts:

1) The paltry 10% annual returns that a mediocre statarb fund can deliver is suddenly looking pretty good when the risk-free rate is under 1% and a prolonged bear market is on the horizon.

2) Mean-reversal models continue to beat momentum models in this crisis environment, as in previous crisis environments. This is not surprising because market returns have completely dominated specific returns, and of course market returns have been highly mean-reverting lately.

3) Models involving shorts are under some tumoil because of regime-change induced by new and ever-changing short-sale regulations. (For a while, I even have difficulties locating SPY for hedging purposes!)

4) Models are generally trained on data with far lower volatility than is recently realized. (Even incorporting VIX in a model does not guarantee that it can match realized volatility any better.)
As a result, P&L's fluctuations are also much higher than usual, which induces deleveraging as a risk-management measure, which drains liquidity from the market, which in turn leads to still higher volatility. The usual viscious cycle.

5) Political risks in an election year have further reduced leverage and increased volatility. What if there is an assassination? What if the wrong party got elected? What if the paper-trailess electronic voting machines cause another dispute for a month? The nightmares will continue at least until the morning of Nov 5.

6) Normally, lack of liquidity in the market is good for statarb models since they profit from renting out temporary liquidity. However, this profitability assumes that there are buyers of last resort for the market: the long-term investors, the mutual funds, Warren Buffet, etc. When they are absent, statarb investors can be left holding the bag. Fortunately, Warren Buffet & Co. has indeed stepped in and we statarb traders can breathe a sigh of relief.

7) I have been paying particular attention to 3 websites since the crisis began in order to judge whether I should return to my normal leverage: the Ted spread (I am waiting for it to return to below 2), the Calculated Risk blog, and Paul Krugman's blog. This crisis is caused by panic in the credit market, so we should look for credit market returning to normal before declaring victory. The VIX? Not so much because I believe it is backward-looking in this environment.

8) Watching Fannie, Freddie, Lehman, AIG, WaMu, Wachovia, Iceland, and the initial bailout bill failed feels like reading Chapter 8 of Harry Potter and the Deathly Hallows: "The Ministry has fallen. Scrimgeour is dead. They are coming." The Dark Lord is taking over our economy.

Webinar on algorithmic trading system

Recently I participated in a webinar on using an algorithmic trading system called Alphacet Discovery. A link to the webinar can be found here. Some previous research using this system was described here.

Index change strategy

Some years ago, I traded a simple index change strategy: buying stocks to be added to the SP500 index at the market open right after the index change announcement and exiting the position at the close, and similarly shorting stocks to be deleted. The results were mediocre at best.

However, new research by University of Edinburgh Business School suggests that a similar strategy works well for FTSE350 stocks (Hat tip to J. Rigg for the link). The trick is to predict which stocks are to be added or deleted 30 days before the announcement ("review date"), buy/sell the stocks, and close out the positions just before the review date.

Since the criteria for inclusion in the FTSE index is well-defined (and primarily based on market capitalization), it should not be hard for the interested traders to make their own predictions and profit from this rebalancing.

Behavioral finance we can all use

In their new book "Nudge: Improving Decisions About Health, Wealth and Happiness", U of Chicago economist Richard Thaler (of behavioral finance fame) and Harvard law professor Cass Sunstein gave a few pieces of personal finance advice, one of which coincided with my point in a previous post: buy insurance with the largest deductible available. The others are: don't invest much in your employer's stock, don't pay points on mortages, and don't pay for extended warranties. The book is reviewed in the NYT Book Review.

Predicting SP500 futures using investor sentiment

Ronald Domingues, an economics graduate student, has done an interesting study of how well a group of qualified investors with superior skills can predict the movement of the SP500 index. This group of qualified investors are selected by their track records of making correct predictions, and as a result of submitting their predictions going forward, they are eligible to be notified of the average predictions of other qualified investors, thus enabling them to make a better informed investment decision.

In other words, the elite will benefit from the collective wisdom of other elites -- sort of like the real world, isn't it?

How well does it work in practice? Well, they correctly predicted whether SP500 index will go up, down, or flat, a whopping 65.2% of the time. The details can be found on his website, where you can also sign up to see if you can join the elite.