Automated Excel Trading programming

[jimmy2shoes]

I am trying to create an automated trading warning system. I am trying to get signals so that for exmaple when the RSI/MA reach a certain point it would give me a buy/sell signal could anyone please help me in giving me a formula? So that when cell-x = below 30 it would say Buy/ when it reaches above 70 it would say Sell etc.

 

[new_trader]

=IF((A1-B1)<30,"Buy",IF((A1-B1)>70,"Sell","No Trade"))


-A1 and B1 are the cells that contain the values you are testing
-Copy the formula into another cell, eg C1
-"No Trade" is what you get for values between 30 and 70 inclusive.
- A1, B1, C1 should be formatted as numbers.

 

[Joey25]

I am trying to create an automated trading warning system. I am trying to get signals so that for exmaple when the RSI/MA reach a certain point it would give me a buy/sell signal could anyone please help me in giving me a formula? So that when cell-x = below 30 it would say Buy/ when it reaches above 70 it would say Sell etc.

If the turning points from "no signal" to "signal" matter to you, then assuming that you have written your own function already for RSI, then you can use:

if(B2>=30,1)*if(B3<30,1)

where column B contains successive RSI values

The expression above evaluates to 1 only at the turning points, 0 otherwise.

Similarly,

-if(B2<=70,1)*if(B2>70,1)

for sell signals, evaluating to -1 only at the turning points.

 

[jimmy2shoes]

brilliant thank u. so that would mean that i need to have a sell column, which would show a -1 when the indicator goes below 30, and in the buy column a +1 in the buy column when the signals are hit? have a look at my spreadsheet under 'Excel Trading /partially completed automated trading signal provider' you can have a look at what i am trying to do.

 

[new_trader]

brilliant thank u. so that would mean that i need to have a sell column, which would show a -1 when the indicator goes below 30, and in the buy column a +1 in the buy column when the signals are hit? have a look at my spreadsheet under 'Excel Trading /partially completed automated trading signal provider' you can have a look at what i am trying to do.

Do you have a single cell that is assigned the the value and updated?

 

[Joey25]

brilliant thank u. so that would mean that i need to have a sell column, which would show a -1 when the indicator goes below 30, and in the buy column a +1 in the buy column when the signals are hit? have a look at my spreadsheet under 'Excel Trading /partially completed automated trading signal provider' you can have a look at what i am trying to do.

Yes - then add the two signals to give your overall result. which will be +1, 0 or -1. You don't have to worry about buy and sell signals wrongly cancelling each other, because they can't appear at the same time.

Furthermore, once you have this overall "turning point" column, if you so wish you can add a column next to it which says:

if(b3 = 0, c2)

which in words says "if there is no signal today, use yesterday's signal". That would mean that you would stay long until a sell signal occurred and vice versa.

 

[jimmy2shoes]

as of yet no, but it does contain the RSI and about 20 other indicators.... ive done this work its just trying to get all the right formulas so that i can have all the signals give me buy/sell signals together. Then once the different indicators i.e. cycle indicators, market strength, momentum etc. are all weighted up then the document should give all the right signals to either purchase/sell any stock. Very optimistic i know. but as i said have a look at the document.

 

[new_trader]

as of yet no, but it does contain the RSI and about 20 other indicators.... ive done this work its just trying to get all the right formulas so that i can have all the signals give me buy/sell signals together. Then once the different indicators i.e. cycle indicators, market strength, momentum etc. are all weighted up then the document should give all the right signals to either purchase/sell any stock. Very optimistic i know. but as i said have a look at the document.

Ok, when you wrote cell-x, I thought you meant value in cell (minus) Value x.

For a single cell use:

=IF((A1)<30,"Buy",IF((A1)>70,"Sell","No Trade"))


Or Joey's one

 

[wackypete2]

This is interesting. What time frame are you using? For smaller time frames i thought excel was too slow or would get bogged down. Sorry if that's a bit off topic. Good thread so far.

 

[jimmy2shoes]

Its done/updated on a daily basis. I am using a MAC:Office 08 which unfortunately does not have the add-ins, the statistical packages or VBA for updating intra-day. I am sure you would be able to do this on a very fast windows computer with VBA!

 

[jimmy2shoes]

Ok, when you wrote cell-x, I thought you meant value in cell (minus) Value x.

For a single cell use:

=IF((A1)<30,"Buy",IF((A1)>70,"Sell","No Trade"))


Or Joey's one

This works perfectly. + actually seems to be quite good at predicting the swings.

