{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "50bb2bb6",
   "metadata": {},
   "source": [
    "<div style=\"background-color:#000;\"><img src=\"pqn.png\"></img></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cafa6d34",
   "metadata": {},
   "source": [
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f508fa0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7fac27e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b5b1cca",
   "metadata": {},
   "outputs": [],
   "source": [
    "from openbb_terminal.sdk import openbb\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59d04f12",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain import OpenAI\n",
    "from langchain.sql_database import SQLDatabase\n",
    "from langchain.chains import SQLDatabaseChain\n",
    "from langchain.agents import Tool, load_tools, initialize_agent"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a706992",
   "metadata": {},
   "source": [
    "Set the OpenAI API key environment variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d60df547",
   "metadata": {},
   "outputs": [],
   "source": [
    "os.environ[\"OPENAI_API_KEY\"] = \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce2dfe25",
   "metadata": {},
   "source": [
    "Create an in-memory SQLite database engine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62f3c19e",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"sqlite:///:memory:\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c2fe82a",
   "metadata": {},
   "source": [
    "Define a list of stock symbols for data retrieval."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57b98c1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "symbols = [\"META\", \"AMZN\", \"AAPL\", \"NFLX\", \"GOOG\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c3647ca",
   "metadata": {},
   "source": [
    "Retrieve historical stock prices for the defined symbols."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b3fa247",
   "metadata": {},
   "outputs": [],
   "source": [
    "prices = openbb.stocks.ca.hist(symbols)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e1f7169",
   "metadata": {},
   "source": [
    "Retrieve options chains for each symbol and append them to a list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3db7bdc5",
   "metadata": {},
   "outputs": [],
   "source": [
    "chains = []\n",
    "for symbol in symbols:\n",
    "    chain = openbb.stocks.options.chains(symbol)\n",
    "    chain[\"symbol\"] = symbol\n",
    "    chain[\"underlying_last\"] = prices.iloc[-1][symbol]\n",
    "    chains.append(chain)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3062f59",
   "metadata": {},
   "source": [
    "Concatenate all options chains into a single DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91ef4bc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "options_chains = pd.concat(chains)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23917cbc",
   "metadata": {},
   "source": [
    "Save the concatenated options chains to the SQLite database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "669171a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "options_chains.to_sql(\"options\", con=engine, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e855553",
   "metadata": {},
   "source": [
    "Initialize the OpenAI language model with a specified temperature setting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c6a31ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "llm = OpenAI(temperature=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1df619e",
   "metadata": {},
   "source": [
    "Initialize the SQLDatabase object with the created engine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc6269ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = SQLDatabase(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "832f8af5",
   "metadata": {},
   "source": [
    "Create a SQLDatabaseChain instance for querying the database using the language model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54bb5675",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_chain = SQLDatabaseChain.from_llm(\n",
    "    llm=llm, \n",
    "    db=db, \n",
    "    verbose=True\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22df2870",
   "metadata": {},
   "source": [
    "Define a Tool for querying options data using the SQL chain."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3cbec3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_tool = Tool(\n",
    "    name=\"Options DB\",\n",
    "    func=sql_chain.run,\n",
    "    description=\"Query options data.\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0193d046",
   "metadata": {},
   "source": [
    "Load LLM math tools and append the SQL tool to the list of tools."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "165c93bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "tools = load_tools([\"llm-math\"], llm=llm)\n",
    "tools.append(sql_tool)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26946096",
   "metadata": {},
   "source": [
    "Initialize a zero-shot agent with the defined tools and language model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03879406",
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_shot_agent = initialize_agent(\n",
    "    agent=\"zero-shot-react-description\",\n",
    "    tools=tools,\n",
    "    llm=llm,\n",
    "    verbose=True,\n",
    "    max_iterations=5,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4ce372a",
   "metadata": {},
   "source": [
    "Define a prompt to query the last prices of specific META call options."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c3d4e77",
   "metadata": {},
   "outputs": [],
   "source": [
    "prompt = \"\"\"\n",
    "What are the last prices of 5 META call options with expiration \n",
    "date greater than 60 days away and strike price within 5% of \n",
    "the underlying price?\n",
    "\n",
    "Create a list of the options and include the expiration date,\n",
    "strike price, and last price. Use that list to create a table\n",
    "using the following template:\n",
    "\n",
    "Expiration    Strike    Price\n",
    "------------------------------\n",
    "expiration_date    strike    last\n",
    "...\n",
    "expiration_date    strike    last\n",
    "\n",
    "If there are no results, print 'no results.'\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f76f33ef",
   "metadata": {},
   "source": [
    "Execute the prompt using the zero-shot agent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00dfcef9",
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_shot_agent.run(prompt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaba876c",
   "metadata": {},
   "source": [
    "Define a prompt to query specific options from each symbol with given criteria."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3b39756",
   "metadata": {},
   "outputs": [],
   "source": [
    "prompt = \"\"\"\n",
    "What are the 5 options from each symbol with \n",
    "expiration date between 40 and 60 days away, a strike \n",
    "price within 5% of the underlying price, open interest \n",
    "greater than 100, and the difference between the ask and \n",
    "the bid less than 0.05?\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0467548f",
   "metadata": {},
   "source": [
    "Execute the second prompt using the zero-shot agent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24f7ff4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_shot_agent.run(prompt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2d1c335",
   "metadata": {},
   "source": [
    "<a href=\"https://pyquantnews.com/\">PyQuant News</a> 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 <a href=\"https://gettingstartedwithpythonforquantfinance.com/\">get started with Python for quant finance</a>. For educational purposes. Not investment advise. Use at your own risk."
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
