Please help before the Wife kills me

Greg92

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,283
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • ScreenHunter_193 Feb. 23 10.53.jpg
    ScreenHunter_193 Feb. 23 10.53.jpg
    11.3 KB · Views: 4
  • ScreenHunter_195 Feb. 23 11.07.jpg
    ScreenHunter_195 Feb. 23 11.07.jpg
    47.7 KB · Views: 4
  • ScreenHunter_196 Feb. 23 11.07.jpg
    ScreenHunter_196 Feb. 23 11.07.jpg
    24.3 KB · Views: 5

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,283
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

Attachments

  • 3triggers.JPG
    3triggers.JPG
    112.7 KB · Views: 5

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,283
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 23, 2017
Messages
2,283
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,042
Messages
5,767,793
Members
425,435
Latest member
cmardaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top