A private financial data infrastructure project for collecting, standardizing, storing, and analyzing market, macro, industry, freight, commodity, and alternative datasets.
This repository is designed as a local research database and Python analytics library. The long-term goal is to make financial data easy to retrieve through service classes such as PriceService, MacroService, IndustryService, and FreightService, while also providing reusable tools for technical indicators, factor analysis, portfolio allocation, and macro-financial research.
This project is not just a collection of Excel files. It is intended to become a structured financial data platform with the following layers:
raw source files (excel, tradingview api, yfinance api etc.)
↓
python etl jobs
↓
parquet data lake
↓
duckdb database
↓
python service layer
↓
research, dashboards, allocation models, and reports
The database currently focuses on:
- market data
- macroeconomic indicators
- freight and shipping indicators
- commodity indices and futures
- industry indicators
- semiconductor data
- automobile data
- real estate data
- volatility and risk indicators
- alternative datasets
The metadata master currently contains hundreds of data items across major asset and alternative-data categories, including commodity, freight, equity, industry, macro, and risk.
Every dataset should be described before it is used.
The project uses a master metadata table to define:
- internal id
- asset class
- category
- sub-category
- instrument type
- symbol
- raw symbol
- exchange
- country
- name
- unit
- frequency
- source
- url
This makes the database easier to maintain as the number of datasets grows.
Raw files should be kept as close as possible to the original source format. Cleansed data should be saved separately.
Recommended structure:
data/
raw manually collected or source-specific files
data_lake/
raw/
standardized parquet files
refined/
cleaned and merged datasets
Database tables should generally store data in long form:
base_date | release_date | symbol | value
Analysis can then pivot the data into wide form when needed:
date | bdi | bsi | bcti | fbx | ...
This keeps the database normalized while still allowing easy modeling in Python.
For macro and alternative data, the project stores:
base_date
release_date
time
time_zone
This is important for avoiding look-ahead bias. A value should only be used after it was actually released and observable.
Users should not need to write raw SQL every time. Future service classes will provide simple Python interfaces such as:
freight = FreightService(db_path="alternative_data.duckdb")
df = freight.get_series(symbols=["BDI", "FBX"], start="2020-01-01")The project uses DuckDB as the local analytical database engine.
A typical fully qualified table name follows this pattern:
catalog.schema.table
Example:
select *
from alternative_data.freight.freight_data;Table:
alternative_data.freight.freight_data
Columns:
base_date
release_date
time
time_zone
symbol
exchange
country
value
Description:
freight_data stores time-series values for freight and shipping-related indices.
examples include bdi, bsi, bci, bcti, bpi, bhsi, blng, blpg, fbx, and bdti.
Logical key:
base_date + symbol + exchange
For datasets with multiple releases per base date, use:
base_date + release_date + time + symbol + exchange
Recommended table:
alternative_data.macro.macro_data
Recommended columns:
base_date
release_date
time
time_zone
symbol
exchange
country
actual
forecast
previous
preliminary_release
Purpose:
macro_data stores scheduled economic releases, including actual, forecast, previous, and preliminary release values.
For indicators with preliminary, revised, and final releases, the same base_date may appear multiple times with different release_date values.
Recommended logical key:
base_date + release_date + time + symbol + exchange
almost macroeconomic data derived from investing.com
Recommended table:
alternative_data.metadata.data_master
Recommended columns:
id
asset_class
category
sub_category
instrument_type
symbol
symbol_raw
exchange
country
name
name_kr
unit
frequency
source
url
Purpose:
data_master defines what each dataset is, where it comes from, how often it updates, and how it should be interpreted.
Each major data table should have a corresponding dictionary table.
Examples:
freight.freight_data → freight.freight_dictionary
macro.macro_data → macro.macro_dictionary
industry.industry_data → industry.industry_dictionary
price.price_data → price.price_dictionary
metadata.data_master → metadata.data_master_dictionary
Use lowercase SQL keywords for readability.
Preferred:
select *
from alternative_data.freight.freight_data
order by release_date;Avoid:
SELECT *
FROM alternative_data.freight.freight_data
ORDER BY release_date;The planned service layer will provide a clean interface for retrieving data from DuckDB.
Purpose:
retrieve price, index, futures, fx, and market data.
Example interface:
price = PriceService(db_path="database/alternative_data.duckdb")
df = price.get_price(
symbols=["spy", "tlt", "gld"],
start="2020-01-01",
end="2026-12-31",
field="close",
)Planned methods:
get_price()
get_ohlcv()
get_returns()
get_wide_price()
get_adjusted_price()
Purpose:
retrieve economic indicators with release-date awareness.
Example interface:
macro = MacroService(db_path="database/alternative_data.duckdb")
df = macro.get_release(
symbols=["cpi_yoy", "nfp", "unrate"],
start="2015-01-01",
use_release_date=True,
)Planned methods:
get_actual()
get_forecast()
get_surprise()
get_revision()
get_asof()
Purpose:
retrieve industry-level indicators such as semiconductor, automobile, real estate, and supply-chain data.
Example interface:
industry = IndustryService(db_path="database/alternative_data.duckdb")
df = industry.get_series(
symbols=["dram_spot", "nand_spot", "muvvi"],
start="2018-01-01",
)Planned methods:
get_series()
get_cycle_indicator()
get_sector_dashboard()
get_industry_momentum()
Purpose:
retrieve freight, shipping, rail, container, tanker, and bulk shipping indices.
Example interface:
freight = FreightService(db_path="database/alternative_data.duckdb")
df = freight.get_series(
symbols=["bdi", "fbx", "bcti"],
start="2020-01-01",
wide=True,
ffill=True,
)Planned methods:
get_series()
get_wide()
get_ffill()
get_shipping_dashboard()
get_freight_momentum()
The project will include reusable research tools.
Planned module:
src/indicators/technical.py
Planned functions:
moving_average()
exponential_moving_average()
relative_strength_index()
macd()
bollinger_band()
z_score()
drawdown()
rolling_volatility()
Planned module:
src/research/factor_model.py
Planned functions:
standardize_factor()
winsorize_factor()
calculate_factor_return()
calculate_beta()
rolling_correlation()
lead_lag_analysis()
Planned module:
src/portfolio/allocation.py
Planned models:
equal weight
inverse volatility
risk parity
mean-variance optimization
black-litterman
hierarchical risk parity
nested clustered optimization
regime-based tactical allocation
Planned module:
src/research/scenario.py
Planned features:
macro shock simulation
commodity shock analysis
freight shock analysis
inflation scenario analysis
fx stress scenario
rate shock scenario
Every ETL job should follow this pattern:
read source file
↓
normalize column names
↓
validate required columns
↓
convert date, time, numeric fields
↓
add metadata
↓
check duplicates
↓
save parquet
↓
load into duckdb
Recommended validation rules:
- required columns must exist
- dates must be parseable
- numeric values must be coercible
- logical primary key must not be duplicated
- symbol must exist in metadata master
- source and frequency should be defined
The project distinguishes:
base_date
release_date
time
time_zone
Definitions:
base_date:
reference period of the observation.
release_date:
date when the value became observable.
time:
local release time.
time_zone:
utc offset or local timezone representation.
This is especially important for:
- macroeconomic releases
- freight indices
- commodity indices
- market-sensitive alternative data
- backtesting
- event studies
- finish metadata master
- standardize raw parquet files
- create duckdb schemas
- create dictionary tables
- define logical keys
- implement BaseService
- implement PriceService
- implement MacroService
- implement IndustryService
- implement FreightService
- technical indicators
- return calculation
- rolling statistics
- factor transformation
- macro surprise calculation
- release-aware as-of joins
- asset allocation modules
- risk models
- optimization
- regime classification
- scenario simulation
- dashboard-ready data marts
- scheduled ingestion jobs
- data quality checks
- logging and monitoring
- automatic parquet refresh
- automatic duckdb table refresh
The project currently has:
- metadata master file
- freight data transformation pipeline
- macro data transformation pipeline
- parquet-based raw data lake
- duckdb-based database structure
- dictionary-table convention
- release-date and release-time aware schema design
The next development priority is to formalize the Python service layer and make data access easier from notebooks, research scripts, and dashboards.
This project is for private financial research, data engineering practice, and personal investment research infrastructure. It is not investment advice and should not be used as the sole basis for trading or allocation decisions.