{"id":5362,"date":"2024-03-25T15:06:39","date_gmt":"2024-03-25T15:06:39","guid":{"rendered":"https:\/\/eodhd.com\/financial-academy\/?p=5362"},"modified":"2024-07-24T16:12:21","modified_gmt":"2024-07-24T16:12:21","slug":"portfolio-risk-analytics-using-google-sheets","status":"publish","type":"post","link":"https:\/\/eodhd.com\/financial-academy\/fundamental-analysis-examples\/portfolio-risk-analytics-using-google-sheets","title":{"rendered":"Portfolio Risk Analytics using Google Sheets"},"content":{"rendered":"\n

Managing portfolio risk<\/a> 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.<\/p>\n\n\n\n

Example of the spreadsheet<\/a>.<\/p>\n\n\n\n

Register & Get Data<\/span><\/a><\/p>\n\n\n\n

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

Import Portfolio Holdings <\/h2>\n\n\n\n

First, get your hands on our sample spreadsheet<\/a>. 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.<\/p>\n\n\n\n

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<\/a> to import your data automatically.<\/p>\n\n\n\n

You can access the list of supported exchanges via Exchanges API<\/a>:<\/p>\n\n\n\n

https:\/\/eodhd.com\/api\/exchanges-list\/?api_token={your_api_token}&fmt=json<\/pre>\n\n\n\n
And for the list of tickers Exchange Symbol List API:<\/p>\n\n\n\n
https:\/\/eodhd.com\/api\/exchange-symbol-list\/{EXCHANGE_CODE}?api_token={your_api_token}&fmt=json<\/pre>\n\n\n\n
Retrieve Historical Prices <\/h2>\n\n\n\n
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: <\/p>\n\n\n\n
https:\/\/eodhd.com\/api\/eod\/{ticker}?api_token={your_api_token}&period=d<\/a><\/pre>\n\n\n\n
Replace {ticker} with the ticker symbol and {your_api_token} with your EODHD API token. The function should look like this: <\/p>\n\n\n\n
=IMPORTDATA(\"https:\/\/eodhd.com\/api\/eod\/AAPL.US?api_token=your_api_token&period=d\")<\/pre>\n\n\n\n
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()”<\/a> 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.<\/p>\n\n\n\n
The “=ARRAYFORMULA()” cell formula could be used to perform calculations on an imported array in the cell. <\/p>\n\n\n\n
An example of the use of “=LET()”<\/a> and “=ARRAYFORMULA()<\/a>” together with the API call for EOD data:<\/p>\n\n\n\n
=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\") )<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
Calculate Portfolio Returns <\/h2>\n\n\n\n
For the current portfolio return, you need to retrieve live data. The current price is available via Live(Delayed) API<\/a>:<\/p>\n\n\n\n
https:\/\/eodhd.com\/api\/real-time\/{ticker}?api_token={your_api_token}&fmt=json<\/pre>\n\n\n\n
The formula for the portfolio return is:<\/p>\n\n\n\n
=(Price_Today - Price_Bought) \/ Price_Bought<\/pre>\n\n\n\n
\"Portfolio<\/figure>\n\n\n\n
Measure Portfolio Volatility<\/h2>\n\n\n\n
The formula for portfolio volatility<\/a> (assuming there is no correlation between components):<\/p>\n\n\n\n
Portfolio Volatility = sqrt(\u03a3(w_i * (\u03c3_i * sqrt(T)))) <\/p>\n\n\n\n
where:<\/p>\n\n\n\n