Best way to get Sharpe ratio and volatility based on BBG-data in Excel for automation

Question

For tickerized portfolios in PRTU, I want to extract Sharpe ratios and historic volatilities per annum or for 1, 3, 5 years from Bloomberg. So far, I have been using BBG V3COM API wrapper to extract historical prices and performances for my portfolios. Now I want to add risk measures and implement these into my existing automated procedures.

What's the best approach to get the risk measures I mentioned above?

I was thinking to calculate them myself based on the time series of the prices (seems a lot of work) or maybe get them out of BBG directly? Ideally, I'd like to use the wrapper, as the built-in excel function =BDH() is impractical to incorporate into macros, as discussed in one of my previous posts. Any pointers appreciated!

Answer 1

As was suggested in a comment in your previous post, BQL would be the way to go, and you should avoid using BDH.

Here is a simple examle of the inputs to get a rolling sharpe ratio for whatever tickers you want:

let(
 #dates=range(2021-01-04, 2021-01-15);
 #sharpe1y=rolling(sharpe_ratio(calc_interval=1Y), iterationdates=#dates);
 #sharpe3y=rolling(sharpe_ratio(calc_interval=3Y), iterationdates=#dates);
 #sharpe5y=rolling(sharpe_ratio(calc_interval=5Y), iterationdates=#dates);
)
get(#sharpe1y, #sharpe3y, #sharpe5y)
for("IBM US Equity")

In excel, just run =BQL(x) with x as a string with the above and you'd get something like this.

Shouldn't be too hard to add the standard deviation as well.

If python in an option, you could alternatively use Bloomberg's BQNT environment.