This is a mobile optimized page that loads fast, if you want to load the real page, click this text.

Excel FX autotrader

Jump to new
MS+Tradesim

MS+Tradesim

Joined
14 June 2007
Posts
1,130
Reactions
3
So for interest's sake, per stormin's suggestion, I am starting a new thread to update on my effort to develop an FX autotrader using Excel. Having said that, it can also be relatively easily adapted to trade anything on IB. This for me is a major undertaking given that this is the FIRST time I have ever tried programming.

The last error I discovered resulted in the order placing module under a certain condition to continue looping without ever exiting resulting in a potentially infinite number of new orders being sent. Catastrophically bad...!

I think I've isolated and fixed that, but we'll see.

Once I'm fully operational I hope to go into some detail on how I'm doing it (without disclosing the exact trading system). Due to a long discussion with wabbit today, I also hope to start learning MQL4 and learn to write EAs from scratch, as I recognise the cumbersome nature of this exercise. :)

Anyways, I have the relevant charts open in Metastock, and here is a pic of my trading spreadsheet. It's basically the IB sample sheet with a whole bunch of my own code and buttons added.

Theoretically, when it's running I don't need to do anything - but each of the buttons can be used for either manual trading (when auto off) or for overriding the auto if needed, though I've yet to add that functionality.

There actually seem to be quite a few people here who write their own autotrader's using C++/# or whatever. Would be good to hear from more about ins and outs, etc. Would probably really benefit us programming noobs.

EDIT: Yes, looping error has been fixed!!! Just did exactly what it was meant to!!!! It has now gone through several full cycles of ordering/modifying/closing/cancelling/etc successfuly. :alcohol:

I'm excited!
 

Attachments

  • order.jpg
    order.jpg
    122.4 KB · Views: 65
when something bad (or potentially bad) happens we have a saying


'a cashtastrophy'.
 
Think I have most of the problems fixed now. I've substantially rewritten the main program. One thing I eliminated was modules having more than one exit point (for the module, not for trades). I think that was causing unnecessary looping and incorrect order placing by having multiple redundant triggers. By rewriting I also found a bunch of logic errors that were resulting in unnecessary order cancellations and resubmissions.

3 days of bug removals so far!
 
great work!

is this a model of your actual trading method? or another model of trading youre using?

itll be good to see how it goes forward testing. its amazing how much discretion is still used in a mechanical system. im trying to code up the franklin based on my system, and the initial rules werent enough. we've had to do 4 versions just to get the entry point correct and have the computer take into account things i as a trader didnt realise i really did (things like support/resistance, movement of other currencies, viewing of the chart for a longer time period etc). now just have to move onto the hard part - exits!

working through the logic steps and seeing the outcomes, and limitations (and strengths) of automated trading is probably 70% of the lesson. the coding of the syntax is no where near as challenging when you have step by step method of what you want to achieve.

if you can create such a complicated excel sheet with google, i think youd be able to create a MT4 (or other platform) language with consummate ease.
 
wabbit said:
Is that all! Keep going then!
Click to expand...

How long should it take?

Are functions working better for you than Subs?
Click to expand...

Haven't started with them yet, but I have tidied up the number of sub calls and the way they operate; also got rid of "Set".

Stormin_Norman said:
is this a model of your actual trading method? or another model of trading youre using?
Click to expand...

It's my strategy which I trade manually. However, it's time intensive as I need to adjust/place/cancel orders every 5mins. The time plus the human error factor adds up to lots of headaches. Hence, my effort to automate it. The beauty of what I'm doing though, is once it's fully operational, I will have a template for other strategies I'll develop.

its amazing how much discretion is still used in a mechanical system.
Click to expand...
Thankfully, mine is completely rule based so it can be automated with relative ease (relative to the skill of the programmer ).

if you can create such a complicated excel sheet with google, i think youd be able to create a MT4 (or other platform) language with consummate ease.
Click to expand...

Yep, I will be looking at learning MQL4 and writing it up - that's my next goal.

Update: it's been running beautifully now since 4pm. I've only encountered one problem since then but that wasn't in the program.
 
Thanks.

I've just turned it off. It ran for about 6 hours with no problems and did about 10 round trips. Next thing to focus on is getting the order trigger method right to minimise slippage. It's tracking slightly higher slippage than backtested. Only a few pips here and there but it makes a big difference to my strategy results.

