Excel FX autotrader

[wabbit]

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.

The code is starting to look neater too! With less subs being called, the code maintenance and debugging should be getting easier? I notice you still have the reading the data from the pages in each sub routine; as mentioned before, I'd take all of them out and one function which reads the worksheet data into variables.

As for the data types, you'll find just about all of that information in the help files too! The Dummies book might explain it better in the first instance, but now that you know what the differences are, you can just refer to the help functions for the info. When you're done, donate the book to your local library!

The use of sub vs function is debatable, but as we discussed I prefer to use functions because they return a value. Combined with "on error" you can trap errors at each function level; if the function returns false you can stop the app and find out why. Continuously calling sub routines just ends up hurting! (On error... is a poor-man implementation of the much more powerful concept available in programming languages like C/C++ etc, which is Try...Catch where you can Throw specific errors and have your code deal with these errors on a type-by-type, case-by-case basis. Happy days )

I know it's a personal preference thing; but if you're going to have a Select Case with only one case, then its the same as an If...Then statement (without an else). I have never speed tested the two so don't know if one is faster than the other, but I think in readability If...Then is a little easier on the eyes when there is not a list of possbilities. Also, with Boolean logic (true and false) you wouldn't need to specify the case in an If...Then e.g.

If test Then something

instead of

If test=true Then something


and for the false cases:

If Not test Then something

is the same as

If test=false Then something


As I said, it's a preference thing.


Did you eventually get a matrix of all the routines and function calls? I started, but then got really pi55ed off with how many places one section of code can visit! so stopped working on it! There are a lot of efficiencies to be made to optimise the running of the code.


Hope this helps.


wabbit

 

[MS+Tradesim]

Hi wabbit,

I have taken all your advice onboard and it will all be implemented in version 2.

My primary goal here was to get this program fully operational.

As you know, the code I sent you used IF-THEN. I rewrote with Select Case and it 'seems' to run smoother and maybe quicker but I did so many other things I can't tell if that was a difference of note. But in terms of just looking at the code, my eyes seem to follow the Select Case easier than the IF-THEN so as long as it does the same thing I'll leave it this way.

I am still working on an overall map and I agree with the efficiency/optimising part - all of that to come in v2.

Thanks for your ongoing advice.

 

[barney]

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

thanks Wabbit.... you Wascal you ....

are we talking logic in the literal sense of language, or a form of computer logic which is related to the programme we are working with? ..... told you I was a novice lol ....

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.

Good advice MS .... Had a quick squiz at the computer "language" you guys are dealing with here MS+ ...... holy crap .... how can you understand this stuff lol ..... I'll keep digging, but I may be too old to cotton on to the lingo !!

Barney

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

Yep, gathered that reading through the posts .... Bit of a S/Sydney supporter myself, so the Bunnies might be an omen !! .... I'll do a bit more research before I pester you lads too much ..... certainly interested in the automation stuff though ...... agree with S/Norm ... it looks the way into the future, particularly if a lot of big punters are playing the same game ...... I guess the only problem eventuates when too many punters start tracking the same style of programme ..... the big players would be continually trying to adapt their "auto's" to extract the maximum amount of cash from the other players, so the game would be continually evolving ..... for that reason, I've always thought us small players should concentrate on the "meat" in the middle of the trend, and be happy with small pickings .... just a theory of course .....

Cheers lads ... gotta go ... have a gig tonight ...... the Les Paul waits to hammered, caressed and bent into tonal (sometimes atonal .... on purpose of course !!) artistry .... lol.

 

[MS+Tradesim]

Cheers lads ... gotta go ... have a gig tonight ...... the Les Paul waits to hammered, caressed and bent into tonal (sometimes atonal .... on purpose of course !!) artistry .... lol.

Have a good nite. I envy you! This is my axe which I might get to pick up again some time soon. Lol.

 

[barney]

Have a good nite. I envy you! This is my axe which I might get to pick up again some time soon. Lol.

Nice !!!!!!!!

