Evidence_v1, Financial Modeling and Programming

Abstract

This is the in-class evidence of the course. Follow directions bellow

1 INTRODUCTION

You have to work on Google Colab for this assessment. You can use any material you want. Also, you can use Gemini for code generation.

For each programming chunk, you MUST DOCUMENT/EXPLAIN WHAT THE CODE DOES and why you run that code. IN CASE GEMINI GIVES YOU EXPLANATIONS SUCH TASKS OR SUB-TASK, DELETE THOSE EXPLANATIONS AND LEAVE ONLY YOUR OWN EXPLANATIONS.

ONLY KEEP THE Python CODE YOU NEED FOR THIS EVIDENCE. Extra CODE CAN BE PENALIZED

You have to analyze historical financial information of US firms to select stocks and propose an optimal portfolio.

You have to submit your .ipynb file, (Jupyter Notebook file). You can generate this file from Google Colab: Go to File / Download / Download .ipynb.

Also, share your Colab link with me (cdorante@tec.mx).

Follow directions bellow.

2 DATA

Run the following code to download 2 datasets:

import requests
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
}
# Download the first file
url1 = 'https://www.apradie.com/datos/usfirms_s1.csv'
response1 = requests.get(url1, headers=headers)
with open('usfirms1.csv', 'wb') as f:
    f.write(response1.content)

# Download the second file
url2 = 'https://www.apradie.com/datos/findata_s1.csv'
response2 = requests.get(url2, headers=headers)
with open('findata1.csv', 'wb') as f:
    f.write(response2.content)

import pandas as pd
# Import as data frames
usfirms = pd.read_csv('usfirms1.csv')
uspanel = pd.read_csv('findata1.csv')

The uspanel is a panel data with quarterly historical financial data of a sub-sample of about 180 US firms. The usfirms is a catalog of these sample firms.

The variables (columns) for each dataset have the same structure as the datasets of Workshop 1.

See Workshop 1 (https://rpubs.com/cdorante/fz2024p_w1) to remember the columns and structure of each dataset.

Important Notes:

Remember that income-statement variables in the uspanel are Fiscal-Year-to-Date amounts. The yearf is the column for the fiscal year. Since you have YTD amounts, if you select rows where fiscalmonth=12, you endup with annual data for each firm.

3 FINANCIAL PROGRAMMING

You have to do the following:

Using these US firms and their historical financial information, you have to:

3.1 Part 1 (30pts) - First filter for Stock selection:

You have to select the best 20 stocks according to the fiscal annual 2023 Return on Assets (ROA).

ROA is calculated as NetIncome divided by Previous TotalAssets. Previous totalassets is the total assets one-year ago. Remember that the uspanel has quarterly data.

Using uspanel, netincome must be calculated as:

ebit = revenue - cogs - sgae - depreciation

netincome = ebit + otherincome + extraordinaryitems - finexp - incometax

3.2 Part 2 (40 pts) - Second filter for Stock selection:

For each of the best 20 stocks you selected in Part 1, download monthly close stock prices from Jan 2021 to Dec 2023.

With this historical data, you have to select the best 10 stocks according to their excess return over the market, based on the market regression model. You have to select the stocks with the highest excess return over the market (the highest beta0).

3.3 Part 3 (5 pts) - Third filter for Stock selection

For each of the 10 best stocks selected in Part 2, select the 5 stocks with the less market risk according to the market regression model.

3.4 Part 4 (25) - Portfolio Optimization

Using the 5 stocks selected in Part 3, find the portfolio with minimum variance (GMV). Show and explain the expected risk and return of your GMV portfolio

3.5 Part 5 (15 extra points) - Backtesting

Using the GMV portfolio you estimated, calculate the holing-period-return of this portfolio if you had invested in this portfolio from Jan 2024 to Nov 2025. Report your result

You MUST DOCUMENT EACH PART WITH YOUR WORDS