Migrating Excel financial model to and Corkscrew calculation in Python Pandas
Question: I'm working on replacing an Excel financial model into Python Pandas. By
financial model I mean forecasting a cash flow, profit & loss statement and
balance sheet over time for a business venture as opposed to pricing swaps /
options or working with stock price data that are also referred to as
financial models. It's quite possible that the same concepts and issues apply
to the latter types I just don't know them that well so can't comment.

So far I like a lot of what I see. The models I work with in Excel have a
common time series across the top of the page, defining the time period we're
interested in forecasting. Calculations then run down the page as a series of
rows. Each row is therefore a `TimeSeries` object, or a collection of rows
becomes a `DataFrame`. Obviously you need to transpose to read between these
two constructs but this is a trivial transformation.

Better yet each Excel row should have a common, single formula and only be
based on rows above on the page. This lends itself to vector operations that
are computationally fast and simple to write using Pandas.

The issue I get is when I try to model a corkscrew-type calculation. These are
often used to model accounting balances, where the opening balance for one
period is the closing balance of the prior period. You can't use a `.shift()`
operation as the closing balance in a given period depends, amongst other
things, on the opening balance in the same period. This is probably best
illustrated with an example:

    
    
    Time              2013-04-01   2013-05-01   2013-06-01   2013-07-01   ...
    
    Opening Balance            0           +3           -2          -10     
    [...]
    Some Operations           +3           -5           -8          +20
    [...]
    Closing Balance           +3           -2          -10          +10
    

In pseudo-code my solution to how to calculate these sorts of things is a
follows. It is not a vectorised solution and it looks like it is pretty slow

    
    
     # Set up date range
     dates = pd.date_range('2012-04-01',periods=500,freq='MS')
    
     # Initialise empty lists
     lOB = []
     lSomeOp1 = []
     lSomeOp2 = []
     lCB = []
    
     # Set the closing balance for the initial loop's OB
     sCB = 0
    
     # As this is a corkscrew calculation will need to loop through all dates
     for d in dates:
    
         # Create a datetime object as will reference it several times below
         dt = d.to_datetime()
    
         # Opening balance is either initial opening balance if at the
         # initial date or else the last closing balance from prior
         # period
         sOB = inp['ob'] if (dt == obDate) else sCB
    
         # Calculate some additions, write-off, amortisation, depereciation, whatever!
         sSomeOp1 = 10
         sSomeOp2 = -sOB / 2
    
         # Calculate the closing balance
         sCB = sOB + sSomeOp1 + sSomeOp2
    
         # Build up list of outputs
         lOB.append(sOB)
         lSomeOp1.append(sSomeOp1)
         lSomeOp2.append(sSomeOp2)
         lCB.append(sCB)
    
     # Convert lists to timeseries objects
     ob = pd.Series(lOB, index=dates)
     someOp1 = pd.Series(lSomeOp1, index=dates)
     someOp2 = pd.Series(lSomeOp2, index=dates)
     cb = pd.Series(lCB, index=dates)
    

I can see that where you only have one or two lines of operations there might
be some clever hacks to vectorise the computation, I'd be grateful to hear any
tips people have on doing these sorts of tricks.

Some of the corkscrews I have to build, however, have 100's of intermediate
operations. In these cases what's my best way forward? Is it to accept the
slow performance of Python? Should I migrate to Cython? I've not really looked
into it (so could be way off base) but the issue with the latter approach is
that if I'm moving 100's of lines into C why am I bothering with Python in the
first place, it doesn't feel like a simple lift and shift?


Answer: This following makes in-place updates, which should improve performance

    
    
    import pandas as pd
    import numpy as np
    book=pd.DataFrame([[0, 3, np.NaN],[np.NaN,-5,np.NaN],[np.NaN,-8,np.NaN],[np.NaN,+20,np.NaN]], columns=['ob','so','cb'], index=['2013-04-01', '2013-05-01', '2013-06-01', '2013-07-01'])
    for row in book.index[:-1]:
        book['cb'][row]=book.ix[row, ['ob', 'so']].sum()
        book['ob'][book.index.get_loc(row)+1]=book['cb'][row]
    book['cb'][book.index[-1]]=book.ix[book.index[-1], ['ob', 'so']].sum()
    book
    

