Hi all
I have a sheet where I enter all my trades (Trade date, name of the stock, quantity and price ...). In this tab, all trades are entered, for all the different stocks (Tab "DEALS"). In the example sheet attached, only two stocks are entered LBTYA US and LBTYK US.
I have created a tab for the stock LBTYA US (Name of the strategy : Here "EFG_LBTYA_US") where I would like Excel to retrieve all the trades details correponding to that particular stock traded (here, LBTYA US Equity, cell B2 in yellow in the tab).
The original idead I had was to ask Excel to retrieve the data by extracting the dates it finds in the tab "DEALS" with the following formula :
First trade date (In Cell(A11))
=SMALL(INDIRECT($L$1),1+COUNTIF(INDIRECT($L$1),"<="&$L$2)) (=> INDIRECT(L1) in the column with all the trades date un the tab "deals")
Then I used this, to find out the next date for which I have a trade : (Cell(A12), Cell(A13))
=IF(OR(A11="",ISERROR(SMALL(INDIRECT($L$1),1+COUNTIF(INDIRECT($L$1),"<="&A11)))),"",SMALL(INDIRECT($L$1),1+COUNTIF(INDIRECT($L$1),"<="&A11))) which basically starts by taking as refrence the first date (Cell A11), and from there, retruve the next available date.
The problem I have, is that I have only one date : So If I trade the same stock a couple of times the same day, my formula will retrieve only the first deal entered that day and I will miss all the others. For example, In the current version of the sheet, I only have the +14250 buy order on the 27/01/2014 and I a missing the -63500 sale order on the same day.
To fill the rest of the table, I plan to use some vlookup ou sumproduct stuff (each name + combination). But as start I need Excel to find all the trades I have done for a particular stock within my trade list and sorts it by date, including all the trades done for each day (does not work at this time!). Unless you guys have a more clever solution to retrieve all the needed infos.
Thanks a lot
Bookmarks