<jupyter_start><jupyter_text>Equal-Weight S&P 500 Index FundThe S&P 500 is the world's most popular stock market index. The largest fund that is benchmarked to this index is the SPDR® S&P 500® ETF Trust. It has more than US$250 billion of assets under management.In this section I will create a Python script that will accept the value of a portfolio and tell how many shares of each S&P 500 constituent you should purchase to get an equal-weight version of the index fund. Library ImportsThe first thing we need to do is import the open-source software libraries that we'll be using (all installed through the requirements.txt file)<jupyter_code>import numpy as np
import pandas as pd
import requests
import xlsxwriter
import math<jupyter_output><empty_output><jupyter_text>Importing Our List of StocksThe next thing we need to do is import the constituents of the S&P 500.These constituents change over time, so in an ideal world you would connect directly to the index provider (Standard & Poor's) and pull their real-time constituents on a regular basis.There's a static version of the S&P 500 constituents available which we will use and need to import.<jupyter_code>stocks = pd.read_csv('sp_500_stocks.csv')
stocks.head()<jupyter_output><empty_output><jupyter_text>Acquiring an API TokenNow it's time to import our IEX Cloud API token. This is the data provider that I will be using.API tokens (and other sensitive information) should be stored in a `secrets.py` file that doesn't get pushed to your local Git repository. We'll be using a sandbox API token, which means that the data we'll use is randomly-generated and (more importantly) has no cost associated with it. In this case, I will leave the `secrets.py` file available but if you want to make real purchases and you upload your project into github, make sure `secrets.py` is added to the `.gitignore` file.<jupyter_code>from secrets import IEX_CLOUD_API_TOKEN<jupyter_output><empty_output><jupyter_text>Making Our First API CallNow it's time to structure our API calls to IEX cloud. We need the following information from the API:* Market capitalization for each stock* Price of each stock<jupyter_code>symbol = 'AAPL'
# api_url = "https://cloud.iexapis.com/" This would be the noprmal api to use but we are using
# the sandbox version


print(data)api_url = f"https://sandbox.iexapis.com/stable/stock/{symbol}/quote?token={IEX_CLOUD_API_TOKEN}"
data = requests.get(api_url).json()<jupyter_output><empty_output><jupyter_text>Parsing Our API CallThe API call that we executed in the last code block contains all of the information required to build our equal-weight S&P 500 strategy. With that said, the data isn't in a proper format yet. We need to parse it first.<jupyter_code>price = data['latestPrice']
market_cap = data['marketCap']<jupyter_output><empty_output><jupyter_text>Adding Our Stocks Data to a Pandas DataFrameThe next thing we need to do is add our stock's price and market capitalization to a pandas DataFrame.<jupyter_code>my_columns = ['Ticker', 'Stock Price', 'Market Capitalization', 'Number of Shares to Buy']
final_dataframe = pd.DataFrame(columns = my_columns)
final_dataframe.append(
    pd.Series(
        [
            symbol,
            price,
            market_cap,
            "N/A"
        ],
        index = my_columns
    ),
    ignore_index=True
)<jupyter_output><empty_output><jupyter_text>Looping Through The Tickers in Our List of StocksUsing the same logic that we outlined above, we can pull data for all S&P 500 stocks and store their data in the DataFrame using a `for` loop.<jupyter_code>final_dataframe = pd.DataFrame(columns=my_columns)
for stock in stocks['Ticker']:
    api_url = f"https://sandbox.iexapis.com/stable/stock/{stock}/quote?token={IEX_CLOUD_API_TOKEN}"
    data = requests.get(api_url).json()
    final_dataframe = final_dataframe.append(
    pd.Series(
        [
            stock,
            data['latestPrice'],
            data['marketCap'],
            "N/A"
        ],
        index = my_columns
    ),
    ignore_index=True
)

final_dataframe.head()<jupyter_output><empty_output><jupyter_text>Using Batch API Calls to Improve PerformanceBatch API calls are one of the easiest ways to improve the performance of our code.This is because HTTP requests are typically one of the slowest components of a script.Also, API providers will often give you discounted rates for using batch API calls since they are easier for the API provider to respond to.IEX Cloud limits their batch API calls to 100 tickers per request. Still, this reduces the number of API calls we'll make in this section from 500 to 5 - huge improvement! In this section, we'll split our list of stocks into groups of 100 and then make a batch API call for each group.<jupyter_code>def chunks(lst,n):
    """Yield successive n-sized chunks from lst"""
    for i in range(0,len(lst),n):
        yield lst[i:i + n]
symbol_groups = list(chunks(stocks['Ticker'],100))
symbol_strings = []
for idx, batch in enumerate(symbol_groups):
    symbol_strings.append(','.join(batch))
final_dataframe = pd.DataFrame(columns=my_columns)
for batch in symbol_strings:
    batch_api_call_url = f"https://sandbox.iexapis.com/stable/stock/market/batch?symbols={batch}&types=quote&token={IEX_CLOUD_API_TOKEN}"
    data = requests.get(batch_api_call_url).json()
    for symbol in batch.split(','):
        final_dataframe = final_dataframe.append(
            pd.Series([
                symbol,
                data[symbol]['quote']['latestPrice'],
                data[symbol]['quote']['marketCap'],
                "N/A"
            ],
            index = my_columns),
            ignore_index=True
        )<jupyter_output><empty_output><jupyter_text>Calculating the Number of Shares to BuyAs you can see in the DataFrame above, we stil haven't calculated the number of shares of each stock to buy.We'll do that next.<jupyter_code>portfolio_size = input('Enter the value of your portfolio: ')

try:
    val = float(portfolio_size)
except ValueError:
    print("Please introduce a number for your portfolio")
    portfolio_size = input('Enter the value of your portfolio: ')
    val = float(portfolio_size)

position_size = val/len(final_dataframe.index)
for i in range(0,len(final_dataframe.index)):
    final_dataframe.loc[i, 'Number of Shares to Buy'] = math.floor(position_size/final_dataframe.loc[i, 'Stock Price'])

final_dataframe<jupyter_output><empty_output><jupyter_text>Formatting Our Excel OutputI will be using the XlsxWriter library for Python to create nicely-formatted Excel files.XlsxWriter is an excellent package and offers tons of customization. Initializing our XlsxWriter Object<jupyter_code>writer = pd.ExcelWriter('recommended_trades.xlsx',engine='xlsxwriter')
final_dataframe.to_excel(writer, 'Recommended Trades', index = False)<jupyter_output><empty_output><jupyter_text>Creating the Formats We'll Need For Our `.xlsx` FileFormats include colors, fonts, and also symbols like `%` and `$`. We'll need four main formats for our Excel document:* String format for tickers* \\$XX.XX format for stock prices* \\$XX,XXX format for market capitalization* Integer format for the number of shares to purchase<jupyter_code>background_colour = '#0a0a23'
font_color = "#ffffff"

string_format = writer.book.add_format(
    {
        'font_color': font_color,
        'bg_color': background_colour,
        'border': 1
    }
)

dollar_format = writer.book.add_format(
    {
        'num_format': '$0.00',
        'font_color': font_color,
        'bg_color': background_colour,
        'border': 1
    }
)

integer_format = writer.book.add_format(
    {
        'num_format': '0',
        'font_color': font_color,
        'bg_color': background_colour,
        'border': 1
    }
)<jupyter_output><empty_output><jupyter_text>Applying the Formats to the Columns of Our `.xlsx` FileWe can use the `set_column` method applied to the `writer.sheets['Recommended Trades']` object to apply formats to specific columns of our spreadsheets.Here's an example:```pythonwriter.sheets['Recommended Trades'].set_column('B:B', This tells the method to apply the format to column B                     18, This tells the method to apply a column width of 18 pixels                     string_template This applies the format 'string_template' to the column                    )``` This code works, but it violates the software principle of "Don't Repeat Yourself". Let's simplify this by putting it in 2 loops:<jupyter_code>column_formats = {
    'A': ['Ticker', string_format],
    'B': ['Stock Price', dollar_format],
    'C': ['Market Capitalizations', dollar_format],
    'D': ['Numbe of Shares to Buy', integer_format]
}

for key,value in column_formats.items():
    writer.sheets['Recommended Trades'].set_column(f"{key}:{key}", 18, value[1])
    writer.sheets['Recommended Trades'].write(f"{key}1", value[0], value[1])<jupyter_output><empty_output><jupyter_text>Saving Our Excel OutputSaving our Excel file is very easy:<jupyter_code>writer.save()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup