Load the list of S&P 600 companies from Wikepedia
The S&P 600 is an index of small-cap company stocks created by Standard & Poor’s, selected by a committee based on recent profitability and other factors.
Currently there are 3 S&P Small Cap 600 ETFs traded on the U.S. markets, the largest one is iShares S&P Small-Cap 600 Value ETF IJS with around 7.09 Billon USD in assets.
The goal of this section is trying to create an index fund following the instruction on https://github.com/nickmccullum/algorithmic-trading-python/tree/master, with this fund it tells how many shares of each S&P 600 constituent you should purchase to get an equal-weight version of the index fund.
# Get the list of S&P 600 stocks from Wikepedia
import pandas as pd
def load_data(url):
html = pd.read_html(url, header=0)
return html
# Load the list of S&P 600 companies
url = 'https://en.wikipedia.org/wiki/List_of_S%26P_600_companies'
df = load_data(url)[0]
df.head()| Symbol | Company | GICS Sector | GICS Sub-Industry | Headquarters Location | SEC filings | CIK | |
|---|---|---|---|---|---|---|---|
| 0 | AAON | AAON, Inc. | Industrials | Building Products | Tulsa, Oklahoma | view | 824142 |
| 1 | AAP | Advance Auto Parts, Inc. | Consumer Discretionary | Automotive Retail | Raleigh, North Carolina | view | 1158449 |
| 2 | AAT | American Assets Trust | Real Estate | Diversified REITs | San Diego, California | view | 1500217 |
| 3 | ABCB | Ameris Bancorp | Financials | Regional Banks | Atlanta, Georgia | view | 351569 |
| 4 | ABG | Asbury Automotive Group | Consumer Discretionary | Automotive Retail | Duluth, Georgia | view | 1144980 |
Retrieve the latest stock price using yfinance
We retrieve the latest day’s stock price using yfinance.
import yfinance as yf
import warnings
warnings.filterwarnings('ignore')
data = yf.download(
# tickers list
tickers = list(df['Symbol']),
# valid periods: 1d, 5d, 1mo, 3mo, 6mo, 1y, 5y, 10y, ytd, max
period = '1d',
# valid intervals: 1m, 2m, 5m, 15m, 15m, 30m, 60m, 90m, 1h, 1d, 5d, 1wk, 1mo, 3mo
interval = '1d',
# group by ticker
group_by = 'ticker',
# adjust all OHLC automatically
auto_adjust = True,
# download pre/post regular market hours data
prepost = True,
# use threads for mass downloading
threads = True,
# proxy URL scheme when downloading
proxy = None
)[*********************100%%**********************] 602 of 602 completed
2 Failed downloads:
['MOG.A', 'CWEN.A']: Exception('%ticker%: No data found, symbol may be delisted')
data['AAON']| Price | Open | High | Low | Close | Volume |
|---|---|---|---|---|---|
| Date | |||||
| 2024-02-23 | 82.639999 | 84.379997 | 82.230003 | 84.050003 | 226500 |
Buid the index fund
In this section, I will use the closing price of the latest day as the price of each stock, and based on this price to build an equal-weight index fund by calculate how much shares to buy.
# Get the available tickers
tickers_unavailable = ['MOG.A', 'CWEN.A']
tickers = [ticker for ticker in list(df['Symbol']) if ticker not in tickers_unavailable]
print('Number of available tickers: ', len(tickers))Number of available tickers: 600
# Create the format of the dataframe that stores the stock price, market cap and number of shares to buy
my_columns = ['Ticker', 'Price','Market Capitalization', 'Number Of Shares to Buy']
final_dataframe = pd.DataFrame(columns = my_columns)
final_dataframe| Ticker | Price | Market Capitalization | Number Of Shares to Buy |
|---|
# Input the value of the portforlio, i.e. example here is 10M USD
portfolio_size = input("Enter the value of your portfolio:")
try:
val = float(portfolio_size)
except ValueError:
print("That's not a number! \n Try again:")
portfolio_size = input("Enter the value of your portfolio:")Enter the value of your portfolio:10000000
# Calculate the number of shares to buy
import math
position_size = float(portfolio_size) / len(tickers)
for i in range(0,len(tickers)):
ticker = tickers[i]
final_dataframe = final_dataframe.append(
pd.Series(
[
ticker,
data[ticker]['Close'].item(),
data[ticker]['Close'].item() * data[ticker]['Volume'].item(),
math.floor(position_size / data[ticker]['Close'].item())
],
index = my_columns),
ignore_index = True)final_dataframe.head()| Ticker | Price | Market Capitalization | Number Of Shares to Buy | |
|---|---|---|---|---|
| 0 | AAON | 84.050003 | 1.903733e+07 | 198 |
| 1 | AAP | 61.099998 | 1.026358e+08 | 272 |
| 2 | AAT | 21.530001 | 5.802335e+06 | 774 |
| 3 | ABCB | 45.880001 | 1.286475e+07 | 363 |
| 4 | ABG | 214.619995 | 1.983089e+07 | 77 |
Save the output in Excel using XlsxWriter
# Initiate the XlsxWriter object, save the output in xlsx format.
import xlsxwriter
writer = pd.ExcelWriter('recommended trades.xlsx', engine = 'xlsxwriter')
final_dataframe.to_excel(writer, 'Recommended Trades', index = False)Create the formats:
- String format for tickers
- $xx.xx format for stock prices
- $xx,xxx for market capitalization
- Integer format for the number of shares to buy
background_color = '#0a0a23'
font_color = '#ffffff'
string_format = writer.book.add_format(
{
'font_color': font_color,
'bg_color': background_color,
'border': 1
}
)
dollar_format = writer.book.add_format(
{
'num_format':'$0.00',
'font_color': font_color,
'bg_color': background_color,
'border': 1
}
)
capital_format = writer.book.add_format(
{
'num_format':'$#,##0',
'font_color': font_color,
'bg_color': background_color,
'border': 1
}
)
integer_format = writer.book.add_format(
{
'num_format':'0',
'font_color': font_color,
'bg_color': background_color,
'border': 1
}
)
percent_format = writer.book.add_format(
{
'num_format':'0.0%',
'font_color': font_color,
'bg_color': background_color,
'border': 1
}
)# Apply the formats to the columns of the output file
column_formats = {
'A': ['Ticker', string_format],
'B': ['Stock Price', dollar_format],
'C': ['Market Capitalization', capital_format],
'D': ['Number of Shares to Buy', integer_format],
}
for column in column_formats.keys():
writer.sheets['Recommended Trades'].set_column(f'{column}:{column}', 18, column_formats[column][1])
writer.sheets['Recommended Trades'].write(f'{column}1', column_formats[column][0], string_format)# Save the output
writer.save()