To give some idea of what I'm trying to achieve, this is some backtest results. I didn't trade the circled area in real life. I sat it out until I was happy the drawdown phase was finished. That was achieved by backtesting every few days and waiting until the simmed equity curve was recovering. So stormin, there is an element of discretion in whether or not to trade overall, but not for any given individual trade. Saved a lot of real life money. When I'm happy with the autotrader I hope to report forward test results which are the only ones that really count.

This test was at 1% risk. Optimum results are achieved at about 1.8-2% risk. A very smooth curve with highly reduced returns can be achieved by using a set dollar risk of $500 per trade.

Edit: I'll be ecstatic if the real results are half as good. This backtest takes every valid signal it can afford. Manually on live trading, I've been taking way fewer trades as the time intensiveness of managing each trade was quite high - another reason why I want it fully automated.
 

Attachments

  • closedequity.jpg
    166.4 KB · Views: 35
MS+Tradesim said:
I didn't trade the circled area in real life. I sat it out until I was happy the drawdown phase was finished. That was achieved by backtesting every few days and waiting until the simmed equity curve was recovering.
Click to expand...
Good work with getting your system up and running, it's the most amazing thing, an Excel spreadsheet that makes money. Usually Excel spreadsheets just help you to add up your losses

I agree with the "virtual" equity curve, this is a good filter. I think you should have the system doing virtual trading on all signals (even when above your leverage limit), and use this to help choose when the system should make "real" trades.
 
Hey lads .... been trading FX small time for a while now .... total novice with anything related to programming, but you guys have got me interested

I like to do the hard yards, so just give me a hint where I should start looking so I can start learning the stuff I need to try this auto business ....

My system is basically discretionary, but only when certain criteria/indicators etc are met....

Main question for a start ..... Is it actually possible to 'programme' in things like for example ....

"entry after X MA crosses Y MA, but only if the X period Stochastic has crossed below 20 and is now above 20 etc etc ........" are these the types of parameters you can programme in ?? ........ that would be interesting stuff ... I might actually get more sleep at night ... lol

Cheers.
 
Excellent work MS, how many hours so far have you put into this?

Cheers,


CanOz
 

If you can write down the logic, it can be programmed.... within reason of course

Yes, your "system" can be scripted.



Hope this helps.

wabbit
 
Didn't get near this project yesterday but back at it today. Refining the trigger and order types to minimise slippage. I will probably run a day or two on each trigger type to collect enough samples to make a decision as to what will work best on auto. I use Booktrader on IB for manual which gives a great deal of flexibility - I can quickly change order type or price. But the auto will have to work a bit more simply at least until I have the time and energy to set up all the different variables.

Pilbara

I think you should have the system doing virtual trading on all signals (even when above your leverage limit), and use this to help choose when the system should make "real" trades.
Click to expand...

I agree.


Barney

Wabbit is the person to talk to about all things programming when it comes to trading.

Personally, if I knew six months ago what I knew now, I would start by getting familiar with Metatrader4 (MT4) and learning Meta Quotes Language 4 (MQL4). If you don't already know, MT4 is a programmable trading platform used by most of the bucketshops and some reputable FX brokers. MQL4 is the language you program it with.

The way I've done things is very round-about and inelegant but I am enjoying the learning and getting used to thinking the way a computer operates.

Canoz

I've been working on the trading strategy for about 3 mths. I believe it will only operate well in these current volatile conditions. I will probably replace it with a longer-term trend following system at some point.

The basic manual trading spreadsheet only took about one or two days to put together. All I did for it was create some position sizing functions based on live data and added some database queries to grab the trading signals from Metastock. Beyond that it was just a matter of making some buttons and assigning various simple macros to them.

The auto trading program however has taken about 2 weeks so far. I am new to programming and this is my first project. I'm very happy with the outcome so far.
 
youre kicking ass!

it good to see a couple of different people getting into programming up autotraders.

when i first started i totally discounted them, now i think they will become my only method of trading.
 
This is the guts of it. The first sub, Autostart(), is basically redundant but there are some things I want to add and I will probably put them in there so it stays for the time being. Something I will do on the next version is define all the variables as an appropriate data type to keep the memory happy and replace a lot of subs with functions...per Wabbit's suggestion.

Also, copy and paste to here lost all the formatting and I can't be stuffed indenting everything.
========================================


Sub AutoStart()
' Checks for safety trigger
Trigger = Sheets("Orders").Range("V1")
If Trigger = 1234 Then
Sheets("Orders").Range("V9").FormulaR1C1 = "ON"
End If
' Begin loop
AutomateTrading
End Sub

Sub AutomateTrading()
Trigger = Sheets("Orders").Range("V1")
If Trigger <> 1234 Then Exit Sub Else
If Trigger = 1234 Then
' Clear all non-open orders and refresh data
RefreshData
Clear
EURJPY
End If
End Sub

Sub EURJPY()
'Set all variables
EJEntry = Sheets("DataSheet").Range("AA4")
EJLow = Sheets("DataSheet").Range("AB4")
EJHigh = Sheets("DataSheet").Range("AC4")
EJFilter = Sheets("DataSheet").Range("AQ4")
EJQty = Sheets("DataSheet").Range("M2")
EJLO = Sheets("Orders").Range("S18")
EJSO = Sheets("Orders").Range("S22")
EJOT = Sheets("DataSheet").Range("AW4")
EJC = Sheets("Orders").Range("S45")
EJCCon = Sheets("Orders").Range("L45")
On Error Resume Next

' Long Side Orders
' Place initial Long order
Select Case EJEntry > EJLow And EJFilter >= 70 And EJQty = 0 And EJOT = 1
Case True
BuyEURJPY
End Select
' Cancel redundant Long order
Select Case EJLO = "PreSubmitted" And EJFilter < 70 And EJQty = 0 And EJOT = 1
Case True
CancelEURJPY
End Select
' Close Long without reversing
Select Case EJQty > 0 And EJFilter > 30 And EJOT = -1
Case True
CloseEURJPY
End Select
' Replace Close with Buy
Select Case EJC = "PreSubmitted" And EJFilter >= 70 And EJCCon = "BUY"
Case True
CancelEURJPY
BuyEURJPY
End Select
' Replace Buy with Close
Select Case EJEntry > EJLow And EJFilter < 70 And EJLO = "PreSubmitted" And EJQty < 0 And EJOT = 1
Case True
CancelEURJPY
CloseEURJPY
End Select

' Short Side Orders
' Place initial Short order
Select Case EJEntry < EJHigh And EJFilter <= 30 And EJQty = 0 And EJOT = -1
Case True
SellEURJPY
End Select
' Cancel redundant Short order
Select Case EJSO = "PreSubmitted" And EJFilter > 30 And EJQty = 0 And EJOT = -1
Case True
CancelEURJPY
End Select
' Close Short without reversing
Select Case EJQty < 0 And EJFilter < 70 And EJOT = 1
Case True
CloseEURJPY
End Select
' Replace Close with Sell
Select Case EJC = "PreSubmitted" And EJFilter <= 30 And EJCCon = "SELL"
Case True
CancelEURJPY
SellEURJPY
End Select
' Replace Sell with Close
Select Case EJEntry < EJHigh And EJFilter > 30 And EJSO = "PreSubmitted" And EJQty > 0 And EJOT = -1
Case True
CancelEURJPY
CloseEURJPY
End Select
EURUSD
End Sub

****Followed by 3 more modules for other FX pairs then....*****

...
...
SetDelay
End Sub

Sub SetDelay()
Trigger = Sheets("Orders").Range("V1")
If Trigger <> 1234 Then Exit Sub Else
If Trigger = 1234 Then
' Set loop delay
Application.OnTime Now + TimeValue("00:00:30"), "AutomateTrading"
End If
End Sub

Sub AutoStop()
' End AutoStart macro by deleting trigger code
Sheets("Orders").Range("V1").ClearContents
Sheets("Orders").Range("V9").FormulaR1C1 = "OFF"
End Sub
 
Ps. And no, it is not possible to reverse engineer my trading method from that post so don't waste your time.

Pps. There are a bunch of calls to things like CloseEURJPY or CancelEURJPY or SellEURJPY etc. They call a bunch of minor subs (sub-procedures) that execute the relevant command by using the modules IB included on the spreadsheet. There is some redundant duplication in those processes which I will eliminate in the next version.

Wabbit I got Excel VBA Programming for Dummies and it has some decent (IMHO) chapters in there on data types and sub/functions so I will be using all that in the next version.
 

Very impressive MS.

Have you decided how you are going to rank the trade?
Have you montecarloed these different possible outcomes on TradeSim ?

I'd be keen to see full backtested stats.
 
nizar said:
Have you decided how you are going to rank the trade?
Click to expand...

Yep. There is no trade ranking. If it signals and there's spare margin according to the overall MM strategy then it takes it.

Have you montecarloed these different possible outcomes on TradeSim ?

I'd be keen to see full backtested stats.
Click to expand...

Yes, and I will post some later.
 
You must log in or register to reply here.