Exclusion formula

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I'm hoping you might be able to help me here. What I'm hoping to do is manage system of forex trades. Some brief background which may help with solutions:
There are 28 main forex pairs based upon the following 8 major currencies: AUD, CAD, CHF, EUR, GBP, JPY, NZD, USD.

What I want to do is have a formula that will put a value in column P (either +1 or -1) based on the open time of each trade and whether or not the trade above contains a currency that is already listed. Some difficulty may be encountered in that each trade has 2 components (in column L you see that each pair is repeated below)

For example in L11-L14 you'll see that the below trades were opened on the same day. What I want to do is have a 1 next to the AUDCAD trades and a -1 next to the AUDNZD
AUDCAD
AUDCAD
AUDNZD
AUDNZD

The common value there being AUD, had the trade been AUDCAD and EURNZD, the value next to both would be 1
Again, if the trade was AUDCAD and USDCAD (instead of AUDNZD) then +1 for AUDCAD (since it came first) and -1 for USDCAD (since it came second)

So what I'm hoping is that the formula can check if the date is the same as the trade above, and if so, add a 1 into the P column of the row the trade appears if the trade is different currencies or a -1 if the trades share a currency.

Here is the sheet

Mav 3.2 compund.xlsx
ABCDEFGHIJKLMNOPQR
1OPENCLOSEProfitPipsRAWFilterFilter
2100001000010000STREAK
310094.50 2018-01-02 00:00:081.201121.194821.194821.207422018-01-02 12:00:561.2074294.5063.0EURUSD10094.5010094.5094.50 94.50
410124.50 2018-01-02 00:00:081.201121.203121.194822018-01-03 10:07:361.2031230.0020.0EURUSD10124.5010124.5030.00 124.5
510124.500.00%2018-01-08 00:00:430.975840.980840.980842018-01-10 21:08:040.98084-100.00-50.0AUDCAD10024.5010023.26-101.25 0
610124.500.99%2018-01-08 00:00:430.975840.980840.980840.970842018-01-10 21:08:040.98084-100.00-50.0AUDCAD9924.509922.01-101.250.76%-100
710124.501.98%2018-01-10 00:00:000.881490.885090.885090.877892018-01-10 12:23:370.88509-97.20-36.0EURGBP9827.309825.57-96.441.73%-197.2
810124.502.94%2018-01-10 00:00:000.881490.885090.885092018-01-10 12:23:370.88509-97.20-36.0EURGBP9730.109729.13-96.442.70%-294.4
910124.50 2018-01-10 00:00:19114.604115.254115.254113.9542018-01-10 08:40:25113.95496.9865.0CHFJPY9827.089823.4894.351.73%0
1010124.50 2018-01-10 00:00:19114.604114.404115.2542018-01-12 11:25:30114.40429.7220.0CHFJPY9856.809852.3928.911.43%29.72
1110124.50 2018-01-11 00:00:000.983740.978340.978340.989142018-01-11 10:18:200.9891497.2054.0AUDCAD9954.009948.1695.770.46%126.92
1210124.50 2018-01-11 00:00:000.983740.985740.978342018-01-12 12:16:070.9857436.0020.0AUDCAD9990.009983.6335.470.10%162.92
1310124.501.33%2018-01-11 00:00:011.089181.094181.094181.084182018-01-11 03:08:061.09418-100.00-50.0AUDNZD9890.009883.79-99.841.10%0
1410124.502.32%2018-01-11 00:00:011.089181.094181.094182018-01-11 03:08:061.09418-100.00-50.0AUDNZD9790.009783.96-99.842.10%-100
1510124.50 2018-01-15 00:00:041.219431.211431.211431.227432018-01-15 12:17:281.2274396.0080.0EURUSD9886.009877.8893.931.14%0
1610124.50 2018-01-15 00:00:041.219431.221431.211432018-01-17 09:45:071.2214324.0020.0EURUSD9910.009901.3623.480.90%24
1710124.50 2018-01-15 00:01:071.710231.699231.699231.721232018-01-17 16:59:451.7212399.00110.0GBPCAD10009.009999.3998.02 123
1810281.25 2018-01-15 00:01:071.710231.699231.699232018-01-29 00:03:011.74048272.25302.5GBPCAD10281.2510268.95269.56 395.25
1910379.85 2018-01-16 00:00:000.989780.983980.983980.995582018-01-17 16:59:450.9955898.6058.0AUDCAD10379.8510370.20101.25 493.85
2010400.25 2018-01-16 00:00:000.989780.983980.983982018-01-18 00:00:000.9909820.4012.0AUDCAD10400.2510391.1520.95 514.25
2110496.25 2018-01-25 00:00:001.240811.232811.232811.248812018-01-25 15:45:321.2488196.0080.0EURUSD10496.2510490.9199.76 610.25
2210514.37 2018-01-25 00:00:001.240811.232811.232812018-01-29 00:00:341.2423218.1215.1EURUSD10514.3710509.7418.83 628.37
2310608.76 2018-01-29 00:04:07116.216115.366115.366117.0662018-01-31 17:31:49117.06694.3985.0CHFJPY10608.7610608.9499.20 722.76
2410701.61 2018-01-29 00:04:07116.216115.366115.3662018-02-06 00:00:00117.05292.8583.6CHFJPY10701.6110706.5297.58 815.61
2510796.11 2018-02-01 00:00:000.991780.998080.998080.985482018-02-01 12:48:470.9854894.5063.0AUDCAD10796.1110807.70101.18 910.11
2610861.21 2018-02-01 00:00:000.991780.998080.998082018-02-07 00:00:000.9874465.1043.4AUDCAD10861.2110877.4069.70 975.21
2710959.21 2018-02-02 00:00:001.086281.093281.093281.079282018-02-06 05:36:231.0792898.0070.0AUDNZD10959.2110983.99106.60 1073.21
2810987.21 2018-02-02 00:00:001.086281.084281.093282018-02-07 22:13:011.0842828.0020.0AUDNZD10987.2111014.4530.46 1101.21
2910987.210.00%2018-02-06 00:00:000.885660.879860.879862018-02-08 00:00:000.88344-35.52-22.2EURGBP10951.6910975.33-39.12 0
3010987.210.32%2018-02-06 00:00:000.885660.879860.879860.891462018-02-08 00:00:000.88344-35.52-22.2EURGBP10916.1710936.20-39.12 -35.52
3111017.17 2018-02-07 00:00:001.237661.247761.247761.227562018-02-07 19:03:431.22756101.00101.0EURUSD11017.1711046.66110.46 0
AM
Cell Formulas
RangeFormula
A2A2=M2
A3:A31A3=MAX(M3,A2)
B3:B31B3=IF(M3 < N(M2), 1-M2/A2, "")
M3:M31M3=M2+J3
N3N3=O3+N2
O3,O31,O29,O27,O25,O23,O21,O19,O17,O15,O13,O11,O9,O7,O5O3=J3*(N2/$N$2)
N4:N31N4=N3+O4
O4,O30,O28,O26,O24,O22,O20,O18,O16,O14,O12,O10,O8,O6O4=J4*(N2/$N$2)
Q3:Q31Q3=IF(M:M<10000,(10000-M:M)*0.0001,"")
R3R3=J3
R4:R31R4=IF((AND(J4<0,R3<=0)),J4+R3,IF((AND(J4>0,R3>=0)),J4+R3,0))
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Is it possible to use a matching function? Is it possible to match based on a fluid basis? For example in column L, match based on the cell above?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,107
Messages
5,628,720
Members
416,333
Latest member
Time2Learn

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