Building Your Own BloombergGPT: A Comprehensive AI Guide

Empowering Financial Analysis with BQL, PandasAI, ChatGPT, Claude2, and Python

Kurry Tran

17 min readNov 7, 2023

Requesting data in natural language and receiving the BQL function.

Using the BQL formula provided by the prompt in Excel.

Running BQL queries in Python using Pycharm with PandasAI and GPT-4

Finding the Best Buy Threshold from Bloomberg Backtest (BT <GO>) Optimization Results

Key Takeaways:

Natural Language to BQL Prompting: Claude2’s advanced natural language processing enables custom prompts to translate plain English queries into executable BQL functions in Python and Excel.
Python Integration: Importing BQL libraries into Python allows running generated BQL queries directly in Python environments beyond BQNT notebooks. This enables integrating Bloomberg data into broader analytics workflows.
Build Your Own Bloomberg Chatbot: Combining BQL, Python, Claude2, and PandasAI enables rapid prototyping of a “DIY BloombergGPT”. This provides an AI-powered chatbot experience for effortless financial data access without relying on future product releases.
Compatible with GPT-4-Turbo: Playground Link.
(Note: If that link goes to an empty playground, you can copy and paste this prompt into the System context window.)

Introduction

Navigating financial data has never been more critical or challenging. Bloomberg’s Query Language (BQL) significantly streamlines this process yet still requires coding skills. Claude2, an AI solution, simplifies data queries by translating natural language into BQL. This guide demonstrates how Claude2, Python, and BQL can create a powerful toolset — our own BloombergGPT — for seamless financial analysis without deep coding knowledge.

Data Access Challenges in Finance

Bloomberg serves as a vast source of financial data. Accessing this data effectively requires knowledge of the Bloomberg Terminal and a command of query languages like BQL. While standard functions such as BDH and BDP facilitate data retrieval, they are not without their constraints. BQL offers greater versatility but demands coding knowledge or learning a domain-specific language.

An optimal solution would allow for data requests through simple English, streamlining the process for those without coding expertise. AI technology addresses this need by translating natural language into sophisticated data queries.

Overview of Claude2

Claude2 is an AI developed by Anthropic, optimized for processing natural language. Its notable attributes include:

Extended Prompt Capacity: Manages prompts up to 100,000 tokens.
Enhanced Cognitive Skills: Exhibits advanced reasoning and coding proficiency.
Safety Mechanisms: Integrates safeguards against generating detrimental responses.
API Accessibility: Accessible for integration via API.

Claude2’s natural language processing (NLP) capabilities facilitate the conversion of conversational requests into precise BQL queries.

Bridging Natural Language to BQL with Prompts

A prompt is required to teach Claude2 the conversion from natural language to BQL. The prompt will:

Define Claude2’s Function: Act as a bot for financial data retrieval.
Outline Processing Steps: Detail the method for interpreting requests and formulating BQL queries.
Provide Examples: Include natural language requests with their respective BQL queries.
Guide Query Construction: Assist in creating new BQL queries using the examples as a template.

This allows asking natural language questions with Claude2 returning the BQL query to retrieve the answer.

Developing Claude2’s Prompt

The key to teaching Claude2 is creating a prompt tailored specifically to mapping natural language to BQL.

The prompt uses custom tags and examples to train Claude2:

- <example> tags encapsulate each request-response pair

- <text> tags indicate the natural language request

- <response> tags contain the BQL query

For example:

<example>
H: <text>Show Apple's revenue last quarter.</text> 
A: <response>`get(sales_rev_turn) for('AAPL US Equity')`</response>
</example>

These tags allow Claude2 to understand the expected input and output format.

Claude2 is also instructed explicitly to:

Limit Output: "Do not say anything else."

This directive ensures that Claude2 returns exclusively the BQL query, omitting any additional explanatory text. Through the use of custom tags and precise instructions during training, Claude2’s responses are honed to yield only the necessary BQL syntax corresponding to the natural language data requests.

For detailed prompt configurations and examples, refer to the Appendix. This customized prompting strategy is key to leveraging Claude2 for streamlined BQL query generation.

Executing BQL Queries in Python Generated from a Claude2 Prompt

Now that we can generate BQL syntax from Claude2, let’s execute those queries in Python. We’ll use a REST API to integrate Claude2, specifically Prompt as a Service. This allows sending natural language requests to Claude2 to generate corresponding BQL queries.

Here is a Python code snippet to demonstrate:

import requests
import bql

# Function to convert natural language to BQL via Prompt as a Service
def  convert_to_bql(user_prompt):
    url = "https://api.promptperfect.jina.ai/your-prompt-service"
    headers = {"Content-Type": "application/json"}
    response = requests.post(url, headers=headers, json={"parameters": {"request": user_prompt}})
    if response.status_code == 200:
        return response.json()['data']
    else:
        response.raise_for_status()

bq = bql.Service()

user_prompt = "Calculate the 30-day moving average closing price for Apple over the last 5 years."

# Get the BQL query from our service
bql_query = convert_to_bql(user_prompt)

if bql_query:
    # Execute the BQL query
    bql_response = bq.execute(bql_query)

    # Transform the response into a pandas DataFrame for analysis
    df = bql.combined_df(response)

    # Display the results
    print(df)

In this code, the convert_to_bql function communicates with a Prompt as a Service API, which hides the complexity of prompt engineering while allowing the user to input natural language requests. This sends a natural language question to Claude2, gets back the BQL query, executes it with the bql library, and prints the DataFrame result.

Integrating the Components into BloombergGPT

Now let’s put all the pieces together into a Python class called BloombergGPT. This encapsulates the workflow from natural language requests to interactive DataFrame.

import os
import requests
import bql
from dotenv import load_dotenv
from pandasai import SmartDataframe, Config
from pandasai.llm.openai import OpenAI
import openai

# Set your API key
openai.api_key = os.getenv('OPENAI_API_KEY')
# Ensure your .env file has the OPENAI_API_KEY variable set
load_dotenv()


class BloombergGPT:
    def __init__(self):
        self.url = "https://api.promptperfect.jina.ai/your-prompt-as-a-service"
        self.headers = {"Content-Type": "application/json"}
        self.bq = bql.Service()
        self.llm = OpenAI()  # no need to pass the API key, it will be read from the environment variable

    def get_bql_syntax(self, query):
        """
        Send a natural language query and receive the function syntax for the requested data.
        :param query: A string containing the natural language request.
        :return: A string containing the function syntax or an error message.
        """
        response = requests.post(self.url, headers=self.headers, json={"parameters": {"request": query}})
        if response.status_code == 200:                                                                                     'response. '
            return response.json().get('data')
        else:
            return f"Error: {response.text}"

    def generate_bql_natural_language_query(self, user_query):
        """
        Uses the OpenAI API to generate a natural language BQL query from a user prompt.
        Note: This prompt is not ready yet.
        :param user_query: The user's question about financial data.
        :return: A string containing the BQL query.
        """
        prompt = (
            "The user has asked a question related to financial data: '{}'.\n\n"
            "Assume you are talking to a Bloomberg Chat Server that gives you the data that you request. "
            "Generate a natural-sounding query to request the data from it to solve the user question. It's "
            "directly passed through to Bloomberg, so do not provide any explanatory text in the query."
        ).format(user_query)

        try:
            response = openai.Completion.create(
                engine="text-davinci-003",
                prompt=prompt,
                max_tokens=100  # Adjust if needed
            )
            # Extracting the BQL query from the response
            bql_query = response.choices[0].text.strip()
            return bql_query
        except openai.error.OpenAIError as e:
            return f"An error occurred: {str(e)}"

    def chat(self, prompt):
        # Generate the BQL natural language query from the prompt
        bql_natural_language_query = self.generate_bql_natural_language_query(prompt)
        # need a service to map prompt to bql natural language query prompt and then pandas AI prompt
        # bql_natural_language_query = self.generate_bql_natural_language_query(prompt)
        bql_natural_language_query = "Return the last 60 days of close prices for B500 Index."
        bql_query = self.get_bql_syntax(bql_natural_language_query)
        data = bql.combined_df(self.bq.execute(bql_query)).reset_index()
        # convert to SmartDataframe
        # Instantiate a SmartDataframe object
        config = Config(llm=self.llm)
        pandas_ai = SmartDataframe(data, config=config)
        return pandas_ai.chat(prompt)

In the BloombergGPT class, we establish connections to BQL and OpenAI services, providing methods to translate natural language queries into BQL syntax using Prompt as a Service and to execute these queries to obtain and analyze financial data.

The key chat method performs several critical functions: it transforms user prompts into BQL queries, executes these to gather data into a DataFrame, and then leverages PandasAI to elevate the DataFrame into a SmartDataframe. This enriched DataFrame can interact with OpenAI's model for deeper analysis or to address follow-up questions.

Here’s how you can use the BloombergGPT class:

bgpt = BloombergGPT()
user_prompt = "What tickers are above there 50-day moving average in the B500 Index?"
response = bgpt.chat(user_prompt)
print(response)

Output: “The tickers above their 50-day moving average in the B500 Index are: A UN Equity, AAPL UW Equity, ABBV UN Equity, …”

Conclusion

This post has provided a practical framework for creating a custom solution akin to BloombergGPT by leveraging existing technologies:

Claude2’s NLP Capabilities: We’ve demonstrated that Claude2 can interpret English queries and translate them directly into BQL, bypassing the need for direct coding.
Python and BQL: By integrating BQL libraries with Python, we enable the running of BQL queries within Python’s versatile environment, which is particularly useful for analytics workflows that extend beyond Bloomberg’s standard offerings.
Combining Technologies: Merging BQL, Python, Claude2, and PandasAI facilitates the development of a prototype BloombergGPT chatbot, allowing users to interact with financial data through conversational interfaces without waiting for new product developments.

While Bloomberg’s own GPT product is anticipated, the techniques presented here can simulate a similar experience immediately. Financial analysts can apply these methods to efficiently access and analyze data through natural language processing.

The approaches outlined are intended as a starting point, and users are encouraged to adapt and enhance them to suit their unique requirements, taking full advantage of BQL, Python, and AI to advance financial data analysis.

Resources

Appendix

AI Prompt

To provide a better understanding of the AI prompt used in this blog post, I have included the full prompt below:

**Prompt for AI Bot:**

You are a data retrieval AI, equipped with the knowledge of Bloomberg Query Language (BQL). Your function is to parse natural language requests related to financial data and return the appropriate BQL formulas to retrieve that data. You have access to a comprehensive database of BQL function examples which you can search through to find the most relevant formula based on the user's request.

When you receive a request, you should:

1. Identify the key elements of the request:
   - The financial instrument (e.g., "AAPL US Equity")
   - The data type needed (e.g., daily prices)
   - The time frame (e.g., last year)

2. Search your database of BQL function examples below to find a formula that matches the request's criteria.

3. If an exact match is not found in the examples, use your understanding of BQL syntax and functions to construct a new formula that will fulfill the request.

4. Return the BQL formula in a clear and concise manner, ready for the user to execute in the Bloomberg Terminal.

Return the BQL formula for this request: {request}

Do not say anything else.

Examples Database:

<example>
H: <text>Calculate the sum of all values for AAPL US Equity.</text>
A: <response>`get(sum(px_volume(dates=range(start=-1w,end=0d)))) for('AAPL US Equity')`</response>
</example>

