strategy-lab/to_explore/pyquantnews/49_LangChainSQL.ipynb

This code retrieves historical stock data and options chains for specific symbols, storing the data in an SQL database. It then sets up a language model with tools for querying the database. Finally, it executes natural language prompts to extract specific options data based on given criteria. This workflow can be used in practice for financial analysis and investment decision-making.

In [ ]:

import os

In [ ]:

import pandas as pd

In [ ]:

from openbb_terminal.sdk import openbb
from sqlalchemy import create_engine

In [ ]:

from langchain import OpenAI
from langchain.sql_database import SQLDatabase
from langchain.chains import SQLDatabaseChain
from langchain.agents import Tool, load_tools, initialize_agent

Set the OpenAI API key environment variable.

In [ ]:

os.environ["OPENAI_API_KEY"] = """

Create an in-memory SQLite database engine.

In [ ]:

engine = create_engine("sqlite:///:memory:")

Define a list of stock symbols for data retrieval.

In [ ]:

symbols = ["META", "AMZN", "AAPL", "NFLX", "GOOG"]

Retrieve historical stock prices for the defined symbols.

In [ ]:

prices = openbb.stocks.ca.hist(symbols)

Retrieve options chains for each symbol and append them to a list.

In [ ]:

chains = []
for symbol in symbols:
    chain = openbb.stocks.options.chains(symbol)
    chain["symbol"] = symbol
    chain["underlying_last"] = prices.iloc[-1][symbol]
    chains.append(chain)

Concatenate all options chains into a single DataFrame.

In [ ]:

options_chains = pd.concat(chains)

Save the concatenated options chains to the SQLite database.

In [ ]:

options_chains.to_sql("options", con=engine, index=False)

Initialize the OpenAI language model with a specified temperature setting.

In [ ]:

llm = OpenAI(temperature=0)

Initialize the SQLDatabase object with the created engine.

In [ ]:

db = SQLDatabase(engine)

Create a SQLDatabaseChain instance for querying the database using the language model.

In [ ]:

sql_chain = SQLDatabaseChain.from_llm(
    llm=llm, 
    db=db, 
    verbose=True
)

Define a Tool for querying options data using the SQL chain.

In [ ]:

sql_tool = Tool(
    name="Options DB",
    func=sql_chain.run,
    description="Query options data.",
)

Load LLM math tools and append the SQL tool to the list of tools.

In [ ]:

tools = load_tools(["llm-math"], llm=llm)
tools.append(sql_tool)

Initialize a zero-shot agent with the defined tools and language model.

In [ ]:

zero_shot_agent = initialize_agent(
    agent="zero-shot-react-description",
    tools=tools,
    llm=llm,
    verbose=True,
    max_iterations=5,
)

Define a prompt to query the last prices of specific META call options.

In [ ]:

prompt = """
What are the last prices of 5 META call options with expiration 
date greater than 60 days away and strike price within 5% of 
the underlying price?

Create a list of the options and include the expiration date,
strike price, and last price. Use that list to create a table
using the following template:

Expiration    Strike    Price
------------------------------
expiration_date    strike    last
...
expiration_date    strike    last

If there are no results, print 'no results.'
"""

Execute the prompt using the zero-shot agent.

In [ ]:

zero_shot_agent.run(prompt)

Define a prompt to query specific options from each symbol with given criteria.

In [ ]:

prompt = """
What are the 5 options from each symbol with 
expiration date between 40 and 60 days away, a strike 
price within 5% of the underlying price, open interest 
greater than 100, and the difference between the ask and 
the bid less than 0.05?
"""

Execute the second prompt using the zero-shot agent.

In [ ]:

zero_shot_agent.run(prompt)

PyQuant News is where finance practitioners level up with Python for quant finance, algorithmic trading, and market data analysis. Looking to get started? Check out the fastest growing, top-selling course to get started with Python for quant finance. For educational purposes. Not investment advise. Use at your own risk.