Analyzing Portfolio Risk with Google Sheets

Build a portfolio risk analytics tool in Google Sheets using for comprehensive data-driven investment decisions.

Effectively managing portfolio risk is crucial for informed investment decisions and maximizing returns. EODHD offers a robust set of financial APIs that integrate smoothly with Google Sheets, allowing users to conduct detailed portfolio risk analysis. This guide will take you through a step-by-step process to create a portfolio risk analytics spreadsheet using EODHD’s APIs and Google Sheets.

Example Template of the spreadsheet

We also recommend checking out our guides on Stock Market Dashboard in Google Sheets, Google Sheets Portfolio Tracker, and the EODHD Google Sheets Add-in. Check out more Ready-to-Go solutions.

Import Portfolio Holdings

Begin by making a copy of our sample spreadsheet. Navigate to “File” > “Make a copy” to save it to your Google Drive. This way, you can modify the spreadsheet to fit your specific requirements without altering the original version.

Next, create a table with columns labeled “Ticker”, “Quantity”, and “Purchase Price”. You can either manually input your portfolio holdings or utilize EODHD’s Google Sheets Add-On to import the data automatically.

For obtaining a list of supported exchanges, use the following API endpoint:

https://eodhd.com/api/exchanges-list/?api_token={your_api_token}&fmt=json

To get the list of tickers for a specific exchange, use the Exchange Symbol List API:

https://eodhd.com/api/exchange-symbol-list/{EXCHANGE_CODE}?api_token={your_api_token}&fmt=json

Retrieve Historical Prices

To calculate portfolio risk metrics, you need to obtain historical price data for each asset. Utilize the IMPORTDATA function in Google Sheets to fetch data from EODHD’s End-of-Day Historical Data API. The API endpoint format for retrieving historical data is as follows:

https://eodhd.com/api/eod/{ticker}?api_token={your_api_token}&period=d

Replace {ticker} with the ticker symbol (e.g., AAPL.US) and {your_api_token} with your EODHD API token. The IMPORTDATA function should be formatted like this:

=IMPORTDATA("https://eodhd.com/api/eod/AAPL.US?api_token=your_api_token&period=d")

This formula pulls historical data directly into your Google Sheets. You can then use an array of values from the imported API data to compute various metrics.

For improved readability and to avoid multiple API calls, the =LET() function in Google Sheets can be used to define variables for storing the API response, making complex formulas more manageable. The =ARRAYFORMULA() function allows you to perform calculations on an entire array within a single cell.

Here’s an example combining =LET() and =ARRAYFORMULA() together with the EODHD API call:

=LET( apiData, ARRAYFORMULA(VALUE(TRANSPOSE(IMPORTDATA("https://eodhd.com/api/eod/{ticker}?api_token={your_api_token}&fmt=json&filter=open")))), SUMIF(apiData, ">0")/ COUNTIF(apiData, ">0") )

In this example:

• The variable apiData stores the API response for further processing.
• The formula then calculates the average price of the stock using SUMIF and COUNTIF functions.

This approach reduces API calls and simplifies working with large datasets directly in Google Sheets.

Calculate Portfolio Returns

To compute the current portfolio return, you need to obtain the latest price data for each asset in your portfolio. This can be done using EODHD’s Live (Delayed) API, which provides up-to-date pricing information.

The API endpoint format for retrieving live data is:

https://eodhd.com/api/real-time/{ticker}?api_token={your_api_token}&fmt=json

The formula for the portfolio return is:

=(Price_Today - Price_Bought) / Price_Bought

Portfolio Volatility Calculation

The formula to calculate portfolio volatility (assuming no correlation between portfolio components) is as follows:

Portfolio Volatility = sqrt(Σ(w_i * (σ_i * sqrt(T))))

Where:

• w_i: weight (proportion) of component i in the portfolio
• σ_i: volatility (standard deviation) of component i
• T: number of periods in the time horizon (e.g., 252 trading days)

The formula for each component’s volatility is defined as:

Component Volatility = σ_i * sqrt(T)

In Google Sheets, the formula to determine component volatility can be represented as:

=LET(apiData, ARRAYFORMULA(IFERROR(VALUE(IMPORTDATA("https://eodhd.com/api/eod/{ticker}?api_token={your_api_token}&fmt=csv&filter=open&order=d"))),0)), prevData, QUERY(apiData, "SELECT * OFFSET 2", 0), currentData, QUERY(apiData, "SELECT * OFFSET 1", 0), change,IFERROR(ARRAYFORMULA((currentData - prevData)/prevData),0), STDEV(change)*SQRT(COUNT(change)))

Explanation of the formula:

1. Import Data: Uses IMPORTDATA to import data from the URL based on parameters like symbol, API token, and date.
2. Convert and Clean: Converts data using VALUE and replaces any errors with 0 using IFERROR.
3. Select Data Points: Extracts previous and current data points using the QUERY function, skipping rows as necessary.
4. Calculate Percentage Change: Computes the percentage change between current and previous data using ARRAYFORMULA and handles errors with IFERROR.
5. Calculate Volatility: Determines annualized volatility using the standard deviation of changes (STDEV) and multiplies by the square root of the count of values (SQRT and COUNT).

The result will display the annualized volatility for the specified input parameters based on the imported dataset.

You can also utilize the EODHD’s Technical API to retrieve individual components’ volatility and standard deviation metrics directly.

Calculate Beta

You can retrieve beta values for individual stocks using EODHD’s Technical Indicators API and import them into Google Sheets using the IMPORTDATA function:

=importdata("https://eodhd.com/api/technical/{ticker}?function=beta&api_token={your_api_key}&fmt=json&filter=last_beta")

This formula pulls in the current beta value for the specified ticker.

To calculate the portfolio’s weighted average beta, use the SUMPRODUCT function in Google Sheets:

=SUMPRODUCT(portfolio_weights_array, curent_beta)

Assess Value at Risk (VaR)
Value at Risk (VaR) is a statistical measure that estimates the potential financial losses within a specific time period for a firm, portfolio, or position. It’s a commonly used metric by investment and commercial banks to evaluate potential risks and loss probabilities in their institutional portfolios.

To calculate VaR, start by determining the mean and standard deviation of your portfolio’s daily returns.

• Mean:

=AVERAGE(Portfolio_Daily_Returns)

• Standard Deviation:

=STDEV(Portfolio_Daily_Returns)

Next, compute VaR using the following formula:

=NORM.INV(1-Confidence_Level, Mean, Standard_Deviation)

Replace Confidence_Level with your desired value (e.g., 0.95 for 95% confidence).

Visualize Risk Metrics

You can utilize Google Sheets’ built-in charting tools to visualize your portfolio’s risk metrics. For example, to create a sparkline chart showing the historical return volatility, you can use this formula:

=sparkline(LET( apiData, ARRAYFORMULA(IFERROR(VALUE(IMPORTDATA("https://eodhd.com/api/eod/"&B11&"."&$B$5&"?api_token={your_api_key}&fmt=csv&filter=open&order=d&from={date}")),0)), prevData, QUERY(apiData, "SELECT * OFFSET 2", 0), currentData, QUERY(apiData, "SELECT * OFFSET 1", 0),IFERROR(ARRAYFORMULA((currentData - prevData)/prevData),0) ))

To plot a sparkline for historical price performance, use the following formula:

=SPARKLINE(IMPORTDATA("https://eodhd.com/api/eod/{ticker}?api_token={your_api_key}&fmt=csv&filter=open&order=d&from={date}))

Conclusion

By leveraging EODHD APIs and following the steps outlined, you can create a comprehensive portfolio risk analytics tool in Google Sheets. This setup allows you to calculate essential risk metrics, visualize your portfolio’s risk profile, and make informed, data-driven investment decisions. Remember to keep your portfolio data updated, regularly retrieve the latest data from EODHD APIs, and actively monitor your risk metrics for optimal portfolio management.

Sign up for an EODHD APIs account today to unlock the full potential of integrated financial data and take your portfolio management to the next level.

We hope you found this article useful! If so, feel free to follow us for future content and give it a clap to support our work. Your engagement helps us continue providing valuable insights 🙂

Please note that this article is for informational purposes only and should not be taken as financial advice. We do not bear responsibility for any trading decisions made based on the content of this article. Readers are advised to conduct their own research or consult with a qualified financial professional before making any investment decisions.

For those eager to delve deeper into such insightful articles and broaden their understanding of different strategies in financial markets, we invite you to follow our account and subscribe for email notifications.

Stay tuned for more valuable articles that aim to enhance your data science skills and market analysis capabilities.