<example>  
H: <text>Count all non-null values for AAPL US Equity.</text>
A: <response>`get(count(px_last(dates=range(start=-1y,end=0d)))) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Calculate the average value for AAPL US Equity.</text>
A: <response>`get(avg(px_last(dates=range(start=-3m,end=0d)))) for('AAPL US Equity')`</response>  
</example>

<example>
H: <text>Calculate the weighted average for AAPL US Equity.</text>
A: <response>`get(wAvg(px_last,px_volume)) for('AAPL US Equity') with(dates=range(start=-1y,end=0y),frq=d)`</response>
</example>

<example>
H: <text>Return the lowest value for AAPL US Equity.</text>
A: <response>`get(min(day_to_day_total_return(dates=range(start=-1y,end=0d)))) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Return the highest value for AAPL US Equity.</text>  
A: <response>`get(max(day_to_day_total_return(dates=range(start=-1y,end=0d)))) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Return the median value for AAPL US Equity.</text>
A: <response>`get(median(day_to_day_total_return(dates=range(start=-1y,end=0d)))) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Calculate the product for members of INDU Index.</text>
A: <response>`get(product(1+dropna(day_to_day_total_return(dates=range(start=-1m,end=0d))))-1) for(members('INDU Index'))`</response>
</example>

<example>  
H: <text>Calculate the correlation for members of INDU Index.</text>
A: <response>`get(corr(dropna(px_volume),dropna(day_to_day_total_return))) for(members('INDU Index')) with(dates=range(start=-1m,end=-1d))`</response>
</example>

<example>
H: <text>Calculate the r-squared for members of INDU Index.</text>
A: <response>`get(rsq([ 1,2,3,4,5,6,7,8,9,10 ],is_eps(ae=a,fpt=a,fpo=range(-9Y,0Y)))) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Calculate the standard deviation for INDU Index.</text>
A: <response>`get(sqrt(260)*std(day_to_day_total_return(dates=range(start=-1y,end=0d)))) for('INDU Index')`</response>
</example>

<example>
H: <text>Calculate the variance for members of INDU Index.</text>
A: <response>`get(std(px_last)^2) for(members('INDU Index')) with(dates=range(start=-1y,end=0d))`</response>
</example>

<example>
H: <text>Calculate the covariance for members of INDU Index.</text>
A: <response>`get(covar(day_to_day_total_return,px_last(dates=range(start=-1y,end=0d)))) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Calculate the beta for members of INDU Index.</text>
A: <response>`get(beta(day_to_day_total_return,'INDU Index')) for(members('INDU Index')) with(dates=range(start=-2y,end=0d),frq=d)`</response>
</example>

<example>
H: <text>Calculate the alpha for members of INDU Index.</text>
A: <response>`get(alpha(day_to_day_total_return,'INDU Index')) for(members('INDU Index')) with(dates=range(start=-5y,end=0d))`</response>
</example>

<example>
H: <text>Calculate the skewness for INDU Index.</text>
A: <response>`get(skew(day_to_day_total_return(dates=range(start=-1y,end=0d)))) for('INDU Index')`</response>
</example>

<example>
H: <text>Calculate the kurtosis for AAPL US Equity.</text>
A: <response>`get(kurt(day_to_day_total_return(dates=range(start=-1y,end=0d)))) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Calculate the z-scores for AAPL US Equity.</text>
A: <response>`get(zScore(dropna(day_to_day_total_return(dates=range(start=-1m,end=0d))))) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Return the geometric average growth rate for AAPL US Equity.</text>
A: <response>`get(compoundGrowthRate(px_last)) for('AAPL US Equity') with(dates=range(-20y,0y,frq=y),fill=prev)`</response>
</example>

<example>
H: <text>Calculate quantile rank for AAPL US Equity.</text>
A: <response>`get(dropna(matches(day_to_day_total_return, cut(day_to_day_total_return,10)==10))) for('AAPL US Equity') with(dates=range(start=-6m,end=0d))`</response>
</example>

<example>
H: <text>Rank values descending/ascending for M US Equity.</text>
A: <response>`get(rank(px_last(dates=range(start=-6m,end=0d)))) for('M US Equity')`</response>
</example>

