Retrieving Rolling Sharpe Ratios from Bloomberg with BQL
Summary
The document describes a way to retrieve rolling Sharpe ratios for selected securities or portfolios from Bloomberg using BQL. Its example defines date iterations and requests rolling Sharpe calculations over several lookback windows, then returns the results for a specified equity. The query can be passed to Excel through the BQL function, which the author presents as more suitable for automated workflows than the historical-data function they had been using.
The response also suggests adding standard deviation to the query for volatility measures and points to Bloomberg’s BQNT environment as an alternative for Python users. The example demonstrates the query structure, but it does not provide a full implementation for portfolio tickers, standard deviation fields, or a comparison of calculated values against independently computed measures. Users still need to check the calculation conventions, date handling, and data availability relevant to their workflow.
Key ideas
- BQL can request rolling Sharpe ratios for specified securities over selected calculation windows.
- The example iterates across dates and returns multiple rolling Sharpe series in one query.
- The query can be run from Excel using Bloomberg’s BQL function.
- The response suggests extending the query for standard deviation or using BQNT with Python, but gives no validation details.
Tags
Full text
# Best way to get Sharpe ratio and volatility based on BBG-data in Excel for automation
# Best way to get Sharpe ratio and volatility based on BBG-data in Excel for automation
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 by David Duarte (score 2, accepted)
https://quant.stackexchange.com/a/60561
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.Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.