Overview

Py2Formula README

About

Py2Formula is a script that converts simple Python functions into formulas compatible with Excel (and similar spreadsheet systems). It has two pieces. The first is the command-line program py2formula.py, which takes a Python source file and prints it out as an Excel formula. The second piece is the FormulaBuilder class which does the actual conversion.

Py2Formula does not support all features of the Python language. It supports features that have analogues in Excel. For example, an if statement in Python will be converted to =IF() for Excel.

Why?

I write a lot of formulas for Excel and for a web platform called Archer. Excel doesn't have very friendly tools for writing complicated formulas (the "missing parenthesis" message is unhelpful), and Archer...well, let's say the version of Archer I'm using features the worst of Excel combined with the worst of an Internet Explorer 6 "development" platform.

I wrote py2formula and FormulaBuilder so that I can write clear, readable conditionals in Python, then just print them out as Excel formulas. Python is a full-featured language. Excel's formula language is not. Sticking to the basics of Python works well, and lets me have readable formulas. It is, in a sense, writing pseudo-code for Excel, and "compiling" it into an actual Excel formula.

Writing Supported Functions

An example

Let's start with an example. I need to calculate battery capacity information. I will define some variables that point to cells in a spreadsheet, then write logic to return the capacity of the battery:

def get_battery_status():
    target_voltage = "B2"
    actual_voltage = "B3"
    target_time = "C2"
    actual_time = "C3"

    if actual_time < target_time:
        return "Failed"
    else:
        if actual_voltage > target_voltage:
            return "Passed, but actual capacity is unknown"
        else:
            return CONCATENATE("Passed with a capacity of ",ROUNDDOWN((actual_time / target_time) * 100, 0), "%")

If this file is called example.py, run py2formula.py --basic example.py and you will get:

=IF(C3<C2,"Failed",IF(B3>B2,"Passed, but actual capacity is unknown",CONCATENATE("Passed with a capacity of ",ROUNDDOWN(((C3/C2)*100),0),"%")))

as the output.

More details on: Writing Functions

FormulaBuilder assumes that one function = one formula. Your Python files can have as many functions as you'd like. Each function will be processed and converted into an Excel formula. All functions should have return statements because that is how FormulaBuilder knows what results to output into a formula.

A simple if statement will have two return values, like so:

def simple_if_example():
    x = "A1"
    if x % 2 == 0:
        return "It is even"
    else:
        return "It is odd"

You can nest if statements, use complex nested logic (and, or, etc.) to create more complicated formulas that would be hard to read in Excel.

More details on: Variables

Variables can be used in two ways in py2formula.

  1. As a reference to cells or ranges. This is done by assigning a string value to the variable. Like so: myrange = "A1:C3". When FormulaBuilder encounters myrange in your code it will output its value, in this case A1:C3. An example would be return SUM(myrange) and result in =SUM(A1:C3)
  2. As a named range (or field in Archer). If you do not declare a variable before using it, FormulaBuilder will simple output the variable name as part the formula. This is useful for named ranges in Excel, or for field names in Archer. The basic get_battery_status example above is not how I use py2formula; I use it almost exclusiveley with named ranges.

Features Supported

Features Supported
Python Feature Excel's Analogue Comment
Functions Formula Whenever a def statement is found, FormulaBuilder creates a formula beginning with the = sign.
if IF(conditions, value-if-true, value-if-false) Nested IFs are allowed.
and AND(condition1, condition2, ...) None
or OR(condition1, condition2, ..) None
! NOT(value) None
return (the value being returned) If you don't return a value, FormulaBuilder translates it into ""
Comparisons (==, !=, <, >, <=, >=) =, <>, <, >, <=, >= None
Basic Arithmetic (+, -, /, *) +, -, /, * If the left-hand argument of addition is not a number, FormulaBuilder assumes it is a string and uses Excel's & operator. This may change in the future. For complete control of building strings, use CONCATENATE(value1, value2, ...) instead.
Modulo (%) MOD(x, y) None
Exponent (^) POWER(x, y) None
Floor division (//) ROUNDDOWN(x, 0) None
Assignment (=) None Variable assignments tell FormulaBuilder to store the variable name and its value for later string substitution. Variables have either global scope, or function scope. FormulaBuilder does not understand scoping within block statements such as if.
True and False TRUE() and FALSE() None
Function Calls Translated literally If you want to use Excel's AVERAGE function in your code, just type it like you would in Excel: AVEREAGE(myPythongVariable). No checking is done to see if the function is valid in Python or Excel.
Numbers A number None
Strings The string, surrounded with double-quotes. Nested quotes are not handled yet.
Empty type (NoneType) Pair of double-quotes None

Python Features Not Supported

This is a massive list, so I won't actually list it. Features such as lists, arrays, comprehensions, classes have no simple analogs in an Excel formula. In a macro or user-defined function, yes, they would be useful. Such is not my goal with this program. If you'd like to write user-defined functions in Python, there is PyXLL. If you want to write a macro in something other than VBA, I suggest you pick up an edition of Visual Studio that includes Visual Studio Tools for Office so that you can use C# instead.