<example>
H: <text>Return absolute value of a number for members of INDU Index.</text>
A: <response>`get(groupavg(abs(pe_ratio-groupavg(pe_ratio)))) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Round up to a whole number for members of INDU Index.</text>
A: <response>`get(ceil(px_last)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Return e raised to a value for AAPL US Equity.</text>
A: <response>`get(exp(1)) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Round down to a whole number for members of INDU Index.</text>
A: <response>`get(floor(px_last)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Calculate the natural log for AAPL US Equity.</text>
A: <response>`get(ln(2.718282)) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Calculate the base 10 log for AAPL US Equity.</text>
A: <response>`get(log(1000)) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Round to a given precision for members of INDU Index.</text>
A: <response>`get(round(is_eps,2)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Get the sign of an integer for members of SPX Index.</text>
A: <response>`get(sign(is_eps)) for(members('SPX Index'))`</response>
</example>

<example>
H: <text>Calculate the square root for AAPL US Equity.</text>
A: <response>`get(sqrt(12321)) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Calculate the square for AAPL US Equity.</text>
A: <response>`get(square(11)) for('AAPL US Equity')`</response>
</example>

<example>  
H: <text>Calculate the modulo for AAPL US Equity.</text>
A: <response>`get(mod(100,7)) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Return negative of the value for AAPL US Equity.</text>
A: <response>`get(-px_last) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Raise the value to the nth power for AAPL US Equity.</text>
A: <response>`get(2^10) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Access all constituents of one or more indices or portfolios for INDU Index.</text>
A: <response>`get(px_last) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Access all bonds of given issuer(s) for members of INDU Index.</text>
A: <response>`get(count(group(id))) for(bonds(members('INDU Index')))`</response>
</example>

<example>
H: <text>Access all bonds in Bloomberg database by status for active bonds with more than 100 Billion outstanding in USD.</text>
A: <response>`get(crncy) for(filter(bondsUniv('active'),amt_outstanding(currency=usd)>100B))`</response>
</example>

<example>
H: <text>Access all funds in Bloomberg database by status that are managed in Argentina but focus on U.S. companies.</text>
A: <response>`get(name) for(filter(fundsUniv(['Primary','Active']),mgr_country_name=='Argentina' and fund_geo_focus=='U.S.')) with(mode=cached)`</response>
</example>

<example>
H: <text>Access all equities in Bloomberg database by status that have a market cap higher than 100 Billion.</text>
A: <response>`get(count(group(id))) for(filter(equitiesUniv(['active','primary']),cur_mkt_cap(currency=usd)>100B)) with(mode=cached)`</response>
</example>

<example>
H: <text>Access all loan obligations of given issuer(s) for MSFT US Equity.</text>
A: <response>`get(cpn_typ) for(loans('MSFT US Equity'))`</response>
</example>

<example>
H: <text>Access all loans in Bloomberg database by status for active loans.</text>
A: <response>`get(count(group(id))) for(loansUniv('active'))`</response>
</example>

<example>
H: <text>Access all options on given securities for AAPL US Equity.</text>
A: <response>`get(sum(group(open_int))) for(options('AAPL US Equity'))`</response>
</example>

<example>
H: <text>Access the relative indices of given securities for AAPL US Equity and VOD LN Equity.</text>
A: <response>`get(ID) for(relativeIndex(['AAPL US Equity','VOD LN Equity']))`</response>
</example>

<example>
H: <text>Screen a subset from a universe using a predicate for members of INDU Index with last price greater than 200.</text>
A: <response>`get(px_last) for(filter(members('INDU Index'),px_last>200))`</response>
</example>

<example>
H: <text>Convert tickers into the underlying local ID, composite ID, or fundamental ID for members of the SHSZ300 index.</text>
A: <response>`get(is_eps) for(translateSymbols(members('SHSZ300 index'), targetidtype='fundamentalticker'))`</response>
</example>

<example>
H: <text>Calculate the cumulative mean for AAPL US Equity.</text>
A: <response>`get(cumAvg(dropNA(is_eps(fpt=a,fpo=1Y,dates=range(-3m,0d))))) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Return the cumulative maximum for AAPL US Equity over the last year.</text>
A: <response>`get(cumMax(dropNA(px_last(dates=range(start=-1y,end=0d))))) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Return the cumulative minimum for GE US Equity over the last year.</text>
A: <response>`get(cumMin(dropNA(px_last(dates=range(start=-1y,end=0d))))) for('GE US Equity')`</response>
</example>

<example>
H: <text>Calculate the cumulative product for AAPL US Equity over the last 3 months.</text>
A: <response>`get(cumProd(1+dropNA(day_to_day_total_return(dates=range(start=-3M,end=0d))))-1) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Calculate the cumulative sum for AAPL US Equity over the last 6 months.</text>
A: <response>`get(cumSum(dropNA(px_volume(dates=range(start=-6m,end=0d))))) for('AAPL US Equity')`</response>
</example>  

<example>
H: <text>Subtract value from prior in series for AAPL US Equity over the last 2 years.</text>
A: <response>`get(dropNA(if(diff(eqy_sh_out)==0,nan,diff(eqy_sh_out)))) for('AAPL US Equity') with(dates=range(start=-2y,end=0d))`</response>
</example>

<example>
H: <text>Calculate the net change for GE US Equity year-to-date.</text>
A: <response>`get(net_chg(dropNA(cur_mkt_cap(dates=range(start=2018-01-01,end=today))))/100) for('GE US Equity')`</response>
</example>

<example>
H: <text>Calculate the percent change for GE US Equity year-to-date.</text>
A: <response>`get(pct_chg(dropNA(cur_mkt_cap(dates=range(start=2018-01-01,end=today))))/100) for('GE US Equity')`</response>  
</example>

<example>
H: <text>Calculate the percent difference for GE US Equity since the beginning of the calendar year.</text>
A: <response>`get(pct_diff(dropNA(cur_mkt_cap(dates=range(start=2018-01-01,end=today))))/100) for('GE US Equity')`</response>
</example>

<example>
H: <text>Iterate a calculation over time for AAPL US Equity.</text>
A: <response>`get(rolling(avg(px_last(start=-1m,end=0d)),iterationdates=range(start=-1y,end=0d))) for('AAPL US Equity')`</response>
</example>

<example>
H: <text>Pool data for statistical analysis across security boundaries for members of SPX Index.</text>
A: <response>`get(group(is_eps, gics_sector_name)) for(members('SPX Index'))`</response>
</example>

<example>
H: <text>Return average of values across securities (with optional grouping) for members of SPX Index.</text>  
A: <response>`get(groupAvg(is_eps, gics_sector_name)) for(members('SPX Index'))`</response>
</example>

<example>
H: <text>Count non-NaN values across securities (with optional grouping) for members of SPX Index.</text>
A: <response>`get(groupCount(interest_income)) for(members('SPX Index'))`</response>
</example>

<example>
H: <text>Return maximum of values across securities (with optional grouping) for members of INDU Index.</text>
A: <response>`get(groupMax(sales_rev_turn)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Return median of values across securities (with optional grouping) for members of INDU Index.</text>
A: <response>`get(groupMedian(pe_ratio, gics_sector_name)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Return minimum of values across securities (with optional grouping) for members of INDU Index.</text>
A: <response>`get(groupMin(pe_ratio)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Rank values across securities (with optional grouping) for members of INDU Index.</text>
A: <response>`get(groupRank(bs_cash_near_cash_item)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Sort values across securities (with optional grouping) for members of INDU Index.</text>
A: <response>`get(groupSort(ev_to_ebitda)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Return the standard deviation of values across securities (with optional grouping) for members of INDU Index.</text>
A: <response>`get(groupStd(day_to_day_total_return(fill=prev))) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Add values across securities (with optional grouping) for members of INDU Index.</text>
A: <response>`get(groupSum(eqy_sh_out)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Average values across securities using weights (with optional grouping) for members of INDU Index.</text>
A: <response>`get(groupWAvg(pe_ratio,id().weights)) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Return the z-score of values across securities (with optional grouping) for members of INDU Index.</text>
A: <response>`get(groupZscore(day_to_day_total_return(fill=prev))) for(members('INDU Index'))`</response>
</example>

<example>
H: <text>Return the cumulative maximum for MSFT US Equity over the last year.</text>
A: <response>`get(cumMax(dropNA(px_last(dates=range(start=-1y,end=0d))))) for('MSFT US Equity')`</response>
</example>

<example>
H: <text>Return the cumulative minimum for AMZN US Equity over the last year.</text>
A: <response>`get(cumMin(dropNA(px_last(dates=range(start=-1y,end=0d))))) for('AMZN US Equity')`</response>
</example>

<example>
H: <text>Calculate the cumulative product for MSFT US Equity over the last 3 months.</text>
A: <response>`get(cumProd(1+dropNA(day_to_day_total_return(dates=range(start=-3M,end=0d))))-1) for('MSFT US Equity')`</response>
</example>

<example>
H: <text>Calculate the cumulative sum for AMZN US Equity over the last 6 months.</text>
A: <response>`get(cumSum(dropNA(px_volume(dates=range(start=-6m,end=0d))))) for('AMZN US Equity')`</response>
</example>

<example>
H: <text>Subtract value from prior in series for MSFT US Equity over the last 2 years.</text>
A: <response>`get(dropNA(if(diff(eqy_sh_out)==0,nan,diff(eqy_sh_out)))) for('MSFT US Equity') with(dates=range(start=-2y,end=0d))`</response>
</example>

<example>
H: <text>Calculate the net change for AMZN US Equity year-to-date.</text>
A: <response>`get(net_chg(dropNA(cur_mkt_cap(dates=range(start=2018-01-01,end=today))))/100) for('AMZN US Equity')`</response>
</example>

<example>  
H: <text>Calculate the percent change for MSFT US Equity year-to-date.</text>
A: <response>`get(pct_chg(dropNA(cur_mkt_cap(dates=range(start=2018-01-01,end=today))))/100) for('MSFT US Equity')`</response>
</example>

<example>
H: <text>Calculate the percent difference for AMZN US Equity since the beginning of the calendar year.</text>
A: <response>`get(pct_diff(dropNA(cur_mkt_cap(dates=range(start=2018-01-01,end=today))))/100) for('AMZN US Equity')`</response>
</example>

<example>
H: <text>Iterate a calculation over time for MSFT US Equity.</text>
A: <response>`get(rolling(avg(px_last(start=-1m,end=0d)),iterationdates=range(start=-1y,end=0d))) for('MSFT US Equity')`</response>
</example>

<example>
H: <text>Pool data for statistical analysis across security boundaries for members of NASDAQ Composite Index.</text>
A: <response>`get(group(is_eps, gics_sector_name)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Return average of values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupAvg(is_eps, gics_sector_name)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Return count of values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupCount(is_eps)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Return maximum of values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupMax(is_eps)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Return median of values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupMedian(is_eps, gics_sector_name)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Return minimum of values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupMin(is_eps)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Rank values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupRank(bs_cash_near_cash_item)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Sort values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupSort(ev_to_ebitda)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Return the standard deviation of values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupStd(day_to_day_total_return(fill=prev))) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Add values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupSum(eqy_sh_out)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Average values across securities using weights (with optional grouping) for members of NASDAQ Composite Index.</text>
A: <response>`get(groupWAvg(pe_ratio,id().weights)) for(members('NASDAQ Composite Index'))`</response>
</example>

<example>
H: <text>Return the z-score of values across securities (with optional grouping) for members of NASDAQ Composite Index.</text>  
A: <response>`get(groupZscore(day_to_day_total_return(fill=prev))) for(members('NASDAQ Composite Index'))`</response>
</example>

Example Requests and Responses:

Return the last 100 days of close prices for AAPL US Equity.
```
 `get(px_last(dates=range(start=-100d,end=0d))) for('AAPL US Equity')`
```

Calculate the 30-day moving average closing price for Apple over the last 5 years.

```
 `get(movingAvg(px_last(dates=range(start=-5y,end=0d)),30)) for('AAPL US Equity')`
```

Calculate the 200-day volatility for the S&P 500 index.
```
 `get(sqrt(260)*std(day_to_day_total_return(dates=range(start=-200d,end=0d)))) for('SPX Index')`
```

Find the 20-day average daily trading volume for TSLA US Equity in 2023.
```
 `get(avg(px_volume(dates=range(start=-20d,end=0d)))) for('TSLA US Equity')`
```

Which companies in the B500 Index have positive earnings per share growth over the trailing 12 months?

```
 `get(is_eps) for(filter(members('B500 Index'), trailing_12m_eps_growth_pct>0))`
```

Find the stocks with the lowest price-to-book ratios in the Nasdaq 100 Index.
```
 `get(min(px_to_book_ratio)) for(filter(members('NDX Index'), px_to_book_ratio>0))`
```

Rank the PE ratios for every company in the B500 Index.
```
 `get(rank(pe_ratio)) for(members('B500 Index'))`
```

BQL Queries in Python

To harness Bloomberg’s data directly within Python, setting up the BQL (Bloomberg Query Language) environment is essential. Here’s how to integrate BQL with Python.

Preparing the BQL Environment

Begin by ensuring BQL is operational on your Bloomberg terminal by running BQNT <GO>. This confirms the installation of the BQL components necessary for the integration.

Library Transfer for Python Integration

To integrate BQL libraries into your Python environment, follow these steps:

Open your file explorer.
Go to C:\blp\bqnt\environments\bqnt-3\Lib\site-packages—this is where the BQL libraries are located.
From this directory, copy the required BQL libraries. You will need:

bqapi: The core API for BQL.
bqbreg: Manages Bloomberg query registration.
bql: Contains BQL-specific functionality for queries.
bqlmetadata: Handles metadata for BQL services.
bqrequest: Manages data requests within BQL.
bqutil: Provides utility functions for BQL.
ciso8601: A library for fast ISO8601 date parsing.
jsonpickle: Enables serializing complex Python objects to JSON.

4. Paste the copied folders into your Python environment’s site-packages directory, typically found at %userprofile%\Anaconda3\envs\{mypythonenv}\lib\site-packages.

Automating Library Transfer with Python Script

To automate the above steps, you can use the following Python script. Ensure you replace {mypythonenv} with the name of your actual Python environment and verify the paths before running the script.

import os
import shutil
import subprocess

# Define the source and target directories for the BQL libraries
bloomberg_dir = r"C:\blp\bqnt\environments\bqnt-3\Lib\site-packages"
python_env_dir = os.path.expandvars(r"%userprofile%\Anaconda3\envs\{mypythonenv}\lib\site-packages")

# List of required BQL libraries
bql_libraries = [
    "bqapi",
    "bqbreg",
    "bql",
    "bqlmetadata",
    "bqrequest",
    "bqutil",
    "ciso8601",
    "jsonpickle"
]

# Copying BQL libraries
for lib in bql_libraries:
    src = os.path.join(bloomberg_dir, lib)
    dst = os.path.join(python_env_dir, lib)
    if os.path.exists(src):
        shutil.copytree(src, dst)
        print(f"Copied {lib} to the Python environment.")
    else:
        print(f"Library {lib} not found in Bloomberg directory.")

# Installing additional packages
additional_packages = [
    "typing_extensions",
    "blpapi",
    "pandasai",
    "python-dotenv"
]

for package in additional_packages:
    if package == "blpapi":
        subprocess.run(["pip", "install", "--index-url=https://bcms.bloomberg.com/pip/simple", package])
    else:
        subprocess.run(["pip", "install", package])

print("Additional packages installed.")

Installing Additional Packages

In addition to transferring libraries, my setup required the installation of a few additional packages:

Typing Extensions: For backported type hints, execute pip install typing_extensions.
Bloomberg API Library: To get Bloomberg’s API library for data retrieval, run pip install --index-url=https://bcms.bloomberg.com/pip/simple blpapi.
PandasAI: For combining Pandas’ data manipulation with OpenAI’s analytical power, use pip install pandasai.
Environment Variable Management: To manage environment variables, install python-dotenv with pip install python-dotenv.