CODEWITHSUNDEEP
google-sheetsformulaxirr
madmoneymike5
madmoneymike5

Reputation: 41

Google Sheets: Convert Horizontal Transaction Data into Chronological Statement + Combining Columns of Data

On a sheet named, "Performance," I have data concerning stock trades in a row like so:

    A       B                   C                   D       E       F           G           H           I           J
1   TICKER  TRADE OPEN DATE     TRADE CLOSED DATE   SHARES  AVG BUY INVESTMENT  AVG SALE    PROCEEDS    PROFIT/LOSS ROIC:
2   ABC     01/05/22            03/31/22            107     $14.22  -$1,521.54  $15.00      $1,605.00   $83.46      5.49%
3   BCA     01/05/22            03/31/22            344     $14.52  -$4,994.88  $15.00      $5,160.00   $165.12     3.31%
4   CAB     01/05/22            03/31/22            526     $12.55  -$6,601.30  $13.00      $6,838.00   $236.70     3.59%
... and so forth ...

Within the same workbook but on a separate sheet named, "Contributions/Withdrawals," I have a list of contributions and withdrawals like so:

    A           B
1   DATE        AMOUNT
2   01/05/22    $700.00
3   02/05/22    $700.00
4   03/05/22    $400.00
5   03/15/22    -$7,000.00
... and so forth ...

I need to convert the first table of trade transactions into a vertical column format exactly like what is in the Contributions/Withdrawals table. (Note that each trade transaction actually represents two transactions, one for opening with its own date, and one for closing with its date.) Finally, I need to stack both tables of transactions in date order to make a combined chronological list of transactions so that I can run an XIRR formula on it.

The resulting table on a sheet named, "Cash Flows," needs to look like this:

    A           B
1   DATE        AMOUNT
2   01/05/22    -$1,521.54
3   01/05/22    -$4,994.88
4   01/05/22    -$6,601.30
5   01/05/22    $700.00
6   02/05/22    $700.00
7   03/05/22    $700.00
8   03/10/22    $400.00
9   03/15/22    -$7000.00
10  03/31/22    $1,605.00
11  03/31/22    $5,160.00
12  03/31/22    $6,838.00

Using the following in cell A2 and B2...

A2  =SORT({Performance!$B$2:$B;Performance!$C$2:$C;'Contributions/Withdrawals'!$A$2:$A})
B2  =SORT({Performance!$F$2:$F;Performance!$H$2:$H;'Contributions/Withdrawals'!$B$2:$B})

...almost gets me there, but the transactions are not lining up with the correct dates. Google Sheets is ordering the amounts from smallest to largest. What I end up with is this:

    A           B
1   DATE        AMOUNT
2   01/05/22    -$7,000.00
3   01/05/22    -$6,602.72
4   01/05/22    -$6,602.39
5   01/05/22    -$6,601.30
6   01/05/22    -$6,596.40
7   01/05/22    -$6,587.10
8   01/05/22    -$4,994.88
9   01/05/22    -$3,315.26
10  01/05/22    -$3,284.91
11  01/05/22    -$1,521.54
12  02/05/22    $400.00
13  03/05/22    $700.00
14  03/10/22    $700.00
15  03/15/22    $700.00
16  03/31/22    $1,605.00
17  03/31/22    $3.249.00
18  03/31/22    $3,731.00
19  03/31/22    $5,160.00
20  03/31/22    $6,348.00
21  03/31/22    $6,532.00
22  03/31/22    $6,786.00
23  03/31/22    $6,838.00

Any help would be appreciated. Thanks!

Upvotes: 0

Views: 69

Answers (1)

Martín
Martín

Reputation: 10185

You are very close indeed! You should join both ranges in order to sort them by the first column:

=SORT({Performance!$B$2:$B;Performance!$C$2:$C;'Contributions/Withdrawals'!$A$2:$A,Performance!$F$2:$F;Performance!$H$2:$H;'Contributions/Withdrawals'!$B$2:$B})

(You may need to change that only comma to a inverted slash if you have another locale settings)

Upvotes: 0

Related Questions