Trading must be going well to afford that MS ........ I'd nearly consider a swap of my 1972 "Les" for that (been my friend for over 30 years) ...... but I'm pretty sure you wouldn't lol ....

 

[bingk6]

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.

Hi MS,

Congrats on getting your system onto an automated platform, certainly no easy task and you appear to have done quite well.

Re: the slippage that you have been experiencing, your findings certainly concur with what I have found with my systems and is one of the reasons why I don't assume or rely on backtested results. With an EOD system or weekly system, you can be certain of the fill because of the auction process that takes place at the beginning of each bar, which means that the fills on your backtested results match with your actual fills. With an intraday system, you don't have that luxury because you cannot say I want a fill at the opening price of the next bar, as there is no auctioning process between bars. I have found that there can be significant differences between what you expect your fill price to be and the actual fill price.

I have tried a number of ways (at different times) to overcome these discrepancies, including
1) making sure that the order is submited only when there is 3 seconds or less left in the current bar (I trade off 1min bars) to try and get as close to the opening price of the next bar and
2) Examine the bid and ask prices just prior to pushing the button to make sure that the spread is not too wide and if it is, to abandon the order and
3) Start off initially with a LMT order to try and trigger a favourable fill price and if not filled by a certain time within the current bar to then change it into a MKT order to ensure a fill.

To date, these different options have given some minor improvements, but not enough for me to want to depend on backtested results. I have also found on vary rare occassions that even MKT orders do not execute immediately and when they do, which can be up to 30secs after submision, you can imagine that the fillcan be nothing like the fill that you are expecting.

I have to say that this inability to depend on backtested results is causing the process to take longer than I like it to. Every change has to be put through the simulator to be monitored over a period of time and that can take a long time indeed.

Finally, one last piece of advice, when your system submits or amends a order, always make sure that your code remembers what it has done and hence can successfully check that previous instructions have taken place correctly before moving on. Your code cannot assume anything, it must check for everything and be aware of its status at all times, just as a human trader would be.

 

[pilbara]

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.

I was wondering when using the Interactive Brokers ECN, are you using the Depth of Market to place limit orders? This should be an advantage of an ECN compared to marketmaker brokers where you are forced to be price takers. But also I think in real trading the IB ECN is aimed for large traders working with 10 or more standard contracts, and maybe small orders don't get the priority.

 

[MS+Tradesim]

Trading must be going well to afford that MS ........ I'd nearly consider a swap of my 1972 "Les" for that (been my friend for over 30 years) ...... but I'm pretty sure you wouldn't lol ....

Thankfully I got this one for cost price. The owner got it for cost from a dealer but decided he wanted a Les Paul so he onsold it for the same price still brand new. He'd never played it. And, no I definitely wouldn't swap it for anything else.

I have tried a number of ways (at different times) to overcome these discrepancies, including...<SNIP>....
2) Examine the bid and ask prices just prior to pushing the button to make sure that the spread is not too wide and if it is, to abandon the order and

One thing I've done is to setup dynamic order pricing in the spreadsheet. It grabs the spread and divides by two then adds/substracts that from the entry price to create an offset which is used for the trigger price - this is constantly updated. So no matter how wide or narrow the spread the trigger is offset by an appropriate amount to compensate. That has trimmed some slippage out. I am still playing around with limit and market orders similar to what you've tried.

Your code cannot assume anything, it must check for everything and be aware of its status at all times, just as a human trader would be.

That's a good point and something that I will spend some time on.

I was wondering when using the Interactive Brokers ECN, are you using the Depth of Market to place limit orders? This should be an advantage of an ECN compared to marketmaker brokers where you are forced to be price takers. But also I think in real trading the IB ECN is aimed for large traders working with 10 or more standard contracts, and maybe small orders don't get the priority.

Dunno about size and priority, but yes, you can use DOM. Also IB has a range of orders and trigger methods giving quite a lot of control. I am still testing different ideas to optimise fill price. The difference with IB is more than just being ECN. Their setup is geared for professional traders and institutions and account managers so it can be as complex as you want.

 

