Excel data range - to sum series within date range
- by Mark
I have a set of data that I would like to manipulate but my problem is not straight forward.
In this data I have date ranges that include multiple entries of the same date on some days and not on others. What I need to accomplish is to manage a trading account so that no more than 1% of the account is put at risk on any given day (retrospectively). To do this, when a series of trades falls on the same day, I need to total the risk associated with each of those trades so that I can limit the total risk of the combined trades by limiting the position size I take in each. Here is a sample set of the data I am working with.
As you can see, there are 5 trades on Jan 3. Each of these trades comes with a risk value. I need to add the risk values of these 5 trades so that I can compare it to an account value and then determine if I should take more than 1 position in each trade. As you can see there are different numbers of trades that occur on the 4th, 5th 6th and 9th. I need the values returned in each row so that I can further manipulate them in the spreadsheet.
I am not new to Excel, but cannot come up with a solution here - your input is much appreciated.
Forgive the presentation below - I cannot upload a pic (new user) and the format does not carry across from excel. I have aligned the first several lines manually.
Thx.
Date ............. Pair ....... L/S ...... Initial Risk .......Win ......Loss ....BE. ....Avg Gain Avg Loss pips/swing
1/3/2012 ....EUR/USD ....S .............15 ................1 ..................................10 ..........................15.
..
1/3/2012 ....USD/CHF .....L ............15 ..........................................1 ..........0
1/3/2012 ....AUD/USD ....S .............15 ................1 .................................16 ...........................18
1/3/2012 ....NZD/USD ....S .............15 ................1 ...................................7 .............................8
1/3/2012 ....AUD/JPY .... S .............10 ................1 .................................25 ............................20
1/4/2012 ....EUR/USD ....L .............20 ................1 .................................19 ...........................19
1/4/2012 ....USD/CHF ....S ............ 15 ................1 .................................17 ...........................20
1/4/2012 EUR/JPY L 20 1 0
1/5/2012 EUR/USD L 15 1 10 20
1/5/2012 GBP/USD L 20 1 15 20
1/5/2012 USD/CHF S 15 1 0
1/5/2012 USD/JPY S 10 1 7 10
1/5/2012 USD/CAD S 15 1 28 36
1/5/2012 AUD/USD L 15 1 20 20
1/6/2012 USD/CAD S 15 1 5 -10
1/6/2012 EUR/JPY L 15 1 7 7
1/9/2012 AUD/USD S 15 1 22 30
1/9/2012 NZD/USD S 15 1 10 15