Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 five trades on Jan 3. Each of these trades comes with a risk value. I need to add the risk values of these five trades so that I can compare it to an account value and then determine if I should take more than one 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.

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.

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
share|improve this question
1  
If you add a link to an online image, we can place it for you in order to clean up the formatting. Can you tell us what you've tried so far? –  CharlieRB Oct 25 '12 at 19:27
add comment

1 Answer

First of all, as far as I can tell, the only values in this spreadsheet that pertain to your question are Date and Risk.  So, I’ve reformatted the data to make it easier for other people to access just those fields:

The Date     Risk   Other Stuff
1/3/2012      15    EUR/USD S 1 . . 10 . 15
1/3/2012      15    USD/CHF L . . 1 . . 0
1/3/2012      15    AUD/USD S 1 . . 16 . 18
1/3/2012      15    NZD/USD S 1 . . 7 . 8
1/3/2012      10    AUD/JPY  S 1 . . 25 . 20
1/4/2012      20    EUR/USD L 1 . . 19 . 19
1/4/2012      15    USD/CHF S 1 . . 17 . 20
1/4/2012      20    EUR/JPY L . . 1 . . 0
1/5/2012      15    EUR/USD L 1 . . 10 . 20
1/5/2012      20    GBP/USD L 1 . . 15 . 20
1/5/2012      15    USD/CHF S . . 1 . . 0
1/5/2012      10    USD/JPY S 1 . . 7 . 10
1/5/2012      15    USD/CAD S 1 . . 28 . 36
1/5/2012      15    AUD/USD L 1 . . 20 . 20
1/6/2012      15    USD/CAD S . 1 . . 5 -10
1/6/2012      15    EUR/JPY L 1 . . 7 . 7
1/9/2012      15    AUD/USD S 1 . . 22 . 30
1/9/2012      15    NZD/USD S 1 . . 10 . 15

One way to do what I believe you want is to use the SUMIF function:

=SUMIF(Date column, "=Date of interest", Risk column)

See illustration.

sums produced with SUMIF

But a perhaps less labor-intensive way is to create a PivotTable:

same sums produced by PivotTable

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.