Are you able to edit for formula so that instead of no trade if for example the previous cell says 'sell' then this continue until a new warning/a strong sell signal occurs?

Also in regards to MA's i have used a 10,20,50,100 signal is there also a excel formula to say,' If 10MA is above 20 Buy' If below says 'Sell' then with the other MA's give them a weighting i.e. 10 above 20MA (20%), 20 above 50MA (35%), 50 above 100 (45%). So that the longer term MA has the strongest weighting so that slight short term movements will not disturb the LR price movement.

If they are all above each other then with the 100% weighting it would say strong buy, say 80% would be a buy, 45% sell. etc. There may be a better way for writing this/approaching it. Not to sure any help would be appreciated.

 

[Joey25]

This works perfectly. + actually seems to be quite good at predicting the swings.

Are you able to edit for formula so that instead of no trade if for example the previous cell says 'sell' then this continue until a new warning/a strong sell signal occurs?

Also in regards to MA's i have used a 10,20,50,100 signal is there also a excel formula to say,' If 10MA is above 20 Buy' If below says 'Sell' then with the other MA's give them a weighting i.e. 10 above 20MA (20%), 20 above 50MA (35%), 50 above 100 (45%). So that the longer term MA has the strongest weighting so that slight short term movements will not disturb the LR price movement.

If they are all above each other then with the 100% weighting it would say strong buy, say 80% would be a buy, 45% sell. etc. There may be a better way for writing this/approaching it. Not to sure any help would be appreciated.

=0.20*if(b10 > c10, 1,-1) + .35*if(c10 > d10,1,-1) + .45*if(d10 > e10, 1,-1)

where b = 10MA, c = 20MA, d = 50MA, e =100MA

 

[A Dashing Blade]

Jimmy . . . seriously suggest you invest in this

While you're waiting for it to be delivered, you may want to peruse these sites . . .

http://www.cpearson.com/excel.htm


http://peach.ease.lsoft.com/scripts/wa.exe?A0=excel-l&D=0&F=&H=0&O=T&S=&T=0

http://blogs.msdn.com/excel/

http://www.excelhero.com/

http://www.blog.methodsinexcel.co.uk/

http://www.rondebruin.nl/

http://spreadsheetpage.com/index.php/tips

 

[jimmy2shoes]

could anyone help please. Once i have created the Buy/Sell Signals. How do i then get a system where.... If the cell contains a buy i purchase 500 shares of (x)? But, if it says Sell i sell 500 shares of (x)

 

[new_trader]

1) How have you created the buy/sell signals?
2) Are the share purchases real or is this a simulation?

 

[jimmy2shoes]

It is simulation.

The Buy/Sell signals are given through a simple MA cross over system i.e. Buy when 10MA goes above 20MA and so on. I have others for RSI and a few other indicators. But, its easy to change the formula once you can see it.

Thanks in advance

 

[new_trader]

1)How does the simulator work? Do you have a new row for each timestamp and appropriate columns populated with all the values of the MA's and stock price for each time?

2)Do you want to ‘short’ stocks or do you only sell if you already own them?

 

[jimmy2shoes]

I have a spreadsheet that contains all the daily data, along with the EMA and SMA! I then have a row that shows when the prices cross over, which give a cell that contains (buy/cell) I would idealistically have a cell that purchases on the buy order, sells on the sell order, but, then turns the trade around to a short ( not owning stock) visa versa. U can find the basis of the simulator in my other posts. I also need to be able to calculate the profits (cumulative) and/or all the other data such as profit/loss ratio!

 

[new_trader]

The reason you aren’t getting a lot of help with this is because anyone who understands programming knows that there is more involved than simply typing in the code. Trust me on this, even on forums dedicated specifically to writing software there are few people willing to help those who don’t appear to be making an effort. Most of what you want to do involves nothing more than simple conditional statements that you can learn how to do yourself. The ‘difficult’ part is getting the logic flow right and accounting for all contingencies.

As far as a buy or sell command, a simple trade statement would look like this:


=IF((B2)="Buy",(500*C2),IF((B2)="Sell",(-500*C2),"No Trade"))

Where:
D2= Cell that contains the above statement. Shows negative to indicate a short sale.
B2= Trade decision
C2= Share price

However, there are things you need to consider which I have not accounted for like whether you already own the stock before making a trade and if the trade is going to open a new order, add to an existing position or close an existing position. Also, in the real world you have to consider the possibility of partial fills. I suggest you buy an Excel for dummies book or do some online tutorials.