Aggregating Signed Commodity Futures Positions by Delivery Period
Article Quant Q&A · Author: Saïd Maanan
Summary
The document addresses how to summarize commodity futures volumes by commodity and tenor, with purchases positive and sales negative. The suggested workflow first converts each trade’s volume into a signed quantity using its buy or sell direction. It then groups the records by tenor and commodity, sums the signed volumes, and reshapes the result so tenors appear as rows and commodities as columns.
This provides a compact aggregation method for position reporting, but the answer does not implement the requested historical selection or explain how yearly and quarterly futures cascading changes delivery-period exposures. It uses a small illustrative dataset and explicitly notes that it lacks access to the questioner’s actual records. The resulting grouping therefore works only if the tenor field already represents the desired delivery periods and any cascading adjustments have been handled separately.
Key ideas
- Convert buys to positive volume and sells to negative volume before aggregation.
- Group signed volume by tenor and commodity, then sum within each group.
- Reshape the grouped results so delivery tenor is the index and commodities are columns.
- Cascading rules and historical filtering require additional logic beyond the example.
Tags
Full text
# Commodity Futures Cascading in Python
# Commodity Futures Cascading in Python
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.
## Answer by oronimbus (score 1)
https://quant.stackexchange.com/a/71031
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.Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.