Match Date & Corresponding Info to Closest 5min Interval

[chubby127]

Hello all,

I've gained tons of knowledge from this site and hopefully some of you will be able to help me out here.

I am involved in some FX trading with four currency pairs and I am looking to make price in/price out charts to where I don't have to manually place trades next to the corresponding time and currency pair (see the sheet "GBPUSD Chart," I have manually placed a buy and sell in the first 2 lines). I believe it can be solved through INDEX and MATCH but I don't have the expertise to do so.

My main problem is that I get the FX prices in 5 min. intervals, so I need the formula to work such that if a trade is made at 11:32am, it is matched up next to the closest 5 min period (in this case 11:30). If this is too difficult, then the 5 min period AFTER the trade will work as well.

For example, for the first trade (EURUSD, 17-Apr 06:01, sold @ 1.31722 ) I would need the trade to show up in the "EURUSDM5" sheet. It should say "Sold" in cell I74 and the correct price should show up in K74 as it was a sell order.

Thanks in advance!

[:) Sixthsense :)]

There is no data present in I74 and K74 of Trades sheet

[chubby127]

I apologize for not being clear. That is, I74 and K74 of the "EURUSDM5" sheet.

I basically need the data from the "Trades" sheet to show up in the corresponding cells in their respective currency sheets.

[:) Sixthsense :)]

Still I am not clear about the cell references I74 and K74 I couldn't see any data in those cell references both in Trades and EURUSDM5 sheet.

[chubby127]

My reference to cells I74 and K74 was purely to give an example where I need to data to show IF the formula was correct.

I'll rephrase my example below of what I need the correct formula to do:

1. Look at row 3 in the "Trades" sheet and you will see the currency pair EURUSD was sold on 17-Apr-13 at 06:01 at the price of 1.31722.
2. Because this is a EURUSD trade, I need the data in cells C3 and H4 (in the "Trades" sheet) to show up in the "EURUSDM5" sheet next to the time that most closely corresponds to the "Trade Date" time in the "Trades" sheet.
3. So, in this case, if you were to find a formula that automates this process correctly, the data from 'Trades'C3 should appear in 'EURUSDM5'I74 and the data from 'Trades'H4 should appear in 'EURUSDM5'K74 (rather than J74, because it was sold), as the time and date in "EURUSDM5'A74 most closely corresponds to the time and date in 'Trades'F3

Does this help? Thanks!

[benishiryo]

hi chubby127, welcome to the forum. use this formula for all the sheets containing the currency pairs. like in "EURUSDM5" sheet under M1:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,6)

then in I2:
=IFERROR(INDEX(Trades!$C$3:$C$64,MATCH($M$1&A2,Trades!$B$3:$B$64&ROUND(Trades!$F$3:$F$64*288,0)/288,0)),"")

in J2:
=IF(OR($I2={"","Sold"}),"",INDEX(Trades!$H$3:$H$64,MATCH($M$1&A2,Trades!$B$3:$B$64&ROUND(Trades!$F$3:$F$64*288,0)/288,0)))

in K2:
=IF(OR($I2={"","Bought"}),"",INDEX(Trades!$H$3:$H$64,MATCH($M$1&A2,Trades!$B$3:$B$64&ROUND(Trades!$F$3:$F$64*288,0)/288,0)))

all of these are array formulas...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

i'm assuming that Buy Price is also taking from the Open Price in "Trades" sheet. if i'm wrong & it should be using Close Price, use this in J2:
=IF(OR($I2={"","Sold"}),"",INDEX(Trades!$I$3:$I$64,MATCH($M$1&A2,Trades!$B$3:$B$64&ROUND(Trades!$F$3:$F$64*288,0)/288,0)))

[chubby127]

This is fantastic Benishiryo!

Just one small edit that I need help with. (I've tried to fix it myself with no success)

In the "Trades" sheet, regardless of whether I bought or sold a currency (column C), I want column J or K in EURUSDM5 to take from the "Close Price" in column I if there is a value there. Otherwise it should use the price in column H. I think all it needs is a simple IF function somewhere but I can't figure out where to place it in your formula.

This is because when the FX position is closed, the open price is repeated from the previous opposite position, so I would have repeating data that actually corresponds to the previous data point.(you can see this in the "Trades" sheet in cells H3 & H4... The correct price for the trade in row 4 is actually the close price, which doesn't always correspond to a "bought" position.

Much appreciated!

[benishiryo]

you're welcome. change J2 to:

Please Login or Register  to view this content.

change K2 to:

Please Login or Register  to view this content.

array formulas still. if that's ok, do mark the thread as "Solved"

[chubby127]

Much appreciated Benishiryo! Works just like I want it to.