I have a large df consisting of hourly share prices. I was hoping to find the optimal buy price and sell price to maximize earnings (revenue - costs). I have no idea what the maximizing buy/sell prices would be, therefore my initial guess is a wild stab in the dark.
I tried to use Scipy 'minimize' and 'basin hopping'. When I run the script, I appear to be getting stuck in local wells, with the results barely moving away from my initial guess.
Any ideas on how I can resolve this? is there a better way to write the code, or a better method to use.
Sample code below
import pandas as pd
import numpy as np
import scipy.optimize as optimize
df = pd.DataFrame({
'Time': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
'Price': [44, 100, 40, 110, 77, 109, 65, 93, 89, 49]})
# Create Empty Columns
df[['Qty', 'Buy', 'Sell', 'Cost', 'Rev']] = pd.DataFrame([[0.00, 0.00, 0.00, 0.00, 0.00]], index=df.index)
# Create Predicate to add fields
class Predicate:
def __init__(self):
self.prev_time = -1
self.prev_qty = 0
self.prev_buy = 0
self.prev_sell = 0
self.Qty = 0
self.Buy = 0
self.Sell = 0
self.Cost = 0
self.Rev = 0
def __call__(self, x):
if x.Time == self.prev_time:
x.Qty = self.prev_qty
x.Buy = self.prev_buy
x.Sell = self.prev_sell
x.Cost = x.Buy * x.Price
x.Rev = x.Sell * x.Price
else:
x.Qty = self.prev_qty + self.prev_buy - self.prev_sell
x.Buy = np.where(x.Price < buy_price, min(30 - x.Qty, 10), 0)
x.Sell = np.where(x.Price > sell_price, min(x.Qty, 10), 0)
x.Cost = x.Buy * x.Price
x.Rev = x.Sell * x.Price
self.prev_buy = x.Buy
self.prev_qty = x.Qty
self.prev_sell = x.Sell
self.prev_time = x.Time
return x
# Define function to minimize
def max_rev(params):
global buy_price
global sell_price
buy_price, sell_price = params
df2 = df.apply(Predicate(), axis=1)
return -1 * (df2['Rev'].sum() - df2['Cost'].sum())
# Run optimization
initial_guess = [40, 90]
result = optimize.minimize(fun=max_rev, x0=initial_guess, method='BFGS')
# result = optimize.basinhopping(func=max_rev, x0=initial_guess, niter=1000, stepsize=10)
print(result.x)
# Run the final results
result.x = buy_price, sell_price
df = df.apply(Predicate(), axis=1)
print(df)
print(df['Rev'].sum() - df['Cost'].sum())
You've not given a lot of detail, but I'm assuming that you consider the "perfect foresight" revenue maximisation problem - i.e. you know at the start how the price will evolve over the horizon.
This problem is quite easy to solve, but as far as I can tell at the moment your problem is unconstrained - you could make an arbitrarily large revenue by buying an infinite No. of units at a low price and selling them at a high price.
You need to add a constraint that you can only start with a finite amount of cash, and that you can only sell stock that you own (this isn't strictly true, it is possible to "short-sell" where you sell stuff now on the expectation it's value will fall (when you'll have to buy it again later).
Ignoring short-selling shenanigans you can formulate an optimisation problem as a Linear Program as follows:
import pandas as pd
import numpy as np
from pulp import *
# Problem Data
df = pd.DataFrame({
'Time': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
'Price': [44, 100, 40, 110, 77, 109, 65, 93, 89, 49]})
times = list(df.Time)
times_plus_1 = times + [times[-1] + 1]
# Instantiate maximisation problem
prob = LpProblem("numpy_constraints", LpMaximize)
# Create the problem vairables
# Cash in bank and stock-level at start of each interval
Cash = pulp.LpVariable.dicts("Cash", times_plus_1, cat='Continuous', lowBound=0)
Stock = pulp.LpVariable.dicts("Stock", times_plus_1, cat='Continuous', lowBound=0)
# Amount bought during interval
Buy = pulp.LpVariable.dicts("Buy", times, cat='Continuous')
# Add Objective to problem - cash at end of period modelled
prob += Cash[times_plus_1[-1]]
# Add constraints
# Start with a single dollar in the bank & no stock
prob += Cash[times[0]] == 1.0
prob += Stock[times[0]] == 0.0
# Cash & stock update rules
for t in times:
prob += Cash[t+1] == Cash[t] - Buy[t]*df.Price[t]
prob += Stock[t+1] == Stock[t] + Buy[t]
# Solve
prob.solve()
# Check when we bought when:
Buy_soln = np.array([Buy[t].varValue for t in times])
print("Buy_soln:")
print(Buy_soln)
Stock_soln = np.array([Stock[t].varValue for t in times_plus_1])
print("Stock_soln:")
print(Stock_soln)
Cash_soln = np.array([Cash[t].varValue for t in times_plus_1])
print("Cash_soln:")
print(Cash_soln)
Which results in the following:
Buy_soln:
[ 0.02272727 -0.02272727 0.05681818 -0.05681818 0.08116883 -0.08116883
0.13611389 -0.13611389 0. 0. ]
Stock_soln:
[0. 0.02272727 0. 0.05681818 0. 0.08116883
0. 0.13611389 0. 0. 0. ]
Cash_soln:
[ 1. 0. 2.2727273 0. 6.25 0.
8.8474026 0. 12.658591 12.658591 12.658591 ]
Not particularly interesting - as expected use all of the cash available to exploit any increases in stock price (buy low sell high).