0
$\begingroup$

I am new to Quantitative Finance so please bear with me. I have the following data set:

> head(df, 20)
# A tibble: 20 × 15
  deal_id book   counterparty   commodity_name commodity_code executed_date       first_delivery_date last_delivery_date  last_trading_date   volume buy_sell trading_unit tenor   delivery_window strategy  
 <int> <chr>  <chr>          <chr>          <chr>          <dttm>              <dttm>              <dttm>              <dttm>               <int> <chr>    <chr>        <chr>   <chr>           <chr>     
 1       0 Book_7 Counterparty_3 api2coal       ATW            2021-03-07 11:50:24 2022-01-01 00:00:00 2022-12-31 00:00:00 2021-12-31 00:00:00  23000 sell     MT           year    Cal 22          NA        
 2       1 Book_7 Counterparty_3 oil            B              2019-11-10 18:33:39 2022-01-01 00:00:00 2022-12-31 00:00:00 2021-11-30 00:00:00  16000 sell     bbl          year    Cal 22          NA        
 3       2 Book_4 Counterparty_3 oil            B              2021-02-25 11:44:20 2021-04-01 00:00:00 2021-04-30 00:00:00 2021-02-26 00:00:00   7000 buy      bbl          month   Apr 21          NA        
 4       3 Book_3 Counterparty_3 gold           GC             2022-05-27 19:28:48 2022-11-01 00:00:00 2022-11-30 00:00:00 2022-10-31 00:00:00    200 buy      oz           month   Nov 22          NA        
 5       4 Book_2 Counterparty_3 czpower        CZ             2022-09-26 13:14:31 2023-03-01 00:00:00 2023-03-31 00:00:00 2023-02-27 00:00:00      2 buy      MW           quarter Mar 23          NA        
 6       5 Book_1 Counterparty_3 depower        DE             2022-08-29 10:28:34 2022-10-01 00:00:00 2022-10-31 00:00:00 2022-09-30 00:00:00     23 buy      MW           month   Oct 22          NA        
 7       6 Book_3 Counterparty_1 api2coal       ATW            2022-12-08 08:17:11 2023-01-01 00:00:00 2023-01-31 00:00:00 2022-12-30 00:00:00  29000 sell     MT           quarter Jan 23          NA        
 8       7 Book_3 Counterparty_2 depower        DE             2020-10-16 17:36:13 2022-03-01 00:00:00 2022-03-31 00:00:00 2022-02-25 00:00:00      3 sell     MW           quarter Mar 22          NA        
 9       8 Book_7 Counterparty_1 api2coal       ATW            2020-10-13 09:35:24 2021-02-01 00:00:00 2021-02-28 00:00:00 2021-01-29 00:00:00   1000 sell     MT           quarter Feb 21          NA        
10       9 Book_2 Counterparty_1 api2coal       ATW            2020-05-19 11:04:39 2022-01-01 00:00:00 2022-12-31 00:00:00 2021-12-31 00:00:00  19000 sell     MT           year    Cal 22          NA        
11      10 Book_6 Counterparty_1 oil            B              2022-03-03 08:04:04 2022-08-01 00:00:00 2022-08-31 00:00:00 2022-06-30 00:00:00  26000 buy      bbl          month   Aug 22          NA        
12      11 Book_3 Counterparty_1 gold           GC             2021-05-09 18:08:31 2022-05-01 00:00:00 2022-05-31 00:00:00 2022-04-29 00:00:00   1600 sell     oz           month   May 22          NA        
13      12 Book_5 Counterparty_2 oil            B              2020-08-20 11:54:34 2021-04-01 00:00:00 2021-04-30 00:00:00 2021-02-26 00:00:00   6000 buy      bbl          month   Apr 21          Strategy_3
14      13 Book_6 Counterparty_2 gold           GC             2020-12-23 16:28:55 2021-12-01 00:00:00 2021-12-31 00:00:00 2021-11-30 00:00:00   1700 sell     oz           month   Dec 21          NA        
15      14 Book_2 Counterparty_1 depower        DE             2021-08-11 12:54:23 2024-01-01 00:00:00 2024-12-31 00:00:00 2023-12-28 00:00:00     15 buy      MW           year    Cal 24          NA        
16      15 Book_5 Counterparty_1 czpower        CZ             2022-02-15 07:45:24 2022-12-01 00:00:00 2022-12-31 00:00:00 2022-11-30 00:00:00     28 buy      MW           month   Dec 22          Strategy_3
17      16 Book_7 Counterparty_2 oil            B              2021-05-19 07:37:05 2022-02-01 00:00:00 2022-02-28 00:00:00 2021-12-31 00:00:00  11000 buy      bbl          quarter Feb 22          Strategy_3
18      17 Book_4 Counterparty_3 depower        DE             2022-02-01 12:34:49 2022-06-01 00:00:00 2022-06-30 00:00:00 2022-05-31 00:00:00     14 sell     MW           month   Jun 22          NA        
19      18 Book_2 Counterparty_3 czpower        CZ             2022-06-02 09:39:16 2023-02-01 00:00:00 2023-02-28 00:00:00 2023-01-30 00:00:00     21 buy      MW           quarter Feb 23          NA        
20      19 Book_3 Counterparty_1 czpower        CZ             2021-10-28 12:41:11 2022-09-01 00:00:00 2022-09-30 00:00:00 2022-08-31 00:00:00      3 sell     MW           month   Sep 22          NA        

And I am asked to extract some information from it while applying what is called Yearly and Quarterly Futures Cascading, which I do not know. The question is as follows:

Compute the position size (contracted volume) for a combination of books and commodities, for a selected time in history. The output format should be a data frame with future delivery periods as index (here comes yearly and quarterly cascading), commodities as column names and total volume as values. Provide negative values when the total volume for given period was sold and positive value when it was bought.

I read some material online about Cascading Futures here and here, but it only gave me a vague idea of what they are about and doesn't help solve the problem in hand. and coding examples in Python are nonexistent.

Can someone please give me a hint as to how to approach this problem? I am a beginner in the field of quantitative finance and any help would be much appreciated.

$\endgroup$
2
  • $\begingroup$ Hi, what language are you using? This seems to be just a question about dataframe manipulation in which case I could give you a solution in Python... $\endgroup$
    – oronimbus
    May 25, 2022 at 14:14
  • $\begingroup$ Hi @oronimbus , I am using Python, and I would be very grateful if you could help me solve this problem. $\endgroup$ May 25, 2022 at 17:40

1 Answer 1

1
$\begingroup$

Ok I'll put this as an answer here. Mind, I don't have access to your data set so I'm kind of winging the answer here. I'd strongly recommend you to familiarise yourself with the pandas package rather than just copy pasting an answer. Really this is more suited for StackOverflow than QSE.

Anyways, I'll use this example here:

df = pd.DataFrame({"volume":[1,2,3],"buy_sell":["buy","buy","sell"],"commodity_name":["a","b","c"],"tenor":["q","q","a"]})

You'll first want to have "directional volume":

df["vol"] = df["volume"] * df["buy_sell"].apply(lambda x: 1 if x == "buy" else -1)

Then just do a simple group by:

df.groupby(["tenor","commodity_name"])["vol"].sum().unstack()

The unstack just puts the level 1 index (=future name) into columns.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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