title: Migrating Excel financial model to and Corkscrew calculation in Python Pandas
body: <p>I'm working on replacing an Excel financial model into Python Pandas. By financial model I mean forecasting a cash flow, profit &amp; 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.</p>
  
  <p>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 <code>TimeSeries</code> object, or a collection of rows becomes a <code>DataFrame</code>. Obviously you need to transpose to read between these two constructs but this is a trivial transformation.</p>
  
  <p>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.</p>
  
  <p>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 <code>.shift()</code> 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:</p>
  
  <pre class="lang-python prettyprint-override"><code>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
  </code></pre>
  
  <p>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</p>
  
  <pre class="lang-python prettyprint-override"><code> # 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)
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>