Value-at-Risk for a portfolio

I have yet to see a comprehensive variance/co-variance method for getting VaR values for individual securities in a portfolio. So lets begin.

First, we need pandas, numpy, scipy and a datasource for our portfolio. I prefer 'bt' for the later.

import pandas as pd
import numpy as np
import pandas.io.data as web
import datetime
import matplotlib.pyplot as plt
import bt

from scipy.stats import norm

So lets pull the data

start = datetime.datetime(2013, 5, 1)


portfolio = bt.get('aapl, tlt, fxe, gs, c, fb, ms, ko, bac', start=start)
port = pd.DataFrame(index=portfolio.columns)

Now we have two dataframes. One with our historical values as portfolio, the other as port which holds the security names which we will later store our VaR values in. Next we create the actual VaRCoV function:

def var_cov_var(P, c, mu, sigma):
    alpha = norm.ppf(1-c, mu, sigma)
    return P - P*(alpha + 1)

def getVar(portfolio, c): #portfolio dataframe, confidence interval    P = 15000   #each position % of portfolio, should create a new one to store marketvalue of each position to be more precise    #c = 0.99    #confidence interval, moved to function    totalrisk = 0    for i in portfolio:
        rets = portfolio[i].pct_change()
        mu = np.mean(rets)
        sigma = np.std(rets)
        var = var_cov_var(P, c, mu, sigma)
        port["VaR"][i] = var
        totalrisk += var
    return port, totalrisk

getVar(portfolio, .95)

Out[471]: 
(             VaR
 aapl  328.585833
 tlt   205.453309
 fxe   136.978460
 gs    274.577792
 c     315.838626
 fb    566.777609
 ms    332.530446
 ko    220.500392
 bac   319.658819, 2700.9012842524335)

Chees.

thinkorswim RTD/DDE data into Python

Many may not know it, but thinkorswim provides users the ability to access real time data (RTD) in excel. This is a great feature that a lot of data-streams ask their customers to pay a pretty penny for each month. Unfortunately, not many people know how to leverage the the functionality as it is pretty limiting out of the box.

So you are a thinkorswim customer and you want real time data in a python environment? No problem.

First, install the xlwings module. Load up your python IDE of choice.

from xlwings import Workbook, Sheet, Range, Chart
import time
wb = Workbook()

You need to have thinkorswim open and running. A blank excel spreadsheet should open up after you execute the last command above.

def getQuote(ticker, type):
    Range('B1').value = '=RTD("tos.rtd", ,"%s", "%s")' % (type, ticker)
    time.sleep(2.5)
    return Range('B1').value

So we create a getQuote function above that takes in two variables, both strings. We utilize the Write value feature in python to input the DDE/RTD function into the Excel cell. After about a second, this will return a value where we can use the return feature to read it back. 

Really, this is just a work-around. Unfortunately, I could not find a way to get RTD data working into the python environment directly. If anyone does, please feel free to drop a note in the comments.

So now we can call the getQuote function a ticker and input type of our choice:

getQuote("/ESU5", "LAST")

Out[447]: 2101.0

Pretty awesome! You can experiment with options as well:

def quoteOption(type, ticker, cp, exp, strike):
    Range('B1').value = '=RTD("tos.rtd", ,"%s", ".%s%s%s%s")' % (type, ticker, exp, cp, strike)
    time.sleep(2.5) #wait for excel to update the cell, usually a couple of seconds.    return Range('B1').value

quoteOption('LAST', 'VXX', 'C', 150731, 17)
Out[446]: 1.23

Cheers.