Automating NAVs

In the previous piece, I showed how to calculate your NAV for a given market. It was theoretical rather than practical: in the real world, you'll want a spreadsheet that looks like this:

If you want to download this sheet, you can get it here. But I'd advise you to build your own. (Actually, I'd advise you develop a more sophisticated one - but this one will do for now.) Now, let us take a typical Betfair market:

Now, copying us across this data to our Excel sheet gives us:

Wow: positive £80.85 - not bad :-) That said, according to the market, there is at least a 45% chance I will lose £177! (This is a classic example of an unbalanced book - and there are trading strategies that seek to, as best as possible, maintain your overall NAV, while balancing your book somewhat. To put in context, a perfectly balanced book with a NAV of £80.85 would have return £80.85 irrespective of the outcome. Sometimes you'll want balanced books, and sometimes you won't.)

But while it's good to be able to generate NAVs, it is pain creating them manually. What we want to be able to do is take what we learnt a few posts ago and automate the procedure. At this point I'm torn: do I continue to use Excel, or do I switch to Resolver? Because I'm lazy, and automating this in Resolver will be clearer (for you) and take less time (for me), I'll make switch.

First things first: I need to recreate the sheet I had in Excel. There is one additional step we should take: right click on first row selector, and choose "Set Header Row":

Now, do the same with selecting the 'A' column, and set the header column. This allows us to reference cells by meaningful names rather than by 'A3', etc. So, we can refer to ['Odds', 'Conservatives'] for example. Once you've used header rows and columns extensively, you'll never go back :-)

Let's do a couple of other things. Let's call the sheet 'NAV'. (In the same way we do in Excel, by double clicking on the Sheet1 tab at the bottom, and changing it to NAV). Then add the following:

The code is pretty self explanatory (and if you don't feel like typing it in, you can download a slightly more sophisticated version of it here, and remember you will need the Betfair libraries from here) - but I'll explain it anyway. The first section ('Pre-constants user code') basically sets up the connection to Betfair, and creates the objects we'll deal with. The grand-daddy of these is the betfairGateway which is our logged in connection. We get other objects - relating to markets or P&Ls - from this parent.

We create two more objects from this gateway object - a market object (betfairMarket) and a P&L object (betfairPL). These objects contain details of runners and prices (betfairMarket) and our positions on the market (betfairPL).

The 'Pre-formula user code' section does the formatting and display of the data in these objects.

row = 2

for selection in betfairMarket.GetRunners():

NAV['Selection', row] = str(selection)

NAV['Odds', row] = selection.Back

row += 1

Firstly, we set the first row that we will copy data into. Then we iterate over the runner objects (GetRunners() returns an array of runner objects).

For each of these selections, we put into the NAV worksheet, in the 'Selection' column, the name of the selection (str(selection)), and then the Back price (selection.Back). We then increment the row number.

for item in betfairPL:

NAV['Outcome', str(item.selectionName)] = item.ifWin

We then iterate over the objects in the betfairPL objects, putting the outcome (item.ifWin) in the appropriate cell. (One of Resolver One's real strength's is the ability to dynamically reference cells by their names.)

for row in NAV.ContentRows:

row['Raw IP'] = 1 / row['Odds']

Now we iterate over each row (ContentRows is rows excluding the Header Row we set earlier), filling in the 'Raw IP' cell with the calculated raw implied percentage.

totalIP = SUM(NAV.Cols['Raw IP'])

Now, we sum the column to see how much we need to deflate individual percentages.

for row in NAV.ContentRows:

row['IP'] = row['Raw IP'] / totalIP

row['NAV'] = row['IP'] * row['Outcome']

This should be pretty obvious now: we're iterating over the rows again, filling in the Implied Percentage (IP) and NAV columns.

rowNum = NAV.MaxRow + 2

NAV[1, rowNum] = 'Total'

NAV.Rows[rowNum].Bold = True

NAV['NAV', rowNum] = SUM(NAV.Cols['NAV'])

Finally, let's add a pretty Total row and calculation at the bottom.

Well - that's it for now. You now have an automated NAV calculator for any given market.