Please help before the Wife kills me

[Greg92]

A month of trying but I can't solve this so please help me!!! I have a sheet for sports trading, within this sheet I have prices for the first 3 selections in the market. The prices have been converted from fractional odds to decimal odds and then into a numbers which run 1 to 350 . From this I've setup up 3 moving averages and volume for each selection . I have a countdown clock which runs from 780 seconds out to start of event. Each second updates on new row so each column with Price , MA1,MA2,MA3,Volume dynamically updates ever second for each selection. So A2 Price B2 MA1,C2 MA2,D2 MA3, E2 VOLUME, F2 Signal MA1/2 CROSSOVER G2 MA2/3CROSSOVER H2 Volume signal. I use IF statements for the signals =IF(B2>C2),1,0 and the same for other triggers. Now the problem!! The only time I want to place a trade is if these triggers happen in order so THE MA1 crosses MA2 Trigger, MA2 crosses MA3 trigger . VOLUME trigger so I can't just SUM column as I'm getting 1,0 all the way down but it only works if they are in the order above so can't take any signals that happen before signal 1 . Hope this makes sense, just a note MA = moving average.
Many thanks for reading and hopefully helping me Greg

 

[Greg92]

Hi Offthelip
IVE Had To WinRAR them as they were to big?

Don't bother trying to load more screen shots the RAR don't seem to work, you have single trigger in columns Jb, JC and JD, , are you trying to trigger those three in that order??

Right so i got you about why Match is returning 21 , And yes i need the three triggers to be picked up in that order and then cause one trade to be placed . If they don`t happen in that order then no trade

 

[offthelip]

Try this I have assumed you want the three trigger all in order, I have used column B,C and D as the column ( save me moving across to JB , Jc jd, ( thats is HUGE worksheet)
Just add a J infront of all addresses:
in col e:
=MATCH(1,B6:B$6,0)
in col F
=MATCH(1,OFFSET(C$6,E6,0,ROW()-E6-5),0)+E6
in col G
=MATCH(1,OFFSET(D$6,F6,0,ROW()-F6-5),0)+F6

 

[Greg92]

Try this I have assumed you want the three trigger all in order, I have used column B,C and D as the column ( save me moving across to JB , Jc jd, ( thats is HUGE worksheet)
Just add a J infront of all addresses:
in col e:
=MATCH(1,B6:B$6,0)
in col F
=MATCH(1,OFFSET(C$6,E6,0,ROW()-E6-5),0)+E6
in col G
=MATCH(1,OFFSET(D$6,F6,0,ROW()-F6-5),0)+F6

Great , thanks again for your continued patience with me I'm very new to excel . I'll let you know how I get on in the morning

 

[Greg92]

Great , thanks again for your continued patience with me I'm very new to excel . I'll let you know how I get on in the morning

Morning Offthelip,
I'm afraid that i get this error when connecting to a live market And the Error #N/A All the way done the column now

 

[offthelip]

the #N/A is correct, I did say at the start that you can get rid of that by using iferror, but the error is helpful to see what is happening in development. this is what my spreadsheet looks like and it does exactly what you asked for:
the VBA error may possibly be caused by the errors on the worksheet, I can't tell because the VBA is nothing to do with this thread

 

[offthelip]

Can I just suggest that if you are running VBA anyway, it could be much faster and easier to do the triggers and the sequencing in VBA. ( that is what I do for automatic trading systems)

 

[Greg92]

Can I just suggest that if you are running VBA anyway, it could be much faster and easier to do the triggers and the sequencing in VBA. ( that is what I do for automatic trading systems)

I Have got it to do as you show in yours now , I'd forgotten to put the J before the first B, silly me !!!! But the run error still comes up. As for VBA I agree , but I know nothing about VBA I only opened Excel for the first time six week ago , the only other time I've got involved in programme is on my ZX81 ???

 

[Greg92]

I Have got it to do as you show in yours now , I'd forgotten to put the J before the first B, silly me !!!! But the run error still comes up. As for VBA I agree , but I know nothing about VBA I only opened Excel for the first time six week ago , the only other time I've got involved in programme is on my ZX81 ???

I've marked this threat as solved as you've done what many others couldn't and gave me the solution, could you help me please if I start a VBA thread or have I done your head in?? ?

 

[offthelip]

Glad to be of help, I will certainly look at any new thread but obviously I can't promise anything. I am pleased that you are at least still alive and the wife isn't in jail!!

 

[Greg92]

Glad to be of help, I will certainly look at any new thread but obviously I can't promise anything. I am pleased that you are at least still alive and the wife isn't in jail!!

Still not sure why the MATCH and INDEX got a run error?