Mathew K AnalyticsLesson 11 · Finance and Stock Market Analytics
Structure of Stock Market Datasets for Analytical Training
This lesson covers the structure of real stock market data you will use as a finance analyst. In finance and stock market work, understanding how data is…
- CourseFinance and Stock Market Analytics
- Lesson11 of 16
- Video24 min
- FormatJupyter notebook · 17 code cells
What you'll learn
- Understanding the Structure of Stock Market Data
- Data Structure: Long Format (One Row per Ticker/Date)
- Example: Stock Prices (Wide Format)
- S&P 500 Company List: One Row Per Company
- Portfolio Dataset: Simulated Positions Using Real Prices
- Fundamentals Dataset: Company-Level Data
- Returns/Time Series Data: Row per Date
- Advanced: Error Handling with Real Datasets
Data
No separate download needed — the notebook creates or downloads everything it uses.
📓 Full notebook
Download .ipynbStructure of Stock Market Datasets#
- This lesson covers the structure of real stock market data you will use as a finance analyst.
- In finance and stock market work, understanding how data is organised is essential for real-world analysis.
- You will learn to recognise and manipulate different types of real market datasets.
- We will cover multiple real stock market data formats, their meanings, and common mistakes beginners make.
- By the end, you will confidently explore, reshape, and work with real financial data for analysis tasks.
import pandas as pd
import numpy as np
import yfinance as yf
import warnings
warnings.filterwarnings('ignore')
Understanding the Structure of Stock Market Data#
- Stock market data comes in many forms: daily prices, financial fundamentals, and trading signals.
- Some datasets have one row per company; some have one row per date and ticker.
- Common columns are Date, Ticker, Open, Close, Volume, and sometimes financial ratios like P/E.
- Beginners often get confused by MultiIndex columns or reshaped (pivoted) dataframes.
- Learning to quickly interpret these structures is vital for analysis and modeling.
tickers = ['AAPL','MSFT','GOOGL','AMZN','TSLA']
ohlcv = yf.download(tickers, period='1y', auto_adjust=True, progress=False)
ohlcv = ohlcv.stack(future_stack=True).rename_axis(['Date','Ticker']).reset_index()
ohlcv.columns.name = None
print(ohlcv.shape)
print(ohlcv.head(3))
Data Structure: Long Format (One Row per Ticker/Date)#
- Each record now shows: date, ticker, open price, high, low, close, volume.
- This 'long format' is perfect for time series analysis and groupby statistics.
- Example: To filter for just AAPL, use ohlcv[ohlcv['Ticker'] == 'AAPL'].
- Beginners sometimes expect each company to be a single column do not be fooled!
aapl = ohlcv[ohlcv['Ticker'] == 'AAPL']
print(aapl.head(3))
ohlcv_wide = ohlcv.pivot(index='Date', columns='Ticker', values='Close')
print(ohlcv_wide.head(3))
Example: Stock Prices (Wide Format)#
- Next, let us look at a stock price dataset: one row per date, columns for each ticker.
- This format is ideal for rolling windows, correlations, or visualising comparisons.
- Beware: If a dataset comes from yfinance with multiple tickers, column names are sorted alphabetically.
tickers = ['AAPL','MSFT','GOOGL','AMZN','TSLA']
df = yf.download(tickers, period='1y', auto_adjust=True, progress=False)
df = df['Close'].reset_index()
df.columns.name = None
print(df.shape)
print(df.head(3))
first_day = df.iloc[0]
print(first_day)
sp500_url = 'https://raw.githubusercontent.com/datasets/s-and-p-500-companies/main/data/constituents.csv'
sp500_df = pd.read_csv(sp500_url)
print(sp500_df.shape)
print(sp500_df.head(3))
S&P 500 Company List: One Row Per Company#
- This dataset shows one row per company, not per date.
- Typical columns: ticker symbol, company name, sector, headquarter location, and more.
- Such lists are critical for sector analysis, universe filtering, or mapping tickers to industries.
tech_companies = sp500_df[sp500_df['GICS Sector'] == 'Information Technology']
print(tech_companies[['Symbol', 'Security']].head(3))
portfolio_tickers = ['AAPL','MSFT','GOOGL','AMZN','TSLA','NVDA','META','NFLX','JPM','JNJ']
sector_map = {'AAPL':'Tech','MSFT':'Tech','GOOGL':'Tech','AMZN':'Consumer',
'TSLA':'Auto','NVDA':'Tech','META':'Tech','NFLX':'Media',
'JPM':'Finance','JNJ':'Health'}
np.random.seed(42)
data = yf.download(portfolio_tickers, period='1y', auto_adjust=True, progress=False)['Close']
rows = []
for tk in portfolio_tickers:
buy = round(float(data[tk].iloc[0]), 2)
cur = round(float(data[tk].iloc[-1]), 2)
rows.append({'ticker': tk, 'shares': int(np.random.randint(5, 100)),
'buy_price': buy, 'cur_price': cur, 'sector': sector_map[tk]})
df_portfolio = pd.DataFrame(rows)
df_portfolio['gain_pct'] = np.round((df_portfolio['cur_price'] - df_portfolio['buy_price']) / df_portfolio['buy_price'] * 100, 2)
print(df_portfolio.shape)
print(df_portfolio.head(3))
Portfolio Dataset: Simulated Positions Using Real Prices#
- Here, each row is a different stock held in a portfolio, with real buy and current prices.
- Random share counts simulate a real investor's positions.
- Columns include ticker, shares, sector, purchase price, latest price, and percentage gain.
- This format helps analyse portfolio performance, risk, and sector exposures.
- The gain_pct is computed using the real buy and end prices (not randomly made up).
biggest_win = df_portfolio.sort_values('gain_pct', ascending=False).iloc[0]
print(biggest_win)
fundamental_tickers = ['AAPL','MSFT','GOOGL','AMZN','TSLA','NVDA','META','JPM','JNJ','XOM','WMT','V']
rows = []
for tk in fundamental_tickers:
info = yf.Ticker(tk).info
rows.append({
'ticker': tk,
'sector': info.get('sector', 'Unknown'),
'market_cap': info.get('marketCap'),
'pe_ratio': info.get('trailingPE'),
'eps': info.get('trailingEps'),
'profit_margin':info.get('profitMargins'),
'debt_equity': info.get('debtToEquity')
})
df_fundamental = pd.DataFrame(rows)
print(df_fundamental.shape)
print(df_fundamental.head(3))
Fundamentals Dataset: Company-Level Data#
- Here, each row is one company and columns store important financial benchmarks.
- Typical fields: market cap, trailing P/E, EPS, profit margin, debt/equity.
- Use such data for valuation, risk, and screening filters in finance.
- Beginners often forget: real world data may be missing values!
spy = yf.download('SPY', period='2y', auto_adjust=True, progress=False)
df_returns = spy[['Close','Volume']].reset_index()
df_returns.columns = ['date','price','volume']
df_returns['daily_return'] = (df_returns['price'].pct_change() * 100).round(4)
df_returns = df_returns.dropna().reset_index(drop=True)
print(df_returns.shape)
print(df_returns.head(3))
Returns/Time Series Data: Row per Date#
- Common for price and returns data: each record is a specific trading date.
- Key columns: date, price (typically close), daily return (%), and often volume.
- Such time series are critical for backtesting and volatility analysis.
signal_data = yf.download('AAPL', period='1y', auto_adjust=True, progress=False)
signal_df = signal_data[['Close','Volume']].reset_index()
signal_df.columns = ['date','close','volume']
signal_df['sma20'] = signal_df['close'].rolling(20).mean().round(2)
signal_df['sma50'] = signal_df['close'].rolling(50).mean().round(2)
signal_df['signal'] = np.where(signal_df['sma20'] > signal_df['sma50'], 1, -1)
print(signal_df.shape)
print(signal_df.tail(3))
Advanced: Error Handling with Real Datasets#
- Real financial datasets may have missing prices, nulls, or bad types.
- Always check for NaN values when performing aggregations or calculations.
- Watch for sudden changes in column names or order, especially after downloading new data.
nan_count = signal_df.isna().sum()
print(nan_count)
clean_df = signal_df.dropna().reset_index(drop=True)
print(clean_df.shape)
print(clean_df.head(2))
Best Practices for Working with Stock Market Datasets#
- Always examine the row/column structure before any analysis.
- Use .info(), .head(), and .describe() often to preview data types and ranges.
- Set np.random.seed(42) for any simulated portfolio positions to ensure reproducibility.
- Document column meanings if processing many datasets.
- Never guess ticker order after yfinance downloads: always verify.
print(clean_df.info())
print(signal_df.describe())
# End-to-End Example: Portfolio Gain by Sector
by_sector = df_portfolio.groupby('sector')['gain_pct'].mean().round(2)
print(by_sector)
For More Learning#
- Review this notebook regularly to reinforce how financial data is structured.
- Explore real datasets on Yahoo Finance or GitHub, and practice inspecting their columns and row layouts.
- Check out additional guides on the official pandas documentation or YouTube for deeper skills.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
21 minLesson 59
Volatility and Risk Analysis Training for Finance and Stock Market
19 minLesson 11
Structure of Retail Transaction Datasets for Data Analytics Training
21 minLesson 48
Customer Cohort Analysis Training in Python for Market Research
26 minLesson 20