Investing in financial markets can be both exciting and daunting. While there are no guarantees when it comes to investing, having a solid portfolio management system in place can help you stay organized and make more informed investment decisions. In this article, we’ll explore how you can use SQL to build a portfolio management system that can help you track your holdings, analyze performance, and make better investment decisions.

Why Use SQL for Portfolio Management?

SQL, or Structured Query Language, is a powerful tool for managing data in relational databases. By using SQL to manage your portfolio data, you can store and analyze large amounts of data in a structured and efficient manner. SQL is also highly flexible, allowing you to customize your portfolio management system to meet your specific needs and requirements. Whether you’re managing a personal portfolio or a large institutional fund, SQL can help you stay organized and make better investment decisions.

Creating a Portfolio Holdings Table.

The first step in building a portfolio management system with SQL is to create a table to store your portfolio holdings. The portfolio_holdings table will store information about the securities held in your portfolio, including the symbol, date, number of shares, cost basis, market value, percentage of the portfolio, sector, country, asset class, and notes. Here’s an example script to create a portfolio_holdings table:

CREATE TABLE portfolio_holdings (
  id SERIAL PRIMARY KEY, 
  symbol VARCHAR(10) NOT NULL, 
  date DATE NOT NULL, 
  shares NUMERIC(15, 4) NOT NULL, 
  cost_basis NUMERIC(15, 4) NOT NULL,
  market_value NUMERIC(15, 4) NOT NULL,
  percentage NUMERIC(5, 2) NOT NULL,
  sector VARCHAR(50), 
  country VARCHAR(50), 
  asset_class VARCHAR(50), 
  notes TEXT
);

1. symbol: The symbol or ticker of the security or financial instrument held in the portfolio.
2. date: The date on which the security was added or removed from the portfolio.
3. shares: The number of shares held for the security.
4. cost_basis: The cost basis for the security, which is the total amount paid to acquire the shares.
5. market_value: The current market value of the shares held in the portfolio.
6. percentage: The percentage of the total portfolio value represented by the security.
7. sector: The sector of the security, which can be used to analyze the portfolio’s diversification across different sectors.
8. country: The country of the security, which can be used to analyze the portfolio’s exposure to different geographic regions.
9. asset_class: The asset class of the security, which can be used to analyze the portfolio’s diversification across different types of assets (e.g., stocks, bonds, real estate).
10. notes: Any additional notes or comments related to the security or its performance.

Inserting Portfolio Holdings.

Once you’ve created the portfolio_holdings table, you can begin inserting your portfolio holdings. Here’s an example INSERT statement to add a row to the portfolio_holdings table:

INSERT INTO portfolio_holdings (symbol, date, shares, cost_basis, market_value, percentage, sector, country, asset_class, notes)
VALUES ('AAPL', '2023-02-23', 100.0, 15000.0, 17500.0, 10.0, 'Technology', 'USA', 'Equity', 'Purchased on market dip');

This statement inserts a new row into the portfolio_holdings table with values for the symbol, date, shares, cost_basis, market_value, percentage, sector, country, asset_class, and notes columns. You can modify the values in the VALUES clause to match the specific holding you want to add to your portfolio_holdings table.

Calculating Portfolio Performance.

One of the key benefits of using SQL for portfolio management is the ability to calculate portfolio performance metrics. For example, you can use SQL to calculate the total return, annualized return, volatility, and Shape ratio of your portfolio.

To calculate the Shape ratio of your portfolio, you can use the following SQL query:

WITH daily_returns as (
  SELECT date, SUM(close*shares) OVER (ORDER BY date) as portfolio_value,
    SUM(close*shares) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - SUM(close*shares) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) as daily_return
  FROM portfolio_holdings
  JOIN stock_prices ON portfolio_holdings.symbol = stock_prices.symbol AND portfolio_holdings.date = stock_prices.date
)
SELECT AVG(daily_return)/STDDEV(daily_return) as sharpe_ratio
FROM daily_returns;

Thanks for your interest !

GitHub repository: https://github.com/KsenoLv/Scripts/blob/Scripts/STRATEGY/Indi_scripts/portfolio.sql