Formula

Overview

1. Create formulas in database, and calculate

Story

I'm building a system for calculate finance number of company, e.g EPS, ROE, ROA, BPS.... As you see, there is so many different formulas, at first, I write formulas in this form:

class ROE(FormulaBase):
    name = u'ROE'
    unit = u'%'
    description = u''
    dependsList = []
        
    @classmethod
    def formula(cls, dataSource):
        d = dataSource

        netIncome = d.sum(d(u'Net Incoming', d.incomingStatement), 3, 0)
        se2 = d(u'Stockholders’ Equity', d.balanceSheet)
        se = d.mov(d(u'Stockholders’ Equity', d.balanceSheet), -4)
        seAvg = (se + se2) / 2
        return ((netIncome / seAvg) * 100) 

Note that the formula method do no calculate finance number directly, they return a proxy object that contains detail of formula. I will calculate it later, so that I can know which data of report is lack. Because when I evaluate all operand, that's the best time to see which data is lack. If I calculate it directly, I can't get benefit like this. That's why I prefer getting formula operations and operands rather than calculate them directly. But however, it is ugly enough. Can you image if you have a lots of formula like this, and you have to change them, manage them. It is a difficult job to do so. What's more, they can't be trace. I have to maintain "dependsList", so that I can know what I have to update if I modify this formula. Things changing so often indicate that they should not be code, they should be data. So what I think is put this formulas into database. First, I can manage they easily, I can even write a GUI to manage these formulas. Second, I can trace this formula automatically. That's why I build this system.

There is more benefit to store formula in database. They are merely data, this indicate that you can calculate them everywhere you like, e.g. you can calculate them in a front-end PHP web page.

Create formulas

It is simple to create a formula in this formula system. Let's show you how to do:

a = ImmediateValue(value=10.0)
b = ImmediateValue(value=30)
op = Operation(operation=u'+')
c = ImmediateValue(value=10)
op2 = Operation(operation=u'/')
        
formula = Formula()
formula.append(a)
formula.append(b)
formula.append(op)
formula.append(c)
formula.append(op2)

This formula equals to "(10.0 + 30.0) / 10.0", it looks strange. Do you remember something in data structure textbook? Yes, they are "suffix-formula", they are not so convenient for human to use, but easy to calculate for computer.

It's time to calculate them.

print formula.calculate()

Result:

4

Like magic..., but... wait, this is a useless formula! What can we do with a non-variable formula? The calculation result is 4 forever! Yeah, I know that's a garbage formula. Now let's see some really useful formula.

a = Variable(key='a')
b = Variable(key='b')
one = ImmediateValue(value=1)
two = ImmediateValue(value=2)
add = Operation(operation=u'+')
sub = Operation(operation=u'-')
mul = Operation(operation=u'*')
div = Operation(operation=u'/')

formula = Formula()
formula.append(a)
formula.append(b)
formula.append(add)
formula.append(b)
formula.append(a)
formula.append(sub)
formula.append(one)
formula.append(add)
formula.append(mul)
formula.append(two)
formula.append(div)

Here we just create a sum formula, this formula equals to "f(a, b) = ((a + b)*(b - a + 1)) / 2 = a + a + 1 + a + 2 + ... b". Let's calculate it with some variables.

print formula.calculate(a=1, b=100)
print formula.calculate(a=1, b=1000)
print formula.calculate(a=500, b=1000)

Result:

5050.0
500500.0
375750.0

If you don't like arguments in dictionary form, you can write this, too.

a2 = Variable(index=0)
b2 = Variable(index=1)

print formula2.calculate(1, 100)
print formula2.calculate(1, 1000)
print formula2.calculate(500, 1000)

Sometimes you'd like to use another formula in your formula, you can do this:

formulaRef = FormulaOperand(ref=formula)
two3 = ImmediateValue(value=2)
mul3 = Operation(operation=u'*')

formula3 = Formula()
formula3.append(formulaRef)
formula3.append(two3)
formula3.append(mul3)

print formula3.calculate(a=1, b=100)

Result:

10100.0

They are useful formula now. But.... isn't it too ugly to create formula like this? For this reason, I create another module, for building formula easily. How about building formula in this way:

a = FormulaMeta(Variable(key='a'))
b = FormulaMeta(Variable(key='b'))

formula = getFormula( ((a + b) * (b - a + 1)) / 2 )

formula4 = getFormula(FormulaMeta(formula) * 2)
print formula4.calculate(a=1, b=100)

It is elegant and human readable. But however, these are too simple example. Let me introduce some more complex usage.

A more complex example

What about some operands that read data from database? You can always get benefit from OO design.

class ReportOperand(Operand):
    using_options(inheritance='multi')
    using_options(tablename='table_operand')
    
    # name of finance's record filed to get value from table
    name = Field(Unicode(32), nullable=False)
    # report table to get data
    report = ManyToOne('Report', required=True)

    def toString(self):
        """Get formula in string form
        
        """
        return '(%s of %s)' % (self.name, self.report.name)
    
    def getValue(self, company, yearSeason):
        record = ReportRecord.get_by(report=self.report, company=company, yearSeason=yearSeason)
        table = record.contentCache
        return table[self.name]

Now you can write formula for calculate finance number.

share  = FormulaMeta(ReportOperand(name=u'Share', report=balanceSheet))
profit = FormulaMeta(ReportOperand(name=u'Net Incoming', report=incomingStatement))
EPS = getFormula( (share / 10) / profit )

You just build a EPS formula in database! Let's calculate EPS of some company.

company = Company.get_by(id=1234)
print EPS.calculate(company, 200801)

Result:

15.7115910157

That's amazing! What if I like to get a formula in string form?

print EPS.toString()

Result:

(((Share of BalanceSheet) / 10.0) / (Net Incoming of IncomingStatement))

Here you are :P

Author

Victor Lin ( bornstub@… ) http://victorlin.serveftp.org/programming/