[pilbara]

another thing to check regarding slippage is the effect of time delays in the system (latency). From what I've seen IB's ECN updates prices very quickly. If your system is running in Australia there will be a minimum half second delay (quarter second each direction) between when a price is published on IB in USA and when your order arrives on their server. Also you could measure your processing time and make sure that's not delaying things too much. Because IB is setup for professionals then you are competing on speed and latency might be an issue. Running your system on a VPS server (Virtual Private Server) in USA might get you a hundredth of a second away from the IB server, instead of a quarter of a second here in Aus.

 

[MS+Tradesim]

Hi Pilbara,

As far as I know, my orders actually go through IB's server in Hong Kong and being an ECN, other orders could be coming from anywhere in the world, not just the USA. But I take your point.

Anyways FWIW, here are the results for day one. I ran it from about 4pm till about 9:30pm QLD time. When it goes live it will trade later. Currently set to trade EURUSD, EURJPY, GPBUSD and USDJPY.

Nominal sim account equity of about 29k, risking 0.8% per trade. I don't think there was ever more than about 8k up as margin. Results are in AUD. Not sure of the R:R yet as I still need to correlate the trades with the initial stops. The last three trades were closed prematurely. Don't know yet how they would have panned out had I left them to exit properly.

 

[barney]

Thankfully I got this one for cost price. The owner got it for cost from a dealer but decided he wanted a Les Paul so he onsold it for the same price still brand new. He'd never played it. And, no I definitely wouldn't swap it for anything else.

Funny ..... I figured that would be your reply ... lol .... it certainly is a nice piece of equipment ..... of course the old "Les", which is now worth over 5 times what I paid for it 30 years ago is not to be sneezed at ........ Cheers.

 

[Stormin_Norman]

sexy graph! what time period is that over?

 

[pilbara]

Dunno about size and priority, but yes, you can use DOM. Also IB has a range of orders and trigger methods giving quite a lot of control. I am still testing different ideas to optimise fill price.

I'm first trying to get an EA to work with a marketmaker broker because they have a simple pricing model. Once I can get this to work then I'll try to understand ECN trading. I think the main difference is that a marketmaker guarantees fills at a published price for a certain time (unless they have a serious problem with liquidity). Every price has a "time to live" during which they will automatically execute a limit order. This "time to live" might only be a few seconds but it means there is less chance of entry slippage with a marketmaker (an order is either filled or killed). This makes system development a bit easier.

 

[MS+Tradesim]

The trading strategy with a few tweaks also works on the SPI. So I'm adding functionality for the autotrader to trade the SPI during the ASX session and then trade FX after that.

Stormin

Which chart do you mean? The first one I put up in post #8 was a backtested equity curve over about 5-6mths.

The excel chart in post #30 was a forward test over about 5-6 hours on Friday afternoon/evening.

 

[Stormin_Norman]

500% return in 6 hours.

BOOYA!

 

[MS+Tradesim]

500% return in 6 hours.

BOOYA!

Not sure what you're looking at Stormin but it was more like about 11%.

 

[ojm]

Not sure what you're looking at Stormin but it was more like about 11%.

Post 30? 200 to 1000. More than 11%?

 

[MS+Tradesim]

Oh, I see what you're thinking. No, that is just closed trade equity. As I said in that post the account equity was about 29k and about 8k was used in margin. But I do apologise - I wasn't clear that the chart was only closed trade equity, not growth in total equity.

So the account return was about 900/29000 = 3% or about 11% ROI if only the used margin is considered.

 

[ojm]

So the account return was about 900/29000 = 3% or about 11% ROI if only the used margin is considered.

That clears it up.

Just a question for you, how long have you been trading currencies? I've just started learning about it all. Any tips you could offer a newcomer? Any resources that you find useful?

 

[Stormin_Norman]

read read and read some more.

test systems. dont trade willy nilly.

demo until u find one that works. then put a small amount into an account and use it.

google 'babypips forum' and 'forexfactory'.

both are excellent resources.