Automated Excel Trading programming

J

jimmy2shoes

Junior member
Messages
16
Likes
0
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((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.
 
jimmy2shoes said:
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.
Click to expand...

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.
 
Last edited:
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.
 
jimmy2shoes said:
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.
Click to expand...

Do you have a single cell that is assigned the the value and updated?
 
Last edited:
HTML:
jimmy2shoes said:
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.
Click to expand...

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.
 
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.
 
jimmy2shoes said:
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.
Click to expand...

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 :)
 
Last edited:
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.
 
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!
 
new_trader said:
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 :)
Click to expand...

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.
 
jimmy2shoes said:
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.
Click to expand...


=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
 
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)
 
1) How have you created the buy/sell signals?
2) Are the share purchases real or is this a simulation?
 
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
 
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?
 
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!
 
Last edited:
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.
 
You must log in or register to reply here.

Similar threads

J
Excel trading (help needed)
Replies
0
Views
1K
jimmy2shoes
J
J
Excel Trading /partially completed automated trading signal provider
Replies
0
Views
3K
jimmy2shoes
J
J
Excel Trading /partially completed automated trading signal provider
Replies
1
Views
2K
BeginnerJoe
BeginnerJoe
W
Automating buy and sell signals
Replies
1
Views
2K
waytogo
W
B
Automated Trading Set Daily
Replies
0
Views
1K
Brennen81
B
Top