import numpy as np
import pandas as pd

df_futures = pd.read_excel("Crude Oil May 24.xlsx", usecols =["Date","Close*","Volume"], na_values=["-"], thousands=",").dropna(subset=["Date","Close*"]).reset_index(drop=True)
df_futures.rename(columns={"Close*": "Futures Price"}, inplace=True)
df_futures["Date"] = pd.to_datetime(df_futures["Date"], format='mixed')
df_futures["Volume"] = df_futures["Volume"].fillna(0).astype(int)
df_futures = df_futures.sort_values(by="Date").reset_index(drop=True)
display(df_futures)

df_WTI_Spot = pd.read_csv("Cushing_OK_WTI_Spot_Price_FOB.csv", header =4).dropna().reset_index(drop=True)
df_WTI_Spot.columns = ["Date", "WTI Spot Price"]
df_WTI_Spot["Date"] = pd.to_datetime(df_WTI_Spot["Date"], format='%m/%d/%Y')
df_WTI_Spot = df_WTI_Spot.sort_values(by="Date").reset_index(drop=True)
display(df_WTI_Spot)

df_fuel = pd.read_csv("U.S._Gulf_Coast_Kerosene-Type_Jet_Fuel_Spot_Price_FOB.csv",header = 4)
df_fuel.columns = ["Date","Fuel Spot"]
df_fuel["Date"] = pd.to_datetime(df_fuel["Date"], format = "%m/%d/%Y")
df_fuel = df_fuel.sort_values(by="Date").reset_index(drop=True)
display(df_fuel)

def scenario_1():
    no_fuel_gallons = 2000000
    contract_size = 1000
    df = pd.merge(df_futures, df_fuel, on="Date", how="inner")
    today = pd.to_datetime("2022-11-15")
    end_date = pd.to_datetime("2024-03-19")
    start_date = today - (end_date - today)
    df = df[df["Date"].isin(pd.date_range(start= start_date, end=end_date))].reset_index(drop=True)         #Filtering the data to include only the dates from 13/07/2021 to 19/03/2024
        
    def part_a() -> int:
        df2 = df[df["Date"] <= today].reset_index(drop=True)
        df_diff = df2[["Futures Price","Fuel Spot"]].diff().dropna().reset_index(drop=True)
        hist_sd = df_diff.std(ddof=1)
        hist_corr = df_diff.corr().iloc[0,1]
        if hist_corr > 1 or hist_corr < -1:
            raise ValueError("Correlation coefficient must be between -1 and 1.")
        optimal_hedge_ratio = hist_corr * hist_sd.iloc[1]/hist_sd.iloc[0]
        optimal_no_contracts = round(optimal_hedge_ratio * no_fuel_gallons / contract_size)         #Rounding to the nearest integer
        return optimal_no_contracts
    
    def part_b() -> tuple[float, float]:
        future_today = df[df["Date"] == today]["Futures Price"].values[0]
        future_end = df[df["Date"] == end_date]["Futures Price"].values[0]
        fuel_end = df[df["Date"] == end_date]["Fuel Spot"].values[0]
        payoff_futures = part_a() * contract_size * (future_end - future_today)
        realised_cost = - no_fuel_gallons * fuel_end + payoff_futures
        return payoff_futures, realised_cost

    def part_c() -> tuple[float, float, float]:
        basis = df[df["Date"] == end_date]["Fuel Spot"].values[0] - df[df["Date"] == end_date]["Futures Price"].values[0]
        basis_mismatch = df[df["Date"] == end_date]["Fuel Spot"].values[0] - df_WTI_Spot[df_WTI_Spot["Date"] == end_date]["WTI Spot Price"].values[0]
        basis_timing = df_WTI_Spot[df_WTI_Spot["Date"] == end_date]["WTI Spot Price"].values[0] - df[df["Date"] == end_date]["Futures Price"].values[0]
        return basis, basis_mismatch, basis_timing
    
    without_hedge_cost = - no_fuel_gallons * df[df["Date"] == end_date]["Fuel Spot"].values[0]

    return part_a(), part_b(), part_c(), without_hedge_cost

part_a_result, part_b_result, part_c_result, no_hedge_cost = scenario_1()

print(f"Optimal number of futures contracts to buy: {part_a_result:,}")
print("---------------------------------------------------------------------------")
print(f"The payoff from the futures position on 19 March 2024: ${part_b_result[0]:,.2f}")
print(f"The net price that the company pays for kerosene jet fuel on 19 March 2024: ${abs(part_b_result[1]):,.2f}")
print(f"The net price that the company pays for kerosene jet fuel on 19 March 2024 without hedging: ${abs(no_hedge_cost):,.2f}")
print(f"Difference in net price between hedging and not hedging: ${abs(part_b_result[1]) - abs(no_hedge_cost):,.2f}")
print("---------------------------------------------------------------------------")
print(f"The basis on 19 March 2024: ${part_c_result[0]:,.2f}")
print(f"The basis risk due to mismatch of assets on 19 March 2024: ${part_c_result[1]:,.2f}")
print(f"The basis risk due to timing differences on 19 March 2024: ${part_c_result[2]:,.2f}")

