Download our Risk Portfolio Tool Template to follow the article:<\/p>\n\n\n\n
Register & Get Data<\/span><\/a><\/p>\n\n\n\n <\/p>\n\n\n\n Monitoring the risk of a portfolio is a crucial aspect of investment management. By keeping a close eye on potential risks, investors can make informed decisions and adjust their strategies accordingly. Here are some key steps to effectively monitor the risk of your portfolio:<\/p>\n\n\n\n Using the EODHD API requires registering for an API key. However, you can follow along with this article using the ‘demo’ key for the tickers AAPL, MSFT, TSLA, and MCD.<\/p>\n\n\n\n To obtain an API Key please visit the EODHD website (https:\/\/eodhd.com\/<\/a>) and sign up for an account. Navigate to the API section and generate an API key. Make sure to keep this key secure, as it will be used to authenticate your API requests.<\/p>\n\n\n\n Register & Get Data<\/span><\/a><\/p>\n\n\n\n To import data from the EODHD API into Excel, we’ll use the WEBSERVICE <\/a>function. This allows fetching data from a URL directly into cells.<\/p>\n\n\n\n In a new Excel spreadsheet, enter the following formula to pull in the list of supported stock exchanges<\/a>:<\/p>\n\n\n\n Replace YOUR_API_KEY with your EODHD API key. This will populate the sheet with a JSON array of the exchange data. You can change “&fmt=json” to “&fmt=csv” to get the data in CSV format instead.<\/p>\n\n\n\n WEBSERVICE function loads all the data in one cell, for one value this function would be sufficient. However, for a list of values, it’s better to transform the data using TEXTSPLIT<\/a> and TRANSPOSE<\/a> functions. An example of the cell formula is the following:<\/p>\n\n\n\n In case your version of Excel does not support the TEXTSPLIT Excel function you can use a workaround utilizing the FILTERXML <\/a>function. To use the FILTERXML function please format JSON output using MID <\/a>and SUBSTITUTE <\/a>cell formulas. The result cell formula will look like this:<\/p>\n\n\n\n The function will return raws with the data from the JSON request.<\/p>\n\n\n\n Similar to get the stock ticker list<\/a> for a specific exchange, like the US market (code “US”):<\/p>\n\n\n\n This returns all active US stocks and ETFs in JSON format.<\/p>\n\n\n\n Create a separate sheet for the historical data required for the ratios’ calculations. Use the TRANSPOSE <\/a>function to transform your vertical list of tickers into a horizontal:<\/p>\n\n\n\n To import historical end-of-day price<\/a> data for a stock, use this formula:<\/p>\n\n\n\n Adjust the ticker symbol, date range, and API key as needed. This pulls in the daily OHLCV data for Apple from the specified date.<\/p>\n\n\n\n Volatility is a statistical measure of the dispersion<\/a> of returns for a given security or market index. In most cases, the higher the volatility, the riskier the security. Volatility is often measured from either the standard deviation<\/a> or variance<\/a> between returns from that same security or market index.<\/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 Excel formula for portfolio volatility:<\/p>\n\n\n\n Where $B$4:$B$68 range of historical daily price change. To calculate daily price change add new column and drag the formula:<\/p>\n\n\n\n =(current_price-previous_price)\/privious_price<\/p>\n\n\n\n To better visualize volatility you can compare price changes with the benchmark. In the example template, you will find the benchmark VTI ETF<\/a>. VTI ETF performance tracks the US market changes and a good indicator for comparison. To compare the performance of the portfolio and VTI ETF insert the line chart and select historical price and date data.<\/p>\n\n\n\n Beta <\/a>(\u03b2) is a measure of the volatility\u2014or systematic risk\u2014of a security or portfolio compared to the market as a whole (usually the S&P 500). Stocks with betas higher than 1.0 can be interpreted as more volatile than the S&P 500.<\/p>\n\n\n\n You can get beta using our Fundamental <\/a>or Technical<\/a> APIs. Fundamental API shows current beta while technical could be used for historical Beta values. An example of the API call using WEBSERVICE function:<\/p>\n\n\n\n Cell function pulls up JSON data for the current value of beta, and formats using the VALUE function into the float number.<\/p>\n\n\n\n To get a portfolio \u03b2 we could use a weighted sum of portfolio components’ betas:<\/p>\n\n\n\n Where: F9:F13 range of weights, G9:G13 range of component’s betas.<\/p>\n\n\n\n The Herfindahl index<\/strong> is a measure of the size of firms in relation to the industry they are in and is an indicator of the amount of competition among them.<\/p>\n\n\n\n The Excel formula for HHI is the following:<\/p>\n\n\n\n =SUMPRODUCT(F9:F13^2)<\/p>\n\n\n\n Where F9:F13 range of portfolio component weights. The index will help you to balance your portfolio.<\/p>\n\n\n\n Design a user-friendly dashboard in Excel to visualize your portfolio’s risk profile. Use charts, graphs, and tables to display risk metrics, asset allocations, and performance data. This dashboard will provide a clear overview of your portfolio’s risk exposure.<\/p>\n\n\n\n To insert charts go to insert->chart menu. Select the data range, and choose the type and style of your chart. Use sparkling in-cell charts to visualize individual dynamic historical data. An example of the visualization can be found in Our template.<\/p>\n\n\n\n Excel and EODHD’s APIs provide investors with a powerful toolkit for portfolio risk management. By harnessing the capabilities of these tools, investors can gain deeper insights into their portfolio’s risk profile, make informed decisions, and ultimately work towards achieving their financial goals.<\/p>\n\n\n\n Sign up for an EODHD API key to get started building your own best Excel risk management dashboard. Feel free to contact our support team at support@eodhistoricaldata.com<\/a> for any questions or current discount offers. We’re happy to assist you through the process of leveraging EODHD’s data to elevate your investment workflow.<\/p>\n\n\n\nPortfolio Risk Management<\/h2>\n\n\n\n
\n
Connect EODHD APIs to Excel<\/h2>\n\n\n\n
=WEBSERVICE(\"https:\/\/eodhd.com\/api\/exchanges-list\/?api_token=YOUR_API_KEY&fmt=json\")<\/pre>\n\n\n\n
=TEXTSPLIT(WEBSERVICE(\"https:\/\/eodhd.com\/api\/exchanges-list\/?api_token=YOUR_API_KEY&fmt=json\"),\":\",\",\")<\/pre>\n\n\n\n
=LET(raw_data, WEBSERVICE(\"https:\/\/eodhd.com\/api\/exchanges-list\/?api_token=YOUR_API_KEY&fmt=json\"), FILTERXML(\"<t><s>\"&SUBSTITUTE(MID(raw_data,2,LEN(raw_data)-2),\",\",\"<\/s><s>\")&\"<\/s><\/t>\",\"\/\/s\") )<\/pre>\n\n\n\n
=WEBSERVICE(\"https:\/\/eodhd.com\/api\/exchange-symbol-list\/US?api_token=YOUR_API_KEY&fmt=json\")<\/pre>\n\n\n\n
Importing Historical EOD Data<\/h2>\n\n\n\n
=TRANSPOSE(tickers_list)<\/pre>\n\n\n\n
=LET(raw_data, WEBSERVICE(\"https:\/\/eodhd.com\/api\/eod\/AAPL.US?api_token=YOUR_API_TOKEN&fmt=json&filter=adjusted_close&from=2024-01-01\"), FILTERXML(\"<t><s>\"&SUBSTITUTE(MID(raw_data,2,LEN(raw_data)-2),\",\",\"<\/s><s>\")&\"<\/s><\/t>\",\"\/\/s\") )<\/pre>\n\n\n\n
![\"Portfolio](\"https:\/\/eodhd.com\/financial-academy\/wp-content\/uploads\/2024\/04\/EOD_Price.gif\")
<\/figure>\n\n\n\nPortfolio Volatility<\/h2>\n\n\n\n
\n
=STDEV($B$4:$B$68)\/SQRT(COUNT($B$4:$B$68))<\/pre>\n\n\n\n
![\"Effective](\"https:\/\/eodhd.com\/financial-academy\/wp-content\/uploads\/2024\/04\/EOD_Price_change-1.gif\")
<\/figure>\n\n\n\nComponents Beta<\/h2>\n\n\n\n
=VALUE(WEBSERVICE(\"https:\/\/eodhd.com\/api\/fundamentals\/AAPL.US?api_token=YOUR_API_TOKEN&fmt=json&filter=Technicals::Beta\"))<\/pre>\n\n\n\n
=SUMPRODUCT(F9:F13,G9:G13)\/COUNT(G9:G13)<\/pre>\n\n\n\n
![\"\"](\"https:\/\/eodhd.com\/financial-academy\/wp-content\/uploads\/2024\/04\/Portfolio_Beta.gif\")
<\/figure>\n\n\n\nPortfolio Diversification Index (HHI)<\/h2>\n\n\n\n
Visualization<\/h2>\n\n\n\n
![\"Visualize](\"https:\/\/eodhd.com\/financial-academy\/wp-content\/uploads\/2024\/04\/Insert_Chart.gif\")
<\/figure>\n\n\n\nConclusion<\/h2>\n\n\n\n