Profit Loss Calculation

Community Support
1

Hi Q Experts,

Please help!

I would like to calculate a running profit and loss across a range of assets. 

(I can work out a single asset using a by clause and update using sums). But working out the profit and loss across all the assets is giving me trouble.

I’m finding it hard to access the last profit and loss of the other instruments if that makes sense?

Thanks,

Andrew C

2

Hi Andrew,

If you’ve already got a table with the profit and loss calculated it should be as easy as adding a column, r, to track the overall PNL by sym then have a column, tot_pnl, to calculate the running sum of the PNL over all assets.

This could be done using the following:

update tot_pnl:sums r from update r:deltas pnl

by sym

from tablewithpnl

Thanks,

Luke

3

Hi Andrew,

Here’s an example with a simplified table to clarify how the update statements are working in the previous answer.

Given the following table, ‘tablewithpnl’:

q) tablewithpnltime sym pnl ------------------------00:00:00.577 sym1 00:00:08.938 sym1 -8.75 00:00:09.049 sym1 -9 00:00:19.881 sym1 -22.5 00:00:19.881 sym1 -25.5 00:00:29.049 sym2 00:00:39.881 sym2 50 00:00:49.881 sym2 -25
    

We can use an update statement to add column r, calculating the difference between consecutive PNLs by sym.

This gives the table below.

q) update r:deltas pnl by sym from tablewithpnl

time sym pnl r

--------------------------------

00:00:00.577 sym1

00:00:08.938 sym1 -8.75

00:00:09.049 sym1 -9 -0.25

00:00:19.881 sym1 -22.5 -13.5

00:00:19.881 sym1 -25.5 -3

00:00:29.049 sym2

00:00:39.881 sym2 50

00:00:49.881 sym2 -25 25

The next update statement is then applied to this to add the tot_pnl column that calculates

the running sum of the PNL across all assets. 

The statement is now as shown previously, resulting in a table as below with a running sum of PNL across all assets:

q) update tot_pnl:sums r from update r:deltas pnl by sym from tablewithpnl

time sym pnl r tot_pnl

------------------------------------------

00:00:00.577 sym1 0

00:00:08.938 sym1 -8.75 0

00:00:09.049 sym1 -9 -0.25 -0.25

00:00:19.881 sym1 -22.5 -13.5 -13.75

00:00:19.881 sym1 -25.5 -3 -16.75

00:00:29.049 sym2 -16.75

00:00:39.881 sym2 50 -16.75

00:00:49.881 sym2 -25 25 8.25

Hope this helps,

Luke