developer accountants

Contents

• Accountants are de facto developers
• Excel is programming
• Formulas are functions
• Problem solving
• Summary
• BONUS: Getting started with Python in Excel

Accountants are de facto developers

I first heard of Developer Accountants from Silverfin.

And it got me thinking… most accountants are de facto developers.

The de jure job title might suggest otherwise but when the daily activities of accountants are observed, a different picture emerges.

Excel is programming

”…Excel is the world’s most widely used functional programming language.” - Simon Peyton Jones

”…This pisses a lot of developers off because it turns out that people in the accounts department have been doing FP [Functional Programming] for longer than they have.” - Kevlin Henney

That’s right. Spreadsheets rule the profession. In industry and practice, accountants use Excel every day. Whether they know it or not, every time they type an Excel formula they are programming.

Formulas are functions

A function is piece of code that takes something as an input, does something and returns an output.

Let’s take a closer look at the first function most accountants learn: SUM

When you type =SUM( into Excel it will display the interface for you:

SUM(number1, [number2], ...)

SUM is a function that takes one or more numbers and returns the result of adding them all together.

Let me show you what sum looks like in Python (one of the world’s most popular programming languages):

sum(iterable, /, start=0)
# Sums start and the items of an iterable from left to right and returns the total.
#The iterable’s items are normally numbers...

Doesn’t look too different, does it?

But wait, what’s an iterable?

Say you have the following data in Excel:

When you use SUM in Excel, you probably don’t type =SUM(A1+A2+A3+A4). You probably use a cell range and type:

=SUM(A1:A4)

The cell range is an iterable. Each element in the cell range is a reference to a cell.

Here’s the same in Python:

>>> numbers = [10, 20, 30, 40]
>>> sum(numbers)

The only difference is that our iterable is a list of numbers (rather than a cell range).

Problem solving

Okay, so that’s a fairly simple example.

Developers solve complex problems with their programming. But how do they do that?

Well, how do you eat an elephant (personal aside: by the way, I’d rather you didn’t eat elephants)? Piece by piece.

Developers break a single complex problem down into multiple simple problems, solving each one and stitching it all together.

Accountants often do the same in Excel.

Say you have the following data…

…and you want to get the bit before the email domain (the name) for each cell. You could nest some formulas together…

=LEFT(A2,FIND("@",A2)-1)

…and then apply that to each cell (by dragging the formula down)…

…giving you:

What have we done to solve this more complex problem? We broke it down into the following steps:

1. FIND("@",A2): Take the text in the cell reference (A2) and find the position of the delimiter ("@") in that text. “@” is the fourth character in “bob@example.com”
2. Take the text in the cell reference (A2) and return the n characters (where n is the position of the delimiter less one, so that the delimiter is not included in the result). Note: =LEFT(A2,FIND("@",A2)-1) evaluates to =LEFT(A2,4-1)
3. Do this for each cell.

How would a developer do the same in Python?

>>> emails = [
      "bob@example.com",
      "bill@example.com",
    ]
>>> names = []
>>> for email in emails:
      names.append(email[:email.find("@")])
>>> names
['bob', 'bill']

Here the code preforms the following steps for each email in emails:

1. Find the position of the delimiter “@”: email.find("a")
2. Take all the characters of email up to the delimiter’s position: email[:email.find("@")] (for “bob@example.com” this evaluates to email[:3], note that Python starts counting from 0; whereas Excel starts counting from 1)
3. Append these to the names list: names.append(email[:email.find("@")]) (for “bob@example.com” this evaluates to names.append("bob"))

Summary

Developers are people that use programming languages to solve problems.

A programming language is a digital tool that lets you write functions to solve a problem.

Excel is a digital tool that lets you write functions to solve a problem.

Therefore Excel is a programming language.

Accountants use Excel to solve problems.

Therefore accountants are developers.

BONUS: Getting started with Python in Excel

Perhaps you are an accountant and, recognizing that you are also a developer, you want to expand the programming languages that you already know.

Python can be fairly quick to pick up and if you might already have it available to you in Excel.

If you have “Insert Python” listed when you click “Formulas” in the Excel ribbon, you have Python in Excel (luck you!).

Read the docs and get practicing.

Let’s do the above Python implementation again using Excel as our developer environment.

Say you have the following data…

Start a Python function in cell B2 by entering =PY(.

Now enter the following:

email = xl("A2")  # Get the text from the cell reference
name = email[:email.find("@")]  # Get the name

Drag the cell formula down.

You’ll end up with: