Mackenna Dsouza

The mid office function at a startup natural gas trading desk within a large midstream energy company (Martin Energy Trading, LLC), needed a system to report on end of day P&L for physical and financial natural gas. Traders at the firm put on fixed price, basis, index and gas daily / cash financially settled and physical positions. The team included one financial trader, one physical trader and one scheduler to begin with. Natural gas was the only commodity traded by the firm.

At the outset, the mid office required a system to capture trades, capture trader marks and report daily p&l to match trader estimates. An operational, reliable, cheap system was required for this USD 30 million dollar startup portfolio. The long term plan was to migrate to a traditional ETRM system as the portfolio grew to USD 120 million. Since the startup decided to move quickly, the time and cost of evaluating and implementing a traditional ETRM would set then back several months if not a year at best.

A quick solution implemented in just a few months was called for.

It was decided to build an Excel based blotter for trades and trader marks. Export the data in the trade blotter and price curve sheets to a MYSQL 5.5 RDBMS. This provided for trade and price curve capture.

Contract expiry calendar was retrieved from ICE at the time. This provided contract information and contract expiry. Calendar information was ingested in to the RDBMS.

Deal types spanned all natural gas linear instruments. Deal types included Fixed Price, Basis, Index and Gas Daily for Texas and North East locations with varying flow dates and volumes.

An Excel-based generic deal capture screen was developed to capture deals into a blotter. This sheet was run on the desktop of the trader. At some point during the day, the trader would push some or all of the deals to the backend MYSQL database. This push was for deals done during the current trading day. The trader had a separate sheet where ALL trades attributed to the specific trader were pulled from the backend MYSQL database. The push of the data from a dynamic Excel range was implemented using VBA and direct connectivity to the backend MySQL database. A single deals SQL table built to capture all types of deals was developed in collaboration with the traders and mid-office personnel.

A separate worksheet was developed to publish trader marks against which end of day profit and loss ( eod P&L) would be calculated. Nymex, Basis, index and gas daily marks were entered and pushed to the backend database via this worksheet and the backing VBA macros. Financial and Physical market price observations were input on a daily basis at the end of the trading day.

Once deals and marks were input to the back-end database, a mid-office resource would decompose each deal into relevant legs based on the expiry calendar retrieved from ICE on a daily basis. Using this calendar, deal flow start dates and end dates would be used to create relevant futures, basis, index, and cash exposures. Each exposure would have a trade date, an exposure start date, and an exposure end date.

Futures start date would be the later of the trade date or futures trading start date from the ICE expiry calendar. Expiry would correspond to the futures expiry date. Basis start date would be the later of the trade date or basis OTC trading start date obtained from ICE expiry calendar. Expiry would correspond to the OTC basis contract expiry date. Index exposure for the month would correspond to Inside Ferc or Platts index for entire month volume across the deal term. The index exposure will have a corresponding exposure start date and expiry date. When Gas Daily (GDD) prices for the cash month are published, daily exposures with a start date corresponding to each individual day expiring that day as well as balmo pricing where relevant is added to the exposures related to the deal.

To calculate P&L, an as-of-date is added to the mid office user’s spreadsheet. This as-of-date can be a date on the reporting date or prior. This date is used to identify all exposures in the deal exposure table that would have been open on the as-of-date. The exposures are mapped to relevant prices using a pandas data frame merge index on exposure date and product type.

The raw exposure as a function of price granularity provided the capability to summarize data in the P&L report. This table for the as-of-date was retrieved into the mid-office personnel’s spreadsheet. Using VBA the range in the spreadsheet was tied to a pivot table. Mid-office personnel could then slice and dice the range using a pivot table within Excel. Pivot table allowed slice and dice for trader level, location level, contract type, and term reporting.

The entire greenfield project was developed in 2 months with 2 resources. The technology used included Excel, Excel VBA, python, pandas and a MYSQL 5.5 RDBMS. This system is still operational after 5 years of use. Significant modifications have been made as the business has been acquired and grown in size. Options support has subsequently been added. The gas desk uses this solution in conjunction with a Triple Point CXL implementation, a this solution is significantly easier to use for flash P&L and subsequent analytics. It is also used to validate numbers obtained from CXL by the gas desk.