Optimal number of futures contracts to buy: 53
---------------------------------------------------------------------------
The payoff from the futures position on 19 March 2024: $-182,850.00
The net price that the company pays for kerosene jet fuel on 19 March 2024: $5,524,850.00
The net price that the company pays for kerosene jet fuel on 19 March 2024 without hedging: $5,342,000.00
Difference in net price between hedging and not hedging: $182,850.00
---------------------------------------------------------------------------
The basis on 19 March 2024: $-80.80
The basis risk due to mismatch of assets on 19 March 2024: $-81.72
The basis risk due to timing differences on 19 March 2024: $0.92

def scenario_2():
    forward_price = 78.00
    no_barrels = 1100000
    futures_contract_size = 1000
    no_futures_contracts = 1100
    capital = 19000000
    cash_rate = 0.04
    margin_rate = 0.03
    start_date = pd.to_datetime("2022-01-12")
    end_date = pd.to_datetime("2023-12-08")
    df = pd.merge(df_futures, df_WTI_Spot, on="Date", how="inner")
    df = df[df["Date"].isin(pd.date_range(start= start_date, end=end_date))].reset_index(drop=True)
    
    def year_transform(start: pd.Timestamp, end: pd.Timestamp) -> float:
        return (end - start).days / 365
    
    print("For strategy 1:")
    def strategy_1() -> float:
        payoff_forward = (df[df["Date"] == end_date]["WTI Spot Price"].values[0] - forward_price) * no_barrels
        payoff_futures = (df[df["Date"] == start_date]["Futures Price"].values[0] - df[df["Date"] == end_date]["Futures Price"].values[0]) * futures_contract_size * no_futures_contracts
        payoff_hedge = payoff_forward + payoff_futures
        return payoff_hedge

    print(f"The outcome of the hedge - the net profit on 8 December 2023: ${strategy_1():,.2f}")
    print("---------------------------------------------------------------------------")
    print("For strategy 2:")
    
    def strategy_2() -> float:
        initial_margin = 6000 * no_futures_contracts
        cash_bal = capital - initial_margin
        margin_call_count = 0
        for i in range(1, len(df)):
            delta_t = year_transform(df.iloc[i - 1]["Date"], df.iloc[i]["Date"])
            margin_interest = initial_margin * ((1 + margin_rate / 365) ** (delta_t * 365) - 1)
            cash_bal = cash_bal * np.exp(cash_rate * delta_t) + margin_interest
            daily_profit = - (df.iloc[i]["Futures Price"] - df.iloc[i - 1]["Futures Price"]) * futures_contract_size * no_futures_contracts
            if daily_profit >= 0:
                cash_bal += daily_profit
            else:
                margin_call = -daily_profit
                margin_call_count += 1
                print(f"Margin call on {df.iloc[i]['Date'].date()}: for an amount of ${margin_call:,.2f}")
                
                if margin_call < cash_bal:
                    cash_bal -= margin_call
                
                else:
                    print(f"")
                    print(f"On {df.iloc[i]['Date'].date()}, cash balance is insufficient for margin call . Cash balance: ${cash_bal:,.2f}, Margin balance: ${initial_margin:,.2f}, Margin call: ${margin_call:,.2f}")
                    print("The futures position is closed immediately due to inability to maintain required margin.")
                    cash_bal -= margin_call
                    break
        
        print(f"Total number of margin calls: {margin_call_count}")
        cash_bal = cash_bal + initial_margin
        print(f"On {df.iloc[i]['Date'].date()}, total capital is: ${cash_bal:,.2f}")
        cash_bal = cash_bal * np.exp(cash_rate * year_transform(df.iloc[i]["Date"], end_date))
        fw_payoff = (df[df["Date"] == end_date]["WTI Spot Price"].values[0] - forward_price) * no_barrels
        PnL = cash_bal + fw_payoff
        print(f"On 8 December 2023:, total capital including interest is ${cash_bal:,.2f}")
        print(f"Forward contract payoff on 8 December 2023: ${fw_payoff:,.2f}")
        
        if PnL < 0:
            cap = 0
            print(f"Total loss on 8 December 2023: ${abs(PnL):,.2f}")
            print(f"The hedge fund's total capital on 9 December 2023 is ${cap:,.2f}. The company is already bankrupt")
        else:
            cap = PnL
            print(f"Total profit on 8 December 2023: ${PnL:,.2f}")
            cap = cap * np.exp(cash_rate * year_transform(end_date, (end_date + pd.Timedelta(days=1))))
            print(f"The hedge fund's total capital on 9 December 2023 is ${cap:,.2f}")

        print("---------------------------------------------------------------------------")
        print("For strategy 3:")
        return cash_bal
    
    def strategy_3() -> float:
        cash = capital * np.exp(cash_rate * year_transform(start_date, (end_date)))
        forward_payoff = (df[df["Date"] == end_date]["WTI Spot Price"].values[0] - forward_price) * no_barrels
        cash_bal = (cash + forward_payoff)*np.exp(cash_rate * year_transform(end_date, (end_date + pd.Timedelta(days=1))))
        print(f"Cash balance at maturity before settlement of the forward contract: ${cash:,.2f}")
        print(f"Payoff of the long forward contract at maturity: ${forward_payoff:,.2f}")
        print(f"Cash balance on 8 December 2023 after cash-settlement of the forward contract: ${(cash + forward_payoff):,.2f}")
        print(f"Capital on 9 December 2023: ${cash_bal:,.2f}")
        return cash_bal
    
    return strategy_1(), strategy_2(), strategy_3()

