Portfolio Risk Analytics using Google Sheets

Managing portfolio risk is essential for making informed investment decisions and optimizing returns. EOD Historical Data provides a comprehensive suite of financial APIs that seamlessly integrate with Google Sheets, enabling users to perform in-depth portfolio risk analytics. In this step-by-step guide, we’ll walk you through the process of building a portfolio risk analytics spreadsheet using EODHD’s APIs and Google Sheets.

Example of the spreadsheet.

Register & Get Data

You can also check our guides for Stock Market Dashboard in Google Sheets, Google Sheets Portfolio Tracker, and EODHD Google Sheets Add-in. Here you’ll find more information on our Ready-to-Go solutions.

Import Portfolio Holdings

First, get your hands on our sample spreadsheet. Make a copy of the spreadsheet in your Google Drive by clicking on “File” > “Make a copy.” This ensures that you can customize the spreadsheet to your needs, and your changes won’t affect the original spreadsheet.

You can start by setting up a table with columns for “Ticker,” “Quantity,” and “Purchase Price.” Manually enter your portfolio holdings or use EODHD’s Google Sheets Add-On to import your data automatically.

You can access the list of supported exchanges via Exchanges API:

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

And for the list of tickers 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 risk metrics, you’ll need historical price data for each asset in your portfolio. Use the IMPORTDATA function in Google Sheets to pull data from EODHD’s End-of-Day Historical Data API. The API endpoint for historical data is:

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

Replace {ticker} with the ticker symbol and {your_api_token} with your EODHD API token. The function should look like this:

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

Later you can use an array of values from the API output to calculate necessary metrics. To declare variables in Google Sheets you can use the “=LET()” function. It will make cell’s formulas with multiple API calls more readable, as well as allow you to not call the API multiple times.

The “=ARRAYFORMULA()” cell formula could be used to perform calculations on an imported array in the cell.

An example of the use of “=LET()” and “=ARRAYFORMULA()” together with the API call for EOD data:

=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 the cell formula, we declare the “apiData” variable, which could be used for in-cell calculations. The formula calculates the average price of the stock.

Calculate Portfolio Returns

For the current portfolio return, you need to retrieve live data. The current price is available via Live(Delayed) API:

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

Measure Portfolio Volatility

The formula for portfolio volatility (assuming there is no correlation between components):

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

where:

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

The formula for Component Volatility:

Component Volatility = σ_i * sqrt(T)

The Google Sheets cell formula for component volatility would be:

=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)))

The formula uses the LET function to break down a complex calculation into smaller, more manageable steps. Here’s what the formula does:

1. Imports data from a specified URL using IMPORTDATA, based on input parameters such as symbol, exchange, API token, and start date.
2. Converts the imported data into numeric values using VALUE and replaces any errors with 0 using IFERROR.
3. Extracts the previous and current data points from the imported data using the QUERY function, skipping the first two rows for previous data and the first row for current data.
4. Calculates the percent change between the current and previous data points using ARRAYFORMULA and replaces any division errors with 0 using IFERROR.
5. Calculates the annualized volatility of the percent changes by taking the standard deviation of the changes (using STDEV) and multiplying it by the square root of the count of values (using SQRT and COUNT).

The final result is the annualized volatility of the percent changes between the current and previous data points, based on the imported data and the specified input parameters.

You can also use EODHD Technical API to get components’ volatility and standard deviation.

Calculate Beta

Retrieve beta values for individual stocks using EODHD’s Technical Indicators API. Use the IMPORTDATA function to pull beta values into your spreadsheet:

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

The formula retrieves the current beta value for the ticker.

Calculate the weighted average beta of your portfolio using the SUMPRODUCT function:

=SUMPRODUCT(portfolio_weights_array,curent_beta)

Assess Value at Risk (VaR)

Value at risk (VaR) is a statistic that quantifies the extent of possible financial losses within a firm, portfolio, or position over a specific time frame. Investment and commercial banks commonly use this metric to determine the extent and probabilities of potential losses in their institutional portfolios.

To calculate VaR, use the NORM.INV and PERCENTILE functions in Google Sheets. First, calculate the mean and standard deviation of your portfolio’s daily returns.

Mean:

=AVERAGE(Portfolio_Daily_Returns) 

Standard Deviation:

=STDEV(Portfolio_Daily_Returns)

Then, calculate VaR using the formula:

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

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

Visualize Risk Metrics

Use Google Sheets’ charting tools to visualize your portfolio’s risk metrics.
You can create a sparkline chart to display the historical return volatility:

=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 sparkling for historical price performance:

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

Conclusion

By following these steps and leveraging EOD Historical Data’s APIs, you can build a powerful portfolio risk analytics tool in Google Sheets. This spreadsheet will enable you to calculate key risk metrics, visualize your portfolio’s risk profile, and make data-driven investment decisions. Remember to regularly update your portfolio holdings, retrieve the latest data from EODHD’s APIs, and monitor your risk metrics to stay on top of your portfolio’s performance.

Don’t miss out on this game-changing opportunity. Sign up for an EODHD API account today and unlock the full potential of API data integration.

Register & Get Data

Feel free to contact our support to ask for the current discounts, we would be happy to assist and guide you: support@eodhistoricaldata.com.