Migrating Excel financial model to and Corkscrew calculation in Python Pandas -

i'm working on replacing excel financial model python pandas. financial model mean forecasting cash flow, profit & loss statement , balance sheet on time business venture opposed pricing swaps / options or working stock price data referred financial models. it's quite possible same concepts , issues apply latter types don't know them can't comment.

so far lot of see. models work in excel have common time series across top of page, defining time period we're interested in forecasting. calculations run down page series of rows. each row therefore timeseries object, or collection of rows becomes dataframe. need transpose read between these 2 constructs trivial transformation.

better yet each excel row should have common, single formula , based on rows above on page. lends vector operations computationally fast , simple write using pandas.

the issue when try model corkscrew-type calculation. these used model accounting balances, opening balance 1 period closing balance of prior period. can't use .shift() operation closing balance in given period depends, amongst other things, on opening balance in same period. best illustrated example:

time              2013-04-01   2013-05-01   2013-06-01   2013-07-01   ...  opening balance            0           +3           -2          -10      [...] operations           +3           -5           -8          +20 [...] closing balance           +3           -2          -10          +10 

in pseudo-code solution how calculate these sorts of things follows. not vectorised solution , looks pretty slow

 # set date range  dates = pd.date_range('2012-04-01',periods=500,freq='ms')   # initialise empty lists  lob = []  lsomeop1 = []  lsomeop2 = []  lcb = []   # set closing balance initial loop's ob  scb = 0   # corkscrew calculation need loop through dates  d in dates:       # create datetime object reference several times below      dt = d.to_datetime()       # opening balance either initial opening balance if @      # initial date or else last closing balance prior      # period      sob = inp['ob'] if (dt == obdate) else scb       # calculate additions, write-off, amortisation, depereciation, whatever!      ssomeop1 = 10      ssomeop2 = -sob / 2       # calculate closing balance      scb = sob + ssomeop1 + ssomeop2       # build list of outputs      lob.append(sob)      lsomeop1.append(ssomeop1)      lsomeop2.append(ssomeop2)      lcb.append(scb)   # convert lists 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 have 1 or 2 lines of operations there might clever hacks vectorise computation, i'd grateful hear tips people have on doing these sorts of tricks.

some of corkscrews have build, however, have 100's of intermediate operations. in these cases what's best way forward? accept slow performance of python? should migrate cython? i've not looked (so way off base) issue latter approach if i'm moving 100's of lines c why bothering python in first place, doesn't feel simple lift , shift?

this following makes in-place updates, should improve performance

import pandas pd import numpy 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']) 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