-1

hi I have a list of trading data. (Scenario #1) The BUY data is in one row and the SELL data of the same security is on anther row, and sometimes (scenario #2) there can be 2 rows of SELL data and 1 row of BUY data of the same security because selling took place in 2 different occasions. I want to line up the BUY data and the SELL data in one row so that I can make calculation for % gain and $ gain. Is there an easy way to rearrange the data? please see pic enter image description here

Jown
  • 1

1 Answers1

0

As I understand it, the issue is creating the weighted average from the source data.

Rather than restructuring, just refer to the data as it is in place.

It will be easier if you convert your raw data to a Table using Ctrl+T. I created a subset of your columns. The date isn't really needed.

enter image description here

Then, you can use UNIQUE (if you are using Excel 365 and it's available to you), otherwise you can use Advanced Filter or simply type the securities as row headers.

=UNIQUE(Table1[SECURITY])

Then, calculate the weighted average BUY and SELL prices. For example, security 1, BUY:

=SUMPRODUCT(Table1[Price],ABS(Table1[QTY]),--(Table1[Side]=H$1),--(Table1[SECURITY]=$G3))/ABS(SUMIFS(Table1[QTY],Table1[Side],H$1,Table1[SECURITY],$G3))

Note that the formula refers to the word BUY in the column header in column H to retrieve the correct rows from the Table. It also refers to the row header in order to find the correct rows for a specific security.

The double-negative operator will convert an array of TRUE/FALSE to an array of 1/0. This is useful in SUMPRODUCT as it has the effect of filtering rows to be multiplied.

SUMPRODUCT is important in calculating the numerator for the weighted average price of scenarios with multiple SELL rows, but also works for those with a single SELL row.

FlexYourData
  • 7,631