task_2a_result, task_2b_result, task_2c_result = scenario_2()

For strategy 1:
The outcome of the hedge - the net profit on 8 December 2023: $4,708,000.00
---------------------------------------------------------------------------
For strategy 2:
Margin call on 2022-01-14: for an amount of $1,870,000.00
Margin call on 2022-01-18: for an amount of $1,771,000.00
Margin call on 2022-01-19: for an amount of $1,683,000.00
Margin call on 2022-01-25: for an amount of $2,519,000.00
Margin call on 2022-01-26: for an amount of $1,925,000.00
Margin call on 2022-01-28: for an amount of $231,000.00
Margin call on 2022-01-31: for an amount of $1,463,000.00
Margin call on 2022-02-01: for an amount of $55,000.00
Margin call on 2022-02-02: for an amount of $66,000.00
Margin call on 2022-02-03: for an amount of $2,211,000.00
Margin call on 2022-02-04: for an amount of $2,244,000.00
Margin call on 2022-02-09: for an amount of $330,000.00
Margin call on 2022-02-10: for an amount of $242,000.00
Margin call on 2022-02-11: for an amount of $3,542,000.00
Margin call on 2022-02-14: for an amount of $2,596,000.00

On 2022-02-14, cash balance is insufficient for margin call . Cash balance: $937,270.91, Margin balance: $6,600,000.00, Margin call: $2,596,000.00
The futures position is closed immediately due to inability to maintain required margin.
Total number of margin calls: 15
On 2022-02-14, total capital is: $4,941,270.91
On 8 December 2023:, total capital including interest is $5,313,073.67
Forward contract payoff on 8 December 2023: $-7,843,000.00
Total loss on 8 December 2023: $2,529,926.33
The hedge fund's total capital on 9 December 2023 is $0.00. The company is already bankrupt
---------------------------------------------------------------------------
For strategy 3:
Cash balance at maturity before settlement of the forward contract: $20,503,659.10
Payoff of the long forward contract at maturity: $-7,843,000.00
Cash balance on 8 December 2023 after cash-settlement of the forward contract: $12,660,659.10
Capital on 9 December 2023: $12,662,046.64

	Date	Futures Price	Volume
0	2019-03-26	59.94	685988
1	2019-03-27	59.41	732150
2	2019-03-28	59.30	712209
3	2019-03-29	60.14	705559
4	2019-04-01	61.59	670808
...	...	...	...
1256	2024-03-19	83.47	76814
1257	2024-03-20	81.68	351161
1258	2024-03-21	81.07	266860
1259	2024-03-22	80.63	231101
1260	2024-03-25	81.95	231101

	Date	WTI Spot Price
0	1986-01-02	25.56
1	1986-01-03	26.00
2	1986-01-06	26.53
3	1986-01-07	25.85
4	1986-01-08	25.87
...	...	...
9624	2024-03-19	84.39
9625	2024-03-20	82.79
9626	2024-03-21	81.99
9627	2024-03-22	81.10
9628	2024-03-25	82.41

	Date	Fuel Spot
0	1990-04-02	0.550
1	1990-04-03	0.555
2	1990-04-04	0.560
3	1990-04-05	0.540
4	1990-04-06	0.536
...	...	...
8533	2024-03-19	2.671
8534	2024-03-20	2.606
8535	2024-03-21	2.608
8536	2024-03-22	2.582
8537	2024-03-25	2.583

Forwards & Futures: Valuation, Hedging, and Risk Analysis

1. Data and Environment Setup

2. Scenario 1: Futures Hedge of Different Underlying Assets

Comment on the hedging outcome¶

3. Scenario 2: Forward and Futures Hedging

4. Project Summary

Scenario 1:¶

Scenario 2:¶