Investing with Python: Part 2 - Stocks

import pandas as pd
import yfinance as yf
import openpyxl
from currency_converter import CurrencyConverter
import numpy as np
from pypfopt import BlackLittermanModel
from pypfopt import black_litterman, risk_models

# Declare constants

PORTFOLIO='/mnt/wd-bigfoot/share/luis/OneDrive/Documents/Portfolio/00 portfolio.xlsx'
STOCKS_SHEET='Stocks'
START_DATE='2010-01-01'
END_DATE='2021-01-08'

ALL_WORLD='IWDA.AS'

TOTAL_FUNDS=40e3 # 40.000 EUR

# Open the portfolio and get the stocks

stocks_pd = pd.read_excel(PORTFOLIO, engine='openpyxl', sheet_name=STOCKS_SHEET)
stocks_pd.index = stocks_pd['Symbol']
stocks_pd = stocks_pd[['Company Name', 'Prediction', 'Pesimistic', 'Optimistic']]

stocks_pd

# Get the historical data of the stocks and exchange to EUR

stocks = pd.DataFrame()
cc = CurrencyConverter(fallback_on_missing_rate=True, fallback_on_wrong_date='True')

for symbol in stocks_pd.index:
    history = yf.download(symbol, start=START_DATE, end=END_DATE)['Adj Close']
    dataframe = pd.DataFrame(history)
    currency = yf.Ticker(symbol).info['currency'].upper()
    dataframe.columns = [ symbol + ':' + currency ]
    
    # Add Market Cap to the stocks_pd
    stocks_pd.at[symbol, 'Market Cap'] = cc.convert(yf.Ticker(symbol).info['marketCap'],
                                                  currency, 'EUR', 
                                                  dataframe.index[-1].to_pydatetime())
    
    # Add column with security in EUR if needed
    if currency.lower() != 'eur':
        for index in dataframe.index:
            original_value = dataframe.at[index, symbol + ':' + currency]
            value = cc.convert(original_value, currency, 'EUR', date=index.to_pydatetime())
            dataframe.at[index, symbol + ':EUR'] = value
    
    # Merge everything
    stocks = pd.concat([ stocks, dataframe], axis='columns')

# Fill any NA values with the best forward or backarwd value
stocks.fillna(method='ffill', inplace=True)

del cc, symbol, history, dataframe, currency, original_value, value, index

stocks_pd

# Get market reference (MSCI All World Accumulative ETF in USD)

market = pd.DataFrame(yf.download(ALL_WORLD, start=START_DATE, end=END_DATE)['Adj Close'])
market.columns = ['Market Ref:USD']

cc = CurrencyConverter(fallback_on_missing_rate=True, fallback_on_wrong_date='True')

for index in market.index:
    original_value = market.at[index, 'Market Ref:USD']
    value = cc.convert(original_value, 'USD', 'EUR', date=index.to_pydatetime())
    market.at[index, 'Market Ref:EUR'] = value

# Fill any NA values with the best forward or backarwd value
market.fillna(method='ffill', inplace=True)

del cc, original_value, value, index

# Clean up dataframes and keep only EUR with the symbol name
# Same for Market

for column in stocks.columns:
    if ':EUR' not in column:
            stocks.drop(column, axis='columns', inplace=True)
    symbol, currency = column.split(':')
    stocks.rename(mapper={column: symbol}, axis='columns', inplace=True)

for column in market.columns:
    if ':EUR' not in column:
            market.drop(column, axis='columns', inplace=True)
    symbol, currency = column.split(':')
    market.rename(mapper={column: symbol}, axis='columns', inplace=True)

del column, symbol, currency

# Create a checkpoint

stocks.to_csv('cache_stocks.csv')
market.to_csv('cache_market.csv')

# Restore Checkpoint

stocks = pd.read_csv('cache_stocks.csv')
stocks.set_index(pd.DatetimeIndex(stocks['Date']), inplace=True)
stocks.drop(['Date'], axis='columns', inplace=True)

market = pd.read_csv('cache_market.csv')
market.set_index(pd.DatetimeIndex(market['Date']), inplace=True)
market.drop(['Date'], axis='columns', inplace=True)

# Construct the dictionary with your views
views = stocks_pd['Prediction'].to_dict()

# Construct a uncertanties dictionary
optimistic = stocks_pd['Optimistic'].to_dict()
pesimistic = stocks_pd['Pesimistic'].to_dict()

variances = []
for symbol in views.keys():
    sigma = (optimistic[symbol] - pesimistic[symbol])/2
    variances.append(sigma ** 2)

omega = np.diag(variances)

del optimistic, pesimistic, variances, symbol, sigma

# Construct the Black Litterman 

S = risk_models.CovarianceShrinkage(stocks).ledoit_wolf()

delta = black_litterman.market_implied_risk_aversion(market['Market Ref'])

# Using a pi equal weighted as the market caps for stocks are too complex to calculate

bl = BlackLittermanModel(S, pi="market", market_caps=stocks_pd['Market Cap'], 
                         risk_aversion=delta, absolute_views=views, omega=omega)

ret_bl = bl.bl_returns()
S_bl = bl.bl_cov()

from pypfopt import EfficientFrontier, objective_functions

ef = EfficientFrontier(ret_bl, S_bl)
ef.add_objective(objective_functions.L2_reg)
ef.max_sharpe()
weights = ef.clean_weights()
pd.Series(weights).plot.pie(figsize=(10,10));

from pypfopt import DiscreteAllocation

da = DiscreteAllocation(weights, stocks.iloc[-1], total_portfolio_value=TOTAL_FUNDS)
alloc, leftover = da.greedy_portfolio()

allocation = pd.DataFrame(data=stocks_pd['Company Name'], 
                          index=stocks_pd.index, columns=['Company Name'])

for key in alloc.keys():
    allocation.at[key, 'Desired Position'] = alloc[key]
    allocation.at[key, 'Last Price EUR'] = int(stocks.iloc[-1][key] * 100)/100
    allocation.at[key, 'Total Funds EUR'] = int(stocks.iloc[-1][key] * alloc[key] * 100)/100

allocation.fillna(0, inplace=True)
allocation['Desired Position'] = allocation['Desired Position'].astype(int)
allocation

allocation.to_excel('/mnt/wd-bigfoot/share/luis/OneDrive/Documents/Portfolio/99 Last Allocation of Stocks.xlsx', 
                    index=True)