excel Archives - Algorithmic and Mechanical Forex Strategies

Become an Algo trader in Baby Steps

April 4, 2016 by Lior Alkalay 10 Comments

The world of traders is divided into two groups; those who trade using algorithms and those who don’t. Those who trade using algorithms, aka algotraders, are well aware of the advantages of trading with an algo… And for those who don’t use algos? They are equally aware of the algo advantages but are reluctant to dive into its complexity; they are deterred from learning how. But the road to success in trading is not by avoiding challenges, but overcoming them, perhaps in baby steps.

First Baby Step to becoming an Algo trader

Let me ask you a question. What do you think is the first thing you’d need to do to become an algotrader? You might respond, “Learn programming, of course! How else?” Well, you’d be wrong.

If you plan to learn programming for the sole purpose of becoming an algotrader, you’re likely to get lost. Eventually, in despair or frustration, you’ll give up on algotrading.

There are numerous languages, from MQL to R to Python, and you have to decide which one to start with. You might find yourself wasting valuable time learning far too many trivial functions. It may take quite a long time before you even create a trading algo, let alone a profitable one.

But there’s another way, which I call reverse engineering.

The first step is to figure out which trading algo you want to make. In other words, what are the functions and strategy it should implement? Then you test it and finally, move on to the programming part. This way, you’re focused like a spotlight. You know exactly what your algo should do and can focus on the exact functionalities you need to learn to make it happen.

Everything will just come intuitively; which language, how and in what. All the pieces will fall much quicker into place because you already know what to look for.

The best way to start is by using a flowchart. It is actually one of the first things you learn in programming schools.

And what determines the flow? Of course, it’s the conditions. What we call the “ifs.” “Ifs” can be one condition or have many “ands” and “ors.” That’s how you decide what your algo should do in any given circumstances. The conditions to the algo are what the brain is to the body; they do the thinking.

Second Baby Step: Use Excel

Once you’ve made a flowchart of conditions, rather than using a complex tool, use Microsoft Excel or some other spreadsheet software.

Source: MT4

Extract historical data of a price data; start by using only the closing price. If you want to trade on a daily interval, extract daily data and so on. Use a separate column for each of the conditions in the flowchart, that way it’s easy to write and figure out. Add another column for a buy and sell output to mark when you “opened” and when you “closed” a position. And finally add one column of accumulated profit/ loss.

Use separate columns for each conditions and fill the chart. All you need now is to run a linear chart on one column, accumulated profit/loss, and look at the algo’s historical performance.

Once you master the basics you can move on to move advanced back-testing and curve fitting. But for a start that will do.

Finally, Learn to Program

Ok, you’re not a “baby” anymore; you have a good idea of what an algo trader needs to do. Now that you’ve got the concept down pat, you’re ready to begin learning programming.

In terms of which language to start with, that will depend on the circumstances. If you are already using an MT4 platform, it’s a no brainer; learn MQL which runs on MT4. The MQL website is filled with “how to” materials. And since you already have a diagram of your algo and an idea what you want to do, finding your way should be simple.

But if you trade with a different broker, you’ll have to decide which best suits. Personally, I recommend starting with MQL, just because it’s easier. Then, you can move into Python, which is a rather easy language to learn or C++ if your algo needs to work fast. If you algo is heavy on the math then perhaps R would fit.

Here are some places you can learn online:

Or, of course, you can learn from a book. I am more of a book person, but that’s just me.

Then there is another issue—API. API is the mechanism that enables your algo to communicate with your broker and execute your trades. Some brokers work better with certain programming languages than others. Most large brokers have communities and forums that can go into detail as to the best way to use an API.

Unlike the first two baby steps, no one would ever say that learning to program was a baby step. Rather, it’s more of a giant leap. It takes time to learn and to master, though it is worth it in the long run. Meanwhile, you can always use libraries of codes on the web for more complex algos. The thing is that once you’ve accomplished the first two baby steps, making the final leap into programming is a no brainer.

The Dimension of Time in Trading

July 9, 2015 by Lior Alkalay 6 Comments

The trading blogosphere is full of articles about the importance of timing your trade. Certainly, it’s important; identifying the right trend, knowing when to open or close – all integral to the trade. But here is the thing, there’s another dimension and timing is only half the story. You see, in the real world, it’s not just about the timing of your entry and exit but also the time between. In fact, the duration of your trade matters a lot more than you might think. In this article I’ll discuss the various aspects in which time can affect both short and long term strategies.

Duration and Returns

Let’s consider a theoretical trading situation, looking at two strategies. The first is a high frequency one designed at gaining 1 pip, with every trade taking a single second. We’ll call this Strategy A. The second strategy, Strategy B, is designed to generate a profit of 10 pips every 10 seconds. So which is the better strategy? On paper, both should generate 60 pips per minute hence equal returns. But in the real world? Well, that’s an entirely different story.

In real life, when you aggregate the results of the two strategies, you’re going to be in for a big surprise. Under Strategy A you had a profit of 50 pips after 1 minute. But under Strategy B you had a profit of 66 pips. Now which strategy do you think is better? Of course, you’d pick B. Interestingly, per trade, each strategy profited exactly as planned. Strategy A profited 1 pip and Strategy B profited 10 pips per trade.

So what made the difference? The time. As you can see below in the two charts, strategy A, when executed, took a little longer on average than initially expected. From the measurement of the duration of trades it clustered around 1.2 seconds per trade rather than 1 second. In Strategy B, the measurement of duration per trade, on average, is about 9 seconds. When executed, Strategy B trades took less time than initially expected. Eventually, at the end of the day, Strategy B returned 66 pips or 32% more than Strategy A’s 50 pips. That means Strategy B is the superior choice.

Now, let’s look at this a bit closer still. We’re going to take the duration of each of the trades executed and measure the standard deviation (a simple exercise in Excel). You would discover that in Strategy A, while the average might be 1 second per trade, the standard deviation is high. That suggests the cycles are not equal thus the strategy is less efficient. On the other hand, Strategy B has a low standard deviation, meaning most trades are close to 10 seconds. Therefore, it has more predictable returns and, hence, a more effective strategy. When you measure a strategy, understand that beyond returns and risk, the time each trade takes matters. And in fact, it can matter quite a lot.

Duration and Swing Trading

So now we understand how the nuances of time can affect the eventual returns of each strategy. Yet when it comes to longer duration trades (i.e. weeks or months) there is still another element. In this case, it is the amount of time the trade has been positive.

Let’s look at another theoretical scenario. This time, we have Strategies C and D, each has the identical duration of two weeks. Likewise, both have the same standard deviation of duration and roughly the same returns. But one strategy is riskier than the other. The question is which? The answer is the strategy that has been profitable for the least amount of time. Say you opened two trades; Strategy C was profitable for 12 of the 14 days it took to reach its target. Strategy D had been negative for the first 12 days before producing steep returns in the last 2. Strategy D, then, is clearly more risky because you are risking a loss for a longer time. This also suggests that, perhaps, the entry signal for Strategy D is not very well calibrated. Strategy C is clearly more effective and less risky, on average.

The Bottom Line

Of course, there are many more aspects to measuring time effectiveness in a trading strategy. There are also other dimensions to examine beyond just time. But the lesson here is clear. The next time you measure your own trading strategy remember that time isn’t just about timing.

Using Excel to Receive Real Time Forex Data from Yahoo! Finance

March 4, 2014 by Kalen Smith 9 Comments

“Procrastination has been called a thief,–the thief of time. I wish it were no worse than a thief. It is a murderer.”

Every Forex trader should live by this quote from William Nevins. Your opportunities deteriorate with every second that you delay making a decision. Traders that have access to real-time data have a huge advantage over the rest of the market.

There are some premium tools on the market, but you don’t need to invest in them. You can export real time data from Yahoo! Finance for free. I found a great VBA script that can do this for any currency pairs that you are trying to track.

Using Yahoo! Finance to Get Real-Time Forex Data

Many Forex traders use Yahoo! Finance to monitor currency prices. Unfortunately, the site isn’t perfect. The biggest limitation of Yahoo Finance! is that prices aren’t listed in real time, but Joshua Radcliffe has created a VBA script that gets around that. Here are some steps to use it to get real-time prices on currency pairs.

Open up Microsoft Excel
Click on the Macros tab and select the View Macros option
Create a name for your Macro in the box
Click Create
Add the code listed at the bottom of this section into the code editor
Change the values in currency 1 and currency 2 to the currency pairs that you would like to monitor. For example, you could set currency1 = “EUR” and currency2 = “USD” if you want to see the price between the dollar the euro. You can also keep the code as is and reference the values for the currencies in the cells shown in the code. However, my solution is easier if you are following a specific currency pair.
Click View Macros again to select the Run option
The real-time data will be shown in cell C9

This script will give you all the real-time data that you need including the market price, the ask price, the bid price, the 1-year target estimate and the beta-coefficient. You can run the program as many times as you would like.

Here is the code that you will need to add:

Sub Macro1()

‘

‘ Macro1 Macro

‘ Provided by Joshua Radcliffe

‘ www.JoshuaRadcliffe.com

Dim currency1 As String

Dim currency2 As String

currency1 = Cells(4, 3).Value

currency2 = Cells(5, 3).Value

Range(“B9:C12”).Select

Selection.ClearContents

With ActiveSheet.QueryTables.Add(Connection:= _

“URL;http://finance.yahoo.com/q?s=” & currency1 & currency2 & “=X”, Destination:=Range(“$B$9”))

.Name = “q?s=” & currency1 & currency2 & “=X_1”

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.WebSelectionType = xlSpecifiedTables

.WebFormatting = xlWebFormattingNone

.WebTables = “””table1″””

.WebPreFormattedTextToColumns = True

.WebConsecutiveDelimitersAsOne = True

.WebSingleBlockTextImport = False

.WebDisableDateRecognition = False

.WebDisableRedirections = False

.Refresh BackgroundQuery:=False

End With

End Sub

I have tested Radcliffe’s code for the CHF/JPY currency pair. The prices are slightly different than those listed on Yahoo! Finance. This shows that Daniel’s script works as claimed.

Applications of this Data

There are a couple of reasons that this data can be useful. First of all, you can use this script to get real time pricing data on your currency pairs. This gives you a significant advantage over traders that are relying on Yahoo! Finance’s charts, because they have a 15-minute lag before prices are updated.

You can also record prices throughout the day and use a variety of Excel tools to observe pricing trends. Run the macro and record the price data in a different cell each time. You can select all of the prices and use them to create a two-dimensional line graph in Excel. If you haven’t used Excel before, simply follow these steps:

Select the prices in the cell (they should all be organized in a column)
Click on the Insert tab
Click on 2D line to create a graph with the data that you selected
You can copy and paste the various charts that you create into a separate document where you can see them later

You can also run a regression analysis. You will need to go to the Excel options tab and click Analysis Tool Pack. You will then need to select Tools and then click Add –Ins. After you have followed these steps, you can click Regression Analysis from the Data Analysis tab.

You may want to monitor pricing trends near popular trading hours. I would recommend monitoring pricing trends between 8 GMT (3 AM EST) and 9 GMT (4 AM EST), because that is one of the most popular trading times. It will take some discipline to wake up that time every day if you are living in the East Coast on the United States, but every bit of knowledge is worth the sacrifice. You can gather approximately 20 data points during that timeframe to draw a trend line. This will give you a better understanding of the trading behavior of the rest of the community.

If you want to get particularly detailed then you can create separate line graphs for different days of the week. You may need a couple months to collect this data, but it will give you a significant advantage over other traders.

Are There Other Options to Get Real Time Data?

There are other tools available to get real-time price data on currency pairs. However, there are a couple of reasons that I would recommend this VBA program instead.

First of all, you don’t have to pay to use this script. This is a great advantage for beginning Forex traders that don’t want to invest a lot of money.

The script also makes it much easier to observe trends. Most other tools that offer real-time Forex data are streaming tools. They may help you make decisions based on the current trading price, but it can be difficult to copy the data and use it to create line charts.

Overall, I would recommend this script over any of the other real-time Forex tools on the market.

Scalper EA

March 5, 2013 by Shaun Overton 88 Comments

It looks like I accidentally stumbled into a scalping strategy. I posted the rules near the top of the page for those that don’t care about how I developed the expert advisor.

Warning: this EA cannot possibly profit on wide spreads. Do not follow this method if your broker’s spread and execution slippage average more than 2 pips.

Scalper EA Trading Rules

Chart: EURUSD 5 minute
SMA Period: 200
Moving Average Envelope: 1.0% of the SMA
Style: Counter-trend scalper

Entry rules

If the price crosses and closes below the lower envelope, then buy at market.

If the price crosses and closes above the upper envelope, then sell short at market.

Exit rules

If the price crosses and closes above the lower envelope, then exit long at market.

If the price crosses and closes below the upper envelope, then exit short at market.

Notice that the scalper strategy uses the same envelope for entry as it does for exit. The distribution of distance around the moving average is sticky when the price extends far away from the SMA.

The screenshot from NinjaTrader shows trades entering and exiting around the lower envelope

Get the code for MetaTrader 4 or NinjaTrader

Why the strategy works

The original intent for this research sought to uncover a range trading strategy based on the price crossing the moving average. Most traders think of distance in terms of pips.

Pips are valid for general context. The problem with modeling a strategy based on pips is that the the meaning of one pip’s movement changes over time.

Taking the idea to extreme lengths, the value of a pip in 1999 when the euro launched around 0.80 hardly compares with today’s price of 1.30. Keeping the discussion to percentages helps to fix the meaning of an idea like 100 pips over long periods of time.

I wanted to visualize how the price generally behaves relative to the moving average. A custom NinjaTrader indicator that my programming team wrote collects and analyzes the data in an Excel spreadsheet. Excel allows me to draw a graph of how frequently the price stretches away from the 200 period simple moving average.

Distribution of prices from SMA 200 on EURUSD M5

The frequency of percentage distances from the SMA 200 on the EURUSD.

The slope of the curve bends as the price extends further from the moving average. As you move left to right along the horizontal axis, the slope is steep until 0.75%. A kink in the curve forms at that point. The slope of the line flattens substantially from that point onward.

A big slope implies that the price will be anywhere but here on the next bar. A flat line means that the price isn’t likely to go anywhere. The distance is sticky at that level.

Scalping in a moving market doesn’t make sense. It’s only when we identify the sticky price condition of 1% away from the moving average that running a scalper EA makes sense.

Scalper EA Backtest results

I developed the strategy in NinjaTrader using data from 2011. My blind period was 2012, which was data that the strategy never saw in development. It was a pure walk forward test.

Results without spread costs

Profit: $5,740 on 108 trades trading 1 standard lot per signal
Profit factor: 2.18
Percent accuracy: 83.33%

NinjaTrader backtest, M5 EURUSD for 2011 without trading costs

Results with 2 pip spread costs

Profit: $1,420 on 108 trades trading 1 standard lot per signal
Profit factor: 1.24
Percent accuracy: 65.74%

A 2 pip spread substantially weighs on the performance

The scalper EA is incredibly sensitive to two assumptions: spread and slippage. I assume that anyone following this methodology trades at a reputable broker with good execution. The backtests assume that the combined cost of both spread and average slippage is 2 pips.

If your broker does not provide execution and spreads within that 2 pip window, then do not trade this strategy. I would not expect for you to walk away a winner.

Walk forward results without spread costs

Profit: $1,960 on 34 trades trading 1 standard lot per signal
Profit factor: 4.38
Percent accuracy: 88.24%

The NinjaTrader backtest shows walk forward results from 2012 on the EURUSD M5.

What is scalping?

Scalping refers to a short term trading style. Profits are very small and occur a large percentage of the time. When losses happen, they tend to be several times larger than a typical winner.

The high number of wins attracts traders of all stripes. The idea of consistently earning profits makes trading more fun and appealing. Traders with experience, which inevitably means traders that have suffered losses, also find the high percentage win rate appealing. It makes the emotional suffering far less difficult.

The emotional component that attracts traders to scalping strategies leads to illogical business decisions. Traders place the need to win frequently above the long term need to expect a profit.

Too many scalping expert advisors tap into the high winning percentage. Most fail to present a clear and obvious reason why it makes sense to scalp.

The EURUSD typically costs $2 to trade a mini lot. Many scalpers set narrow profit targets between 1-5 pips, which are worth $1-5.

The trader spends $2 to make $1-5. If this were a normal business, that would be the end of the game. You win. The game is only limited by the number of trades placed.

Trading, unlike other businesses, frequently results in losses. It’s very possible to build an expert advisor that could win trading for free but loses when costs enter the picture.

The best example is the difference in the scalper EA backtests for 2011. The first test showed a percent accuracy of 83% without including the spread. Adding the spread to the second backtest decreased the accuracy to 65%.

Trading costs make all the difference in scalping. Many scalping strategies live and die based on their trading costs.

The accuracy dropped because all trades had to subtract the spread cost from their simulated winnings. The number of trades that flipped from profit to loss because of a 2 pip spread shows how many trades profited by the narrowest of margins. The narrower the margin of profit, the more sensitive a strategy becomes to spread costs.

Do you think that I should have considered other ideas in the strategy? Suggest some ways to improve in the comments section below.

After-thoughts

This series eventually led to a profitable trading strategy. If you’d like to read through the journey, then I suggest reading the articles sequentially

The initial strategy idea
Selecting an appropriate time frame
A research plan
An annoying surprise in the initial backtests
An attempt at range trading
Range trading results
The moving average envelope scalper

Group Trading Strategy

January 4, 2013 by Shaun Overton 42 Comments

Francis D. from Australia likes to bounce different EA ideas off of me. He mentioned in the latest emails a fear of getting whipsawed from a signal that is either long or short.

This type of problem occurs all the time. I first encountered it with a simple strategy that fades price crosses over the moving average. Whenever the price crosses and closes below the moving average, go long. Shorts follow the same rules. It’s the kind of stupid simple range trading strategy that I always advocate.

Buy when price crosses below the 200 SMA. Sell and go short when the price crosses back above

The example above highlights the same thing that Francis complains about. The price floats around the moving average without going anywhere.

Random Outcomes

Trades based on the price crossing above the SMA come out near breakeven. Winners occur approximately 66% of the time and are one third the size of losers. Such a strategy neither makes nor loses money when ignoring trading costs.

The vast majority of the winners in the strategy are teeny tiny. The strategy encounters its maximum opportunity whenever it is closest to the SMA. The further away it goes, the more likely it keeps going the wrong way.

Flipping the trades still yields a random outcome. The only difference is that winners drop to 33% accuracy, but the average winner is now 2:1.

The Birth of a Trading Strategy

I always wonder how a scaling strategy might affect the outcome. If the strategy is most likely to win when the opportunity is smallest, what happens when the strategy attempts to reduce its position size as the market moves adversely?

Alternatively, what happens if it takes a pass on all the small winners and scales into positions? Yes, it will scale into losers, but it should also make resulting winners bigger. The question then becomes how to decide the rate of scaling and when to bail out of losers.

Thank you, Francis! A new blog series is born. Now that I’ve decided to scale into trades, I need to choose how and when to do it.

Nothing insightful or special jumped to mind. I’m a visual person, so I spent the better part of this afternoon creating the little chart in Excel using NinjaTrader. What starts out as a simple project always grows on itself. It took nearly 4 hours to get the information and formatting correct.

The graphs displays the percent distance of the price from the SMA against frequency (i.e., how often is the price this far from the 200 SMA?)

I care about scaling into trades as the price moves further from the 200 SMA. My instinct from looking at the graph above says I should focus on the inflection point. The curve forms a nice bend around 0.3% away from the SMA. Maybe I can start buying at the inflection point until I get to 0.6% or so.

What do you think I should do?

After-thoughts

This series eventually led to a profitable trading strategy. If you’d like to read through the journey, then I suggest reading the articles sequentially

Selecting an appropriate time frame
A research plan
An annoying surprise in the initial backtests
An attempt at range trading
Range trading results
The moving average envelope scalper

Export NinjaTrader Prices Excel

September 21, 2012 by Shaun Overton 1 Comment

Exporting prices from NinjaTrader is extremely straight forward.

Click on Tools \ Historical Data Manager
Click on the Export Tab
On the left window, select the + button next to the instrument that you wish to export. You’ll more than likely need to expand the tree until you’ve clicked on minute data.
NinjaTrader Historical Data Manager
On the right side, select the date range that you wish to export
Push Export
Save the file as a .txt in a location that you can easily remember. I prefer the desktop as it is easy to find visually
Open the file using MS Excel. A window screen will pop up
Push Nexton the first screen
The Text Import Wizard in Excel 2010
On the second screen, change the checked delimiter from Comma to Semicolon. Push Next.
Select the semicolon delimiter on step 2 of the text import wizard.
Push Finish

The prices of your forex, stock or futures instrument are now in an Excel spreadsheet.

Export MetaTrader Price Data To Excel

November 29, 2011 by Shaun Overton 12 Comments

Exporting the price history from a MetaTrader chart to Excel could not be easier. Just follow the steps below:

1. Open a chart
2. Click File, then Save As
3. Change the file name to whatever you wish. Make sure to leave the file type as ASCII Text *.csv
4. Push save
5. Double click on your saved file.

MetaTrader price export to Excel

.csv is a file format known as Comma Separated Values. Excel supports this format, which makes it the easiest method for exporting prices into a spreadsheet.