PuLP minimize weighted average of a selection

Question

I am stuck setting up a proper objective function for the minimization of a weighted average.

Exercise: For a portfolio of fixed income securities (ie bonds) find the selection of bonds to sell that generates a minimum gain. Within that constraint, minimize the weighted average yield of the selection of bonds.

The relevant data fields are Yield, Current Face Value and Current Profit or Loss. The weighted avg yield of the selection is the sumproduct of Yield and Current Face Value, divided by sum of Current Face Value. Continuous or Binary decision variables are acceptable.

This post is similar to my issue... Optimizing A Loan Portfolio On Weighted Average Criteria

I picked up PulP quicker than Scipy but I can use whatever package is best suited for this type of problem.

import pandas as pd
from pulp import *

d = {
      'Current Face Value':[173669.3, 219544.4, 253647.88, 256776.38, 264824.02, 348820.44, 415053.58, 475354.13, 643773.24, 781129.21, 866839.35, 866013.93, 974840.18, 1053465.32, 1074261.8, 1097979.35, 1112974.78, 1125646.64, 1216768.1, 1231914.2, 1423300.7, 1462152.67, 1642708.16, 1679005.31, 1625365.08, 1708252.62, 1765860.7, 1763888.76, 1828103.0, 1833238.25, 1796976.58, 1973474.26, 2027289.74, 2058402.54, 2186044.1, 2185605.23, 2222353.6, 2260944.05],
      'Current Profit or Loss':[-1613.81, 6546.25, 1965.83, 431.56, 1653.4, -556.95, 3192.99, 1732.18, -870.51, 16216.16, 140.5, -11624.28, 13347.67, 37106.55, 1638.02, 11903.53, 22179.76, 16074.41, 34904.67, 12146.89, 3976.73, -4810.06, -11510.87, 44416.35, 11475.39, 24260.51, 11766.51, 14648.76, 12272.55, 12255.15, -33461.55, -18799.52, 35814.91, 41468.95, 8442.11, 41645.9, 51555.44, -3333.12],
      'Yield':[2.118191, 3.381024, 3.723284, 3.567963, 3.381002, 3.554571, 1.786467, 3.585767, 2.061981, 3.395873, 1.895965, 1.41617, 2.935917, 3.140995, 3.103661, 2.157629, 2.400065, 2.231383, 2.941482, 2.172782, 2.086372, 2.128788, 2.400682, 3.497868, 2.094667, 2.667469, 2.308526, 2.513017, 2.326085, 2.306328, 2.685972, 2.001348, 2.806967, 3.659145, 2.203806, 3.201562, 2.839683, 1.462699],
    }
data = pd.DataFrame(d)

data_gains = data[data['Current Profit or Loss'] > 0].reset_index(drop=True)    # securities with gains
data_losses = data[data['Current Profit or Loss'] <= 0].reset_index(drop=True)  # securities with losses
print('Secs w/ gains:',str(len(data_gains)),'  Secs w/ losses: '+str(len(data_losses)))

prob = LpProblem('Portfolio Selection',LpMinimize)

# decision variables
decision_variables = []
for rownum, row in data_gains.iterrows():
    variable = pulp.LpVariable(str('x' + str(rownum)), lowBound = 0, upBound = 1, cat= 'Continuous')
    decision_variables.append(variable)
print ('Decision_variables: ' + str(len(decision_variables)))

# objective - minimize weighted avg yield of selection
wa_yield = []
for rownum, row in data_gains.iterrows():
    for i, flag in enumerate(decision_variables):
        if rownum == i:
            wa_yield.append(row['Current Face Value']*row['Yield']*flag)    # numerator of the proper objective
wa_yield = sum(wa_yield)
prob += wa_yield

# create constrains - total gain to be in this range
min_gain, max_gain = 100000, 150000

total_gain = []
for rownum, row in data_gains.iterrows():
    for i, flag in enumerate(decision_variables):
        if rownum == i:
            total_gain.append(row['Current Profit or Loss']*flag)
total_gain = sum(total_gain)

prob += (total_gain >= min_gain)
prob += (total_gain <= max_gain)

prob.solve()
print("Status:", LpStatus[prob.status])

result = []
for v in prob.variables():
    print(v.name, "=", v.varValue)
    result.append(v.varValue)

what problems did you run into? also your `wa_field` and `total_gain` are strings. Can you share what they look like? — Buckeye14Guy, Jan 05 '20 at 05:07

score 0 · Answer 1 · answered Jan 05 '20 at 05:13

0

Try changing the two blocks with the string objects like so. I think you should be using optimizable functions since you already have your variables. You could also use numpy.sum

wa_yield = []
for rownum, row in data_gains.iterrows():
    for i, flag in enumerate(decision_variables):
        if rownum == i:
            wa_yield.append(row['Current Face Value']*row['Yield']*flag)    # numerator of the proper objective
wa_yield = sum(wa_field)
prob += wa_yield

...

total_gain = []
for rownum, row in data_gains.iterrows():
    for i, flag in enumerate(decision_variables):
        if rownum == i:
            total_gain.append(row['Current Profit or Loss']*flag)

total_gain = sum(total_gain)

answered Jan 05 '20 at 05:13

Buckeye14Guy

831
6
12

Thanks @Buckeye14Guy I updated the code and the result it yields isn't optimal. The result is x0 = 1, x9 = 1, x12 = 0.967, x14 = 1. The gain of this selection is exactly 100,000 and meets the constraint, however the weighted avg yield is 2.86%. I can find other solutions that give a gain in the acceptable range with a lower weighted avg yield. e.g. x4 = 1, x8 = 1, x12 = 1, x13 = 1, x16 = 1, x18 = 1, x20 = 1, x22 = 1, x26 =1. This produces a 102,728 of gain and 2.27% yield. – Mark F Jan 06 '20 at 23:58
The objective isn't calculating the denominator for the weighted avg yield, which in the case of the solution the script found would be x0*Cur Face0 + x9*Cur Face9 + x12*Cur Face12 + x14*Cur Face14. How can this be incorporated into the objective function? I think this post will help...https://stackoverflow.com/questions/33929353/how-to-use-a-variable-as-a-divisor-in-pulp – Mark F Jan 06 '20 at 23:59

PuLP minimize weighted average of a selection

1 Answers1