# Index Funds with Mixed-Integer-Programming

## Index Funds with Mixed-Integer-Programming¶

We will analyze daily price data for stocks in the Dow Jones index and then try to build an accurate index fund using a small numbers of stocks therein.

Similar material was already used in a presentation at PyData Berlin 2017. See the "Tour of popular packages" notebook. Back then, we worked with Julia 0.6 and used the packages DataFrames, Plots and JuMP. Now, we work with Julia 1.0 and use packages from the Queryverse, VegaLite and IndexedTables for data prep and visualization. Also, I added an alternative model.

In [1]:
using Queryverse
using IndexedTables: ndsparse


The data consists of daily closing prices from 2016 of the 30 stocks that participate in the Dow Jones and are given in a CSV file:

In [2]:
;head -n4 ../files/dowjones2016.csv

Date,Symbol,Price
2016-01-04,AAPL,105.349997999999999
2016-01-04,AXP,67.589995999999999
2016-01-04,BA,140.500000000000000

In [3]:
price = load("../files/dowjones2016.csv") |> ndsparse
price |> @take(3)

Out[3]:
Date Symbol Price
2016-01-04 "AAPL" 105.35
2016-01-04 "AXP" 67.59
2016-01-04 "BA" 140.5
In [4]:
price |> @vlplot(:line, x=:Date, y=:Price, color={"Symbol:o", scale={scheme="viridis"}},
width=600, height=450)

Out[4]:

### Computing the Dow Jones Index¶

The Dow Jones index is computed from the prices of its stocks, as a weighted average, where the weight is itself defined through the price of the stock.

We will compute the average price of each stock over the days. Then we will normalize these values by dividing through the total of the average prices. The normalized weights are then multiplied to the daily prices to get the daily value of the index.

In [5]:
using Statistics: mean

In [6]:
avgprice = price |>
@groupby(_.Symbol) |>
@map({Symbol=key(_), AvgPrice=mean(_.Price)}) |> ndsparse
avgprice |> @take(4)

Out[6]:
Symbol AvgPrice
"AAPL" 104.604
"AXP" 63.7933
"BA" 133.112
"CAT" 78.698
In [7]:
avgprice |> @vlplot(:bar, x=:Symbol, y=:AvgPrice)

Out[7]:
In [8]:
totalavg = avgprice |> @map(_.AvgPrice) |> sum

Out[8]:
2617.739246769842
In [9]:
weight = avgprice |> @map({Symbol=_.Symbol, Weight=_.AvgPrice / totalavg}) |> ndsparse
weight |> @take(4)

Out[9]:
Symbol Weight
"AAPL" 0.0399597
"AXP" 0.0243696
"BA" 0.0508498
"CAT" 0.0300634
In [10]:
dowjones = price |>
@join(weight, _.Symbol, _.Symbol, {_.Date, _.Symbol, Contrib=_.Price * __.Weight}) |>
@groupby(_.Date) |>
@map({Date=key(_), Value=sum(_.Contrib)}) |> ndsparse
dowjones |> @take(4)

Out[10]:
Date Value
2016-01-04 100.573
2016-01-05 100.511
2016-01-06 99.0142
2016-01-07 96.606
In [11]:
@vlplot(width=600, height=450) +
@vlplot(mark={:line, strokeWidth=1, opacity=0.8}, data=price, x=:Date, y=:Price,
color={"Symbol:n", scale={scheme="blues"}}) +
@vlplot(mark={:line, strokeWidth=3, color=:orange}, data=dowjones, x=:Date, y=:Value)

Out[11]:

In the previous plot, the Dow Jones index is marked in orange. In the following, it will be our target, which we want to approximate by using a small number of the stocks.

## Model for Linear Fit¶

Let us start with an optimization model similar to an ordinary linear regression, but using the l1-norm, which is readily formulated as a linear program. In compact vector form, this reads

\begin{align*} \text{minimize} \quad & \lVert w^T P - I \rVert_1 \\ \text{subject to} \quad & w \ge 0 \end{align*}

where $P$ stands for the prices of the individual st, $I$ for our index (the target) and $w$ for the weights we use in our fund. We only allow non-negative weights for use in the portfolio.

We can use a standard linear programming trick and introduce auxiliary variables for the positive and negative parts inside the absolute values and minimize their sum. This is formulated using JuMP:

In [12]:
using JuMP
using SCIP

In [13]:
dates = unique(price.index.columns.Date)
symbols = unique(price.index.columns.Symbol)

length(dates), length(symbols)

Out[13]:
(252, 30)

We could use all days of the year as input for our model, but for our evaluation we only use the days from the first three quarters. That way, we can see how our fit extrapolates through the remaining quarter.

In [14]:
training_days = 189
traindates = dates[1:training_days]
testdates = dates[training_days + 1:end]
@show traindates[end] testdates[1]

# for visualization
datebreak = [(Date=traindates[end],)] |> @take(1)

length(traindates), length(testdates)

traindates[end] = 2016-09-30
testdates[1] = 2016-10-03

Out[14]:
(189, 63)
In [15]:
function solve_index_linear()
m = Model(solver=SCIPSolver("display/verblevel", 2))

@variable(m, weight[symbols] >= 0)
@variable(m, posdev[traindates] >= 0)
@variable(m, negdev[traindates] >= 0)

for d in traindates
@constraint(m, sum(weight[s]*price[d,s][1] for s in symbols) - dowjones[d][1] == posdev[d] - negdev[d])
end

@objective(m, :Min, sum(posdev[d] + negdev[d] for d in traindates))

status = solve(m)
return (
status = status,
weight = getvalue(weight),
)
end

Out[15]:
solve_index_linear (generic function with 1 method)
In [16]:
sol = solve_index_linear()
sol.status

Out[16]:
:Optimal
In [17]:
solweight = [(Symbol=s, SolWeight=sol.weight[s]) for s in symbols]
weight |>
@join(solweight, _.Symbol, _.Symbol, {_.Symbol, _.Weight, __.SolWeight}) |>
@vlplot(:point, x=:Weight, y=:SolWeight)

Out[17]:

In the scatter chart above, we compare the weights from the Dow Jones index, with the weights found by the LP model. As we can see (the points lie on the diagonal), we recover the actual weights perfectly, even though we are only using a subset of the data.

## Model with Sparsity Constraint¶

The last model gave us an exact fit, but used all available stocks.

We are changing it now, and allow it to use only a given number of stocks. For this end, we will introduce additional binary variables to select stocks to become active in our index fund.

The weight of inactive variables must be forced to 0, which we do with a so-called big-M constraint. The constant is chosen in such a way, that any active stock could single-handedly approximate the target index.

In [18]:
bigM = 1 / minimum(weight.data.columns[1])

Out[18]:
90.926297738811

We start by using only a single stock and then go up to subsets of 6 stocks out of the 30.

In [19]:
nstocks_range = 1:6

Out[19]:
1:6

The model seems to be relatively difficult to solve for SCIP, probably because of the big-M constraints, so we set a gap limit of 5%, rather than solving to proven optimality.

In [20]:
function solve_index_sparse(nstocks)
m = Model(solver=SCIPSolver("display/verblevel", 2, "limits/gap", 0.05))

@variable(m, active[symbols], Bin)
@variable(m, weight[symbols] >= 0)
@variable(m, posdev[traindates] >= 0)
@variable(m, negdev[traindates] >= 0)

for d in traindates
@constraint(m, sum(weight[s]*price[d,s][1] for s in symbols) - dowjones[d][1] == posdev[d] - negdev[d])
end

for s in symbols
@constraint(m, weight[s] <= bigM*active[s])
end

@constraint(m, sum(active[s] for s in symbols) <= nstocks)

@objective(m, :Min, sum(posdev[d] + negdev[d] for d in traindates))

status = solve(m, suppress_warnings=true)
return (
status = status,
weight = getvalue(weight)
)
end

Out[20]:
solve_index_sparse (generic function with 1 method)
In [21]:
sols = [solve_index_sparse(n) for n in nstocks_range];


We extract the model solutions and concatenate them into a table for visualization.

In [22]:
solweight = [(NStocks=n, Symbol=s, SolWeight=sols[n].weight[s]) for n in nstocks_range for s in symbols] |>
@filter(_.SolWeight > 0.0)
solweight |> @take(3)

Out[22]:
NStocks Symbol SolWeight
1 "TRV" 0.91443
2 "BA" 0.394428
2 "MMM" 0.313139
In [23]:
indices = price |>
@join(solweight, _.Symbol, _.Symbol, {__.NStocks, _.Date, _.Symbol, Contrib=_.Price * __.SolWeight}) |>
@groupby({_.NStocks, _.Date}) |>
@map({NStocks=_.NStocks[1], Date=_.Date[1], Value=sum(_.Contrib)}) |> ndsparse
indices |> @take(3)

Out[23]:
NStocks Date Value
1 2016-01-04 100.56
1 2016-01-05 101.017
1 2016-01-06 99.7094
In [24]:
@vlplot(width=600, height=300) +
@vlplot(mark={:line, color=:orange, strokeWidth=3},
data=dowjones, x=:Date, y={:Value, scale={domain=[80, 125]}}) +
@vlplot(mark={:line, strokeWidth=1, opacity=0.8},
data=indices, x=:Date, y=:Value, color="NStocks:o") +
@vlplot(:rule, data=datebreak, x=:Date)

Out[24]:

As we can see, our solutions stick to the target fund quite closely during the training period, but then diverge from it quickly. Allowing more stocks gives us a better fit.

## Computing Returns¶

In the previous model, we tried to fit our index fund to the absolute day-to-day closing prices. But the next model will be based on picking representative that are similar, which is defined through correlation of returns.

So let's start by computing daily returns from the closing prices (for the training period).

In [25]:
prevdates = (Date=dates[2:end], PrevDate=dates[1:end-1])
prevdates |> @take(3)

Out[25]:
Date PrevDate
2016-01-05 2016-01-04
2016-01-06 2016-01-05
2016-01-07 2016-01-06
In [26]:
price[:, "AAPL"] |> @take(3)

returns = prevdates |>
@filter(_.Date <= traindates[end]) |>
@join(price, _.Date, _.Date, {_.Date, _.PrevDate, __.Symbol, __.Price}) |>
@join(price, {Date=_.PrevDate, _.Symbol}, {_.Date, _.Symbol},
{__.Symbol, _.Date, Return=_.Price / __.Price - 1.0}) |>
ndsparse
returns |> @take(3)

Out[26]:
Symbol Date Return
"AAPL" 2016-01-05 -0.0250593
"AAPL" 2016-01-06 -0.0195697
"AAPL" 2016-01-07 -0.0422046
In [27]:
returns |>
@vlplot(mark={:line, opacity=0.8, strokeWidth=1}, width=600, height=450,
x=:Date, y=:Return, color={"Symbol:o", scale={scheme="viridis"}})

Out[27]: