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!


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:

 #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.

enter image description here

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.

  • $\begingroup$ The problem is that BQL cannot see historical portfolio holdings, thus the returned values are incorrect, as I have rebalanced my portfolios in the past. $\endgroup$ – Friedrich Jan 18 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.