New Logistics for Cash Flow Modellers – Github Repos and Excel Labs

The world of open-source software has driven innovation, fuelled collaboration worldwide and yielded libraries to spawn a whole range of new ideas. One major influence has been Github, which is a type of repository. Imagine dropbox of documents, but for programmers to share, duplicate, refine and track changes. There was some scepticism when Microsoft took the plunge and bought it for an astronomic price. Would the corporate, infamous for self-promotion, nurture this open-source project? Thankfully, the consensus appears to be that it has continued to be a resounding success.

To date, Github hasn’t been a great repository for Excel spreadsheets. The tracked changes feature does not work for different versions of Excel files. For example, if someone changes cell A1 from Value X to Value Y, Github will not highlight these changes. This is very different to text-based coding where each line and character of code is tracked for every coding author to see who made the change. In Github terms you literally track the ‘blame’ apportioned to each change: who made and “pushed” the change, and at what time.

But now Github is incredibly useful for advanced Cash Flow modellers. If they like working with Lambda Functions in Excel, they can benefit from Github’s platform power. If they benefit from Github (more on that below), then Corporates may ensure analysts write formulae only once for an entire national or international team. They can neatly document Cash Flow functions to reduce the risk management issues Excel modellers are embroiled with today.

At the heart of the interaction between Github and Excel are Github Gists. These are shareable snippets of code. As per below, they can be shared via internet links (URLs), embedded in web page or simply circulated via email and social media.

Getting Started

Cash Flow modellers can take the following simple steps to explore Github Gists and Excel:

  1. Firstly, download Excel Labs which is like a code or text editor that sits within the Excel workbook interface.
  2. Secondly, get a account.
  3. Go to the Github Gist page and create any new Lambda function.
  4. Copy a shareable Gist URL
  5. Use Excel Labs to import the Gist URL.

Github Gists

A Github URL is shareable with anyone. For example:

The Github Gist content looks like the following embedded item here:

/* Add_TZero_To_The_Right_Of
This function shows “0” (or nothing if the user wants) in a nearby right-hand cell next to an existing cell.
This helper function is used to indicate whether Time Zero is displayed or nothing at all.
In a Discounted Cash Flow, modellers often want to show the explicit Time Zero (aka: “Day-1”) the moment
the transaction is closed or the cash flow is assumed to start.
Lambda Function: Add_TZero_To_The_Right_Of( existingCell, displayTZero )
Existing Value: e.g. this could be a label like “Months” intended to be located at the left of the cash flow.
DisplayTZero: is an optional entry. 1 = display zero “0”, otherwise display nothing and provide empty value “”
IF(ISOMITTED(displayTZero), HSTACK(existingCell, “”),
IF(displayTZero=1, HSTACK(existingCell, “0”),
HSTACK(existingCell, “”)))

Between “/ * ” and ” * / ” you can write any comments that will be ignored by the Excel workbook Lambda function editor. This helps document anything your team needs to know about your Lambda function.

Excel Labs Add-In

The Excel Labs Add-in looks like the following when it has imported the above Github Gist:

When the Github Gist is downloaded successfully into your Excel Workbook, start typing and you should see your imported Lambda function appear quickly like any other ready-made Excel formula.

Leave a Reply