You are a fintech analyst working for a investment firm. Your task is to analyze a stock portfolio and provide insights to the investment team. The portfolio consists of various stocks with their corresponding quantities and prices. Your goal is to calculate the total value of the portfolio, the average price of each stock, and the stock with the highest total value.
Using Python and pandas, read the data into a DataFrame and perform the following tasks:
Symbol Quantity Price
AAPL 100 145.23
MSFT 50 234.56
AMZN 20 3123.12
GOOGL 30 2456.78
FB 40 356.12
import pandas as pd
import sys
print('Python version ' + sys.version)
print('Pandas version ' + pd.__version__)
Python version 3.11.7 | packaged by Anaconda, Inc. | (main, Dec 15 2023, 18:05:47) [MSC v.1916 64 bit (AMD64)] Pandas version 2.2.1
df = pd.read_clipboard()
df
Symbol | Quantity | Price | |
---|---|---|---|
0 | AAPL | 100 | 145.23 |
1 | MSFT | 50 | 234.56 |
2 | AMZN | 20 | 3123.12 |
3 | GOOGL | 30 | 2456.78 |
4 | FB | 40 | 356.12 |
# verify data types
df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 5 entries, 0 to 4 Data columns (total 3 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Symbol 5 non-null object 1 Quantity 5 non-null int64 2 Price 5 non-null float64 dtypes: float64(1), int64(1), object(1) memory usage: 252.0+ bytes
# total value per Symbol
df['Total Value'] = df['Quantity'] * df['Price']
df
Symbol | Quantity | Price | Total Value | |
---|---|---|---|---|
0 | AAPL | 100 | 145.23 | 14523.0 |
1 | MSFT | 50 | 234.56 | 11728.0 |
2 | AMZN | 20 | 3123.12 | 62462.4 |
3 | GOOGL | 30 | 2456.78 | 73703.4 |
4 | FB | 40 | 356.12 | 14244.8 |
# calculate the total value of the portfolio
total = df['Total Value'].sum()
total
176661.59999999998
# calculate the average price of each stock
df['Avg Price'] = total/df['Price']
df
Symbol | Quantity | Price | Total Value | Avg Price | |
---|---|---|---|---|---|
0 | AAPL | 100 | 145.23 | 14523.0 | 1216.426358 |
1 | MSFT | 50 | 234.56 | 11728.0 | 753.161664 |
2 | AMZN | 20 | 3123.12 | 62462.4 | 56.565742 |
3 | GOOGL | 30 | 2456.78 | 73703.4 | 71.907782 |
4 | FB | 40 | 356.12 | 14244.8 | 496.073234 |
# Determine the stock with the highest total value and display its symbol, quantity, price, and total value
df.sort_values(by='Total Value', ascending=False).head(1)
Symbol | Quantity | Price | Total Value | Avg Price | |
---|---|---|---|---|---|
3 | GOOGL | 30 | 2456.78 | 73703.4 | 71.907782 |
This tutorial was created by HEDARO