Turbocharging Excel

Posted: 2023-12-17

Aka: Fast data retrieval from a database.

In a lot of enterprises that Process IQ had a pleasure to work with, there is always an Excel spreadsheet that implements a certain business process. One of the more painful aspects of Excel is how the data is getting there. There are few ways: - Manual data entry (🤷 although it can be pretty easy especially with built-in data validation) - Linking a worksheet to another worksheet on the shared drive (👎 ugly, evil and hideous) - Pulling the data from a database (👍👊👊)

We are big fans of bringing in the data via a database query. This helps to maintain data quality, perform audits or other regulatory reporting and later restructure the process to use more streamlined UIs or even move the process / application to the cloud.

There are few ways to query the database from Excel, but the one that is fairly fast is to use WorkbookQuery. The code below shows how to do it from a VBA level, which allows for additional parametrization of SQL.

WorkbookQuery is a relatively cheap trick that can help to restructure spreadsheets that may contain even few hundred thousand records (yes we have seen such monsters 🤕). And the nice thing is reduction of data load times. With cloud-hosted database, loading 100,000 records takes only 15 seconds now - way faster than opening the spreadsheet from a slow remote drive.

Here is VBA for those who are interested in

Private Sub btnRefresh_Click()
On Error GoTo ErrorHandler

    Dim wq = Me.Parent.Queries("Query1")
    wq.Formula = "let Source = Odbc.Query(""dsn=" & dsn & """, """ & sql & """) in Source"
   
    Dim conn As WorkbookConnection
    Set conn = lo.QueryTable.WorkbookConnection
    lo.QueryTable.BackgroundQuery = False
    conn.Refresh

    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description + " at " + Err.Source, Error.vbExclamation
End Sub

Image by Herbert Aust from Pixabay

Scenario analysis for Natural Gas options

Posted: 2023-11-12

Using Excel for decision making again!

When trading or pricing options, there is always a dilemma what would be best structure for given assumed market scenario. Here are some of the choices: - type of structure: put, call, calendar spread, fence, collar, condor, 1x2, etc - strike - maturity - entry / exit slippage - execution and brokerage costs

A seasoned investor would have a good intuitive approach which structure would create most of opportunities with minimal risk, but how do you gain such knowledge?

One way to do it is to use Excel and Data Table functionality for scenario analysis. Data Table can easily run 2 dimensional scenarios which are the most common for options: What would be the option price for a given futures price and time to expiry.

In this post we’re going to analyse a simple Natural Gas Call against January 2021 contract. We’re using Black model, however the Data Table also works for advanced models. As usual, Excel worksheet is here.

The following picture shows option pricer parameters. Additionally there is a matrix showing option prices for given market move on given day.
As the time progresses our call option looses time value, however if market goes up we can see sudden value increases. For example if we bought this option on 19 Dec 2020 (a day before expiry) for USD 0.019, a 5% move up in the market would value this option at USD 0.098 - an increase of more than 5 times! This is a phenomenal leverage, but trade-off is that our investment would be worthless next day.

Here’s the chart showing how time value decays as time progresses for different scenarios. For out of the money options as they are nearer the expiry their value becomes 0. On the other hand, the in the money options converge to their intrinsic value.

Photo by Alexander Schimmeck on Unsplash

How to make your boss happy

Posted: 2023-11-05

By speeding up that boring reconciliation with Excel.

A very frequent activity in any business is checking if an invoice, order list or any other data sets are correct. This is one of our obsessions here at Process IQ and we do this very frequently. One way to do it is to manually go through each item and check whether they match. The other way is to use a bit of automation. Excel is perfect if dataset is relatively small and the task is ad-hoc. In this post we are going to talk about two-way reconciliation. Unlike an SQL database where such two-way rec is called full outer join, the Excel doesn’t have a native function therefore we will use what’s available. As usual, Excel worksheet is here.

The setup

We are going to compare two systems PRODUCTION and UAT if generated invoices are matching. Here’s the example datasets, which is only few rows for the sake of simplicity.

This is a very small datasets and we can see that invoice 3 is missing in UAT. Additionally, UAT has invoice 7 which is not present in PRODUCTION system.

So, here’s the trick - we’re going to use Pivot table. Let’s combine these two tables to make one big table with additional attribute where the data is coming from. The PRODUCTION amounts will have to be negated which are in column AmountNew

Once the data is combined, we can then run a Pivot table with Data being Sum of AmountNew and Rows mapped to Client column. Here’s how it looks like

We can clearly see that invoices from Blue Circle and Round Square have differences between two systems in questions. Additionally, second pivot reconcilies invoices by id.

Finally Excel pivot table has a nice feature to drill into cell content. Clicking on Blue Circle will show composition of data from two systems.

Photo by Cookie the Pom on Unsplash

Taking chances in Monte Carlo

Posted: 2023-10-29

One of the cool ways to learn few tricks with Excel is to use it as a Monte-Carlo simulator to price options. For simple options like the ones traded against Nymex Henry Hub futures, there are closed form formulas available, however where’s fun there?

Basic Monte Carlo algorithm is actually quite simple: - we are going to throw a computer dice using Excel function Rand, this way we get a number from 0 to 1. This will be our “probability” - then we will use function Norm.s.inv to convert this probability into a draw from a normal distribution - next we calculate futures price at option expiry using this formula

$$ P = P_0 \cdot e^{( \frac {-\sigma^2 \cdot \tau} 2 + \sigma \sqrt \tau {dw})} $$ , where: $P_0$ is current futures price, $\sigma$ is volatility, $\tau$ is time fraction i.e. $\frac {expiry - today} {365}$, and $dw$ is normal draw from above

All the parameters required to price options are conveniently specified in worksheet’s panel. Details, as usual, in the attached Excel spreadsheet.

To finally calculate option fair value price, all we have to do is to average simulated option payouts. This chart shows distribution of generated futures prices.

Pricing exchange traded options using Monte Carlo is an over-kill, however Excel worksheets can be used to generate required scenarios to price more complicated instruments like storage contracts or transport rights. Stay tuned.

Photo by Heather Gill on Unsplash

How to make money in Natural Gas with Excel

Posted: 2023-10-20

One of the strategies is to buy cheap summer gas, store it for few months, and sell as expensive winter gas. Easily said than done. But how much should you pay for the storage facility and how to hedge the risk?

In this post we are going to review a simple gas storage model with static replication. This is an educational exercise and it is designed for simplicity.

Natural Gas storage is a critical function that operators use to balance the demand and supply especially during summer / winter seasons. Historically the heating demand for Natural Gas during winter is much higher than summer power burn and industrial demand which is reflected in the gas forward curve.

As mentioned earlier, winter Natural Gas demand is typically higher than available supply hence winter prices are also typically higher than summer prices. Gas storage owners use this phenomenon to buy “cheap” gas during summer months and sell during more expensive winter months.

Let us look at a simple storage model with 1y firm contract with no ratchets, no injection, draw and financing costs and no bid-offer or locational spreads. The hedge will follow static replication method, which means we are not taking volatility into consideration just price levels.

To solve optimization problem, we need to first define a target function. We are going to maximize revenue by buying gas and selling it later. The schedule will be subject to constraints: - maximum storage level cannot be greater than 100,000 dekatherms - maximum daily injection rate cannot be greater than 2,000 dekatherms - maximum daily withdrawal rate cannot be greater than 4,000 dekatherms

This formula describes our target function: $$ \max \sum Q{ij}(P{j}-P{i}) $$ , where: $Q{ij}$ means we are buying $Q$ dekatherms in month $i$, keeping it in storage and selling the same amount in month $j$. $P{i}$ and $P{j}$ are respective purchase and sell prices.

We are going to skip constraints for brevity, the target formula and constraints are implemented in attached Excel sheet.

Using Excel solver shows that we can make revenue of USD 39,520 so if storage costs less than that we can make some profit.

Here is calculated hedging plan. For example, we need to buy 3 April’21 contracts at price 2.921 and sell 3 Jan’22 contracts at price 3.294 to hedge the P&L.

This chart shows amount of gas in storage for calculated plan vs the forward prices. The solver did an excellent job and constrained maximum storage level to 100,000 as was required.

Photo by Josh Appel on Unsplash

Help! My Excel is too slow

Posted: 2023-10-14

Quick tip for those who suffer with slow Excel syndrome (SES). The main culprit are typically too much data and large number of volatile functions. We can talk how to solve too much data in Excel the other day, but let’s take a look at volatile functions first.

These functions are considered volatile: - NOW() - TODAY() - OFFSET() - INDIRECT() - VLOOKUP() - HLOOKUP() - RAND() - RANDBETWEEN()

In case a worksheet has a deep calculation tree (for example a cell containing value from Today() function is input directly or indirectly to other cells) then any change on the worksheet will force Excel to rerun all calculations.

The solution to this problem is to replace Today with TodayNV. Here’s the VBA:

Public Function TodayNV()
Begin
	Application.Volatile False
    TodayNV = Today
End

This way TodayNV will not be calculated during every change, however in order to recalculate the cell containing TodayNV run either Ctrl+Alt+F9 (full worksheet recalculation) or press F2 and then Enter on that cell.

Other trick, especially when spreadsheet is heavily dependant on Vlookup or Hlookup functions is to replace them with combination of Match and Index. Here is an example:

Vlookup example:

Match and Index example:

Photo by Krzysztof Niewolny on Unsplash

A little bit of Excel Automation

Posted: 2023-10-05

Excel applications are the good, the bad and the ugly of trading or logistics process. Spreadsheets are great as a simple database with couple of formulas to act as a business logic, however what happens when a spreadsheet becomes integral part of the business process?

There is a reason for this and this reason is either to support a new business initiative or to implement workaround due to an existing system deficiency. The situation becomes very ugly very quickly. The new process means copying and pasting data into Excel and then copying and pasting into Outlook to communicate it to another group. Some of the copy and paste errors we have seen in our career were counted into hundreds of thousands of dollars.

There are couple of tricks that can help to fortify Excel worksheets:
- Lock the cells, such as only few input cells are editable - No overriding of formulas. For example, two trades are represented as two separate records rather than a single record with additional formula for combined quantity - Control sums with conditional formatting if values are outside of tolerance. For example, a scheduling spreadsheet can show supply and market for trades and storage and conditional formatting can flash warning in case of imbalance - Our favorite: Adding a bit of VBA to send an email via Outlook just by pressing a button

The last point is also a great time saver. Simply pressing a button opens up a new Outlook email with pre-populated content. This reduces number of copy-paste errors. Here how it works in practice:

Data input

Pre-populated email

Here is VBA for those who are interested in

Private Sub btnSendEmail_Click()
On Error GoTo ErrorHandler
    Dim EmailTo As String

    EmailTo = Me.Parent.Names("EmailTo").RefersToRange.Value
    Dim EmailSubject As String

    EmailSubject = Me.Parent.Names("EmailSubject").RefersToRange.Value
    Dim EmailBody As Range

    Set EmailBody = Me.Parent.Names("EmailBody").RefersToRange
    SendEmail EmailBody, EmailTo, EmailSubject
    Exit Sub

ErrorHandler:
    MsgBox "Got Error: " & Err.Description, vbExclamation
End Sub

Private Function SendEmail(rng As Range, eTo As String, eSubject As String)
    Dim htmlContent As String, i As Long, j As Long
    htmlContent = "<table>"

    For i = 1 To rng.Rows.Count
        htmlContent = htmlContent & "<tr>"
        For j = 1 To rng.Columns.Count
            htmlContent = htmlContent & "<td>" & rng.Cells(i, j).Value & "</td>"
        Next
        htmlContent = htmlContent & "</tr>"
    Next
    htmlContent = htmlContent & "</table>"

    Dim OutApp As Object, OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = eTo
        .Subject = eSubject
        .HTMLBody = htmlContent
        .Display
    End With
End Function