Workshop/10_PibCoPilot.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PIB CoPilot\n",
    "PIBs are also used to create a pitchbook by assessing a company's strategy, competitive positioning, review of financial statements, industry dynamics, and trends within the industry. \n",
    "1. Company Overview and Executive Bio - A brief description of the company and its key executives with biographies.\n",
    "2. Conference calls: The same day a company issues its quarterly press release, it will also hold a conference call. On the call, analysts often learn details about management guidance. These conference calls are transcribed by several service providers and can be accessed by subscribers of large financial data providers.\n",
    "3. Press Release: Can be found in the investor relations section of most companies' websites and contains the financial statements which are used in forms 10-K and 10-Q. \n",
    "4. News: News articles that may affect a company's stock price or growth prospect would be something that analysts look into, particularly within a 6-12 month time horizon.\n",
    "5. SEC filings: These regulatory documents require a company to file Form 10-K and Form 10-Q with the SEC on an ongoing basis. Form 10-K is a financial overview and commentary for the last year, usually found on the company's website. Form 10-Q is similar to form 10-K, but it is a report for the last quarter instead of the previous year.\n",
    "6. Equity research reports: Look into key forecasts for metrics like Revenue, EBITDA, and EPS for the company or competing firms to form a consensus estimate. \n",
    "7. Investor Presentations: Companies provide historical information as an important foundation from which forecasts are made to guide key forecasting drivers. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 0 -  Pre-requsite and imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os  \n",
    "import json  \n",
    "import openai\n",
    "from Utilities.envVars import *\n",
    "import uuid\n",
    "# Set Search Service endpoint, index name, and API key from environment variables\n",
    "indexName = SearchIndex\n",
    "\n",
    "# Set OpenAI API key and endpoint\n",
    "openai.api_type = \"azure\"\n",
    "openai.api_version = OpenAiVersion\n",
    "openai_api_key = OpenAiKey\n",
    "assert openai_api_key, \"ERROR: Azure OpenAI Key is missing\"\n",
    "openai.api_key = openai_api_key\n",
    "openAiEndPoint = f\"https://{OpenAiService}.openai.azure.com\"\n",
    "assert openAiEndPoint, \"ERROR: Azure OpenAI Endpoint is missing\"\n",
    "assert \"openai.azure.com\" in openAiEndPoint.lower(), \"ERROR: Azure OpenAI Endpoint should be in the form: \\n\\n\\t<your unique endpoint identifier>.openai.azure.com\"\n",
    "openai.api_base = openAiEndPoint\n",
    "davincimodel = OpenAiDavinci\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parameters\n",
    "embeddingModelType = \"azureopenai\"\n",
    "temperature = 0\n",
    "tokenLength = 1000\n",
    "symbol = 'VOYA'\n",
    "apikey = FmpKey\n",
    "os.environ['BING_SUBSCRIPTION_KEY'] = BingKey\n",
    "os.environ['BING_SEARCH_URL'] = BingUrl\n",
    "pibIndexName = 'pibdata'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.chains.qa_with_sources import load_qa_with_sources_chain\n",
    "from langchain.llms.openai import AzureOpenAI, OpenAI\n",
    "from langchain.embeddings.openai import OpenAIEmbeddings\n",
    "from langchain.docstore.document import Document\n",
    "from langchain.prompts import PromptTemplate\n",
    "from IPython.display import display, HTML\n",
    "from langchain.utilities import BingSearchAPIWrapper\n",
    "from langchain.chains.summarize import load_summarize_chain\n",
    "from langchain.docstore.document import Document\n",
    "from langchain.text_splitter import RecursiveCharacterTextSplitter\n",
    "import pandas as pd\n",
    "from langchain.prompts import PromptTemplate\n",
    "from datetime import datetime\n",
    "from pytz import timezone\n",
    "from dateutil.relativedelta import relativedelta\n",
    "from datetime import timedelta\n",
    "from Utilities.pibCopilot import indexDocs, createPressReleaseIndex, createStockNewsIndex, mergeDocs, createPibIndex, findPibData, findEarningCalls, deletePibData, performEarningCallCogSearch\n",
    "from Utilities.pibCopilot import indexEarningCallSections, createEarningCallVectorIndex, createEarningCallIndex, performCogSearch, createSecFilingIndex, findSecFiling\n",
    "from Utilities.pibCopilot import findLatestSecFilings, createSecFilingsVectorIndex, indexSecFilingsSections\n",
    "import typing\n",
    "from Utilities.fmp import *\n",
    "from langchain.chat_models import AzureChatOpenAI, ChatOpenAI\n",
    "\n",
    "# Flexibility to change the call to OpenAI or Azure OpenAI\n",
    "\n",
    "if (embeddingModelType == 'azureopenai'):\n",
    "    openai.api_type = \"azure\"\n",
    "    openai.api_key = OpenAiKey\n",
    "    openai.api_version = OpenAiVersion\n",
    "    openai.api_base = OpenAiBase\n",
    "\n",
    "    llm = AzureOpenAI(deployment_name=OpenAiDavinci,\n",
    "            temperature=temperature,\n",
    "            openai_api_key=OpenAiKey,\n",
    "            max_tokens=tokenLength,\n",
    "            batch_size=10, \n",
    "            max_retries=12)\n",
    "    \n",
    "    llmChat = AzureChatOpenAI(\n",
    "                openai_api_base=openai.api_base,\n",
    "                openai_api_version=OpenAiVersion,\n",
    "                deployment_name=OpenAiChat16k,\n",
    "                temperature=temperature,\n",
    "                openai_api_key=OpenAiKey,\n",
    "                openai_api_type=\"azure\",\n",
    "                max_tokens=tokenLength)\n",
    "    \n",
    "    logging.info(\"LLM Setup done\")\n",
    "    embeddings = OpenAIEmbeddings(deployment=OpenAiEmbedding, chunk_size=1, openai_api_key=OpenAiKey)\n",
    "elif embeddingModelType == \"openai\":\n",
    "    openai.api_type = \"open_ai\"\n",
    "    openai.api_base = \"https://api.openai.com/v1\"\n",
    "    openai.api_version = '2020-11-07' \n",
    "    openai.api_key = OpenAiApiKey\n",
    "    llm = OpenAI(temperature=temperature,\n",
    "            openai_api_key=OpenAiApiKey,\n",
    "            max_tokens=tokenLength)\n",
    "    embeddings = OpenAIEmbeddings(openai_api_key=OpenAiApiKey)\n",
    "\n",
    "    llmChat = ChatOpenAI(temperature=temperature,\n",
    "        openai_api_key=OpenAiApiKey,\n",
    "        model_name=\"gpt-3.5-turbo\",\n",
    "        max_tokens=tokenLength)\n",
    "    \n",
    "    embeddings = OpenAIEmbeddings(openai_api_key=OpenAiApiKey)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "central = timezone('US/Central')\n",
    "today = datetime.now(central)\n",
    "currentYear = today.year\n",
    "historicalDate = today - relativedelta(years=3)\n",
    "historicalYear = historicalDate.year\n",
    "historicalDate = historicalDate.strftime(\"%Y-%m-%d\")\n",
    "totalYears = currentYear - historicalYear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "#find CIK based on Symbol\n",
    "cik = str(int(searchCik(apikey=apikey, ticker=symbol)[0][\"companyCik\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "#symbol: str = \"AAPL\"\n",
    "#cik = \"320193\"\n",
    "#symbols: typing.List[str] = [\"AAPL\", \"CSCO\", \"QQQQ\"]\n",
    "#exchange: str = \"NYSE\"\n",
    "#exchanges: typing.List[str] = [\"NYSE\", \"NASDAQ\"]\n",
    "#query: str = \"AA\"\n",
    "#limit: int = 3\n",
    "#period: str = \"quarter\"\n",
    "#download: bool = True"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1. Paid Data - Company Profile and Key Executives"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Search index pibdata already exists\n",
      "Total docs: 1\n",
      "\tIndexed 1 sections, 1 succeeded\n",
      "Total docs: 1\n",
      "\tIndexed 1 sections, 1 succeeded\n",
      "[{'id': 'e006320f-5b41-49fc-9ddf-c386908a34b1', 'symbol': 'VOYA', 'cik': '1535929', 'step': '1', 'description': 'Company Profile', 'insertedDate': '2023-07-24', 'pibData': '[{\\'symbol\\': \\'VOYA\\', \\'mktCap\\': 7374694964, \\'companyName\\': \\'Voya Financial, Inc.\\', \\'currency\\': \\'USD\\', \\'cik\\': \\'0001535929\\', \\'isin\\': \\'US9290891004\\', \\'exchange\\': \\'New York Stock Exchange\\', \\'industry\\': \\'Financial Conglomerates\\', \\'sector\\': \\'Financial Services\\', \\'address\\': \\'230 Park Avenue\\', \\'city\\': \\'New York\\', \\'state\\': \\'NY\\', \\'zip\\': \\'10169\\', \\'website\\': \\'https://www.voya.com\\', \\'description\\': \"Voya Financial, Inc. operates as a retirement, investment, and employee benefits company in the United States. The company\\'s Wealth Solutions segment offers tax-deferred employer-sponsored retirement savings plans and administrative services; and individual retirement accounts, and other retail financial products and services, as well as financial planning and advisory services. This segment serves corporate, education, healthcare, and other non-profit and government entities, as well as institutional and individual customers. Its Investment Management segment provides fixed income, equity, multi-asset, and alternative products and solutions to individual investors and institutional clients through its direct sales force, consultant channel, banks, broker-dealers, and independent financial advisers. The company\\'s Health Solutions segment offers stop loss, group life, voluntary employee-paid, and disability products through consultants, brokers, third-party administrators, enrollment firms, and technology partners to mid-sized and large businesses. The company was formerly known as ING U.S., Inc. and changed its name to Voya Financial, Inc. in April 2014. Voya Financial, Inc. was incorporated in 1999 and is based in New York, New York.\"}]'}, {'id': '64c14bd3-131f-4507-827f-619d9658750d', 'symbol': 'VOYA', 'cik': '1535929', 'step': '1', 'description': 'Biography of Key Executives', 'insertedDate': '2023-07-24', 'pibData': '[{\\'name\\': \\'Mr. Rodney Owen Martin Jr.\\', \\'title\\': \\'Chairman & Chief Executive Officer\\', \\'biography\\': \" Rodney O. Martin Jr. is an American business executive who serves as the Chairman and Chief Executive Officer of Voya Financial, Inc., a Fortune 500 financial services company based in New York, New York. He has been at the helm of 8 different companies and has a bachelor\\'s degree in business administration from Alfred University. He began his career in 1975 as an agent with Connecticut Mutual Life Insurance Company and has since become the Chairman and CEO of Voya Financial, Inc. Heather Lavallee is the Chief Executive Officer and a board member of Voya Financial, Inc. and Rodney O. Martin Jr. is the Executive Chairman.\"}, {\\'name\\': \\'Ms. Heather Hamilton Lavallee\\', \\'title\\': \\'Pres, Chief Executive Officer of Wealth Solutions & Director\\', \\'biography\\': \" Voya Financial, Inc. (NYSE: VOYA) has appointed Heather Lavallee, currently chief executive officer of Voya\\'s Wealth Solutions business, as president and CEO-elect, and to the board of directors. Lavallee has 30 years of experience in the financial services industry and is a collaborative leader who excels in building high-performing businesses. She will become CEO on Jan. 1, 2023, succeeding Rodney O. Martin, Jr. as the company\\'s CEO. Lavallee is also responsible for driving Voya\\'s customer experience and profitable growth.\"}, {\\'name\\': \\'Tony Donghui Oh\\', \\'title\\': \\'Principal Accounting Officer\\', \\'biography\\': \" Voya Financial, Inc. has appointed Tony D. Oh as their Principal Accounting Officer, effective September 19, 2022. Tony is a Chief Accounting Officer and Corporate Controller at Voya Financial. Dina Santoro has been appointed to the new role of Chief Operating Officer (COO) for the business, and Donald C. Templin has joined the company as Executive Vice President and Chief Financial Officer (CFO). Hanwen Chen, Song Tang, Donghui Wu, and Daoguang Yang have published research on the Political Dynamics of Corporate Tax Avoidance, and Donghui Wu and Ye Qing have published research on Public Attention and Auditor Behavior. Wendy has been a board member and officer for national professional liability and healthcare insurance organizations, and Tony Melo is a Business Solutions Consultant at Voya Financial. Principal SimpleInvest portfolios are comprised primarily of Principal products, and Tony O\\'Donoghue is an Allied Health, Supply Chain Buyer at PHSA.\"}, {\\'name\\': \\'Mr. Rodney Owen Martin Jr.\\', \\'title\\': \\'Executive Chairman\\', \\'biography\\': \\' Rodney O. Martin Jr. is an American business executive and the Chairman and CEO of Voya Financial, Inc., a Fortune 500 financial services company based in New York, New York. He began his career in 1975 as an agent with Connecticut Mutual Life Insurance Company and earned his bachelor’s degree in business administration from Alfred University. He has more than 40 years of experience in the retirement, insurance and financial services industries and is also a board member of Junior Achievement USA.\\'}, {\\'name\\': \\'Ms. Heather Hamilton Lavallee\\', \\'title\\': \\'Chief Executive Officer of Wealth Solutions, CEO & Director\\', \\'biography\\': \\' Heather Lavallee is the Chief Executive Officer and a board member of Voya Financial, Inc. (NYSE: VOYA), a leading health, wealth and investment company. She has 30 years of experience in the financial services industry and is a collaborative leader. She was appointed President and CEO-elect, and appointed to Voya’s board to become Chief Executive Officer on Jan. 1, 2023. She is also on the board of Junior Achievement of Southwest New England and National Down Syndrome Society. She is responsible for driving Voya’s customer experience and is a unique bundle of intelligence, creativity and “edge”.\\'}, {\\'name\\': \\'Ms. Heather Hamilton Lavallee\\', \\'title\\': \\'Chief Executive Officer of Wealth Solutions, CEO & Director\\', \\'biography\\': \\' Heather Lavallee is the Chief Executive Officer and a board member of Voya Financial, Inc., a leading health, wealth and investment company. She has 30 years of experience in the financial services industry and is a collaborative leader who excels in building high-performing businesses. She is also on the board of Junior Achievement of Southwest New England and National Down Syndrome Society. Heather Lavallee is set to become CEO of Voya Financial on January 1, 2023, and Rodney O. Martin, Jr. will become Executive Chairman. She is also a Top 25 Women in Business 2022 Honoree and is responsible for driving Voya’s customer experience and growth.\\'}, {\\'name\\': \\'Ms. Christine Lynn Hurtsellers C.F.A., CFA\\', \\'title\\': \\'Chief Executive Officer of Voya Investment Management\\', \\'biography\\': \\' Christine L. Hurtsellers, CFA, is the Chief Executive Officer of Voya Investment Management, the asset management subsidiary of Voya Financial. She is responsible for the strategic direction and operational performance of the Investment Management business. She has 25 years of experience in the financial services industry, and holds the Chartered Financial Analyst® designation. She is also the Chief Executive Officer of Voya Investment Management Co. LLC and Voya Investment Management LLC.\\'}, {\\'name\\': \\'Ms. My Chi To\\', \\'title\\': \\'Executive Vice President & Chief Legal Officer\\', \\'biography\\': \\' My Chi To has had a successful career in the legal field, having been Editor-in-Chief of Debevoise Women’s Review, Associate at Nishimura & Asahi, and Executive Vice President and Chief Legal Officer at Voya Financial. She has also been appointed to the Executive Leadership Team at Voya Financial, and is currently the Chief Executive Officer of Lloyd H. Dean. Additionally, she has been named Regents Professor at Arizona State University, and Executive Vice President and Chief Legal Officer at Intel Corporation. She is also the President of CHI Health at Home.\\'}, {\\'name\\': \\'Mr. Kevin Dwight Silva\\', \\'title\\': \\'Executive Vice President & Chief HR Officer\\', \\'biography\\': \" Kevin Silva is the Executive Vice President and Chief Human Resources Officer for Voya Financial, a certified Great Place to Work® and recognized as one of the World’s Most. He has also served in senior human resources leadership roles for other companies. He is also a board member for Junior Achievement USA and the New York Institute of Technology. He is responsible for directing a human resources strategy aimed at building ING’s human capital by attracting, retaining and developing world-class employees and incenting them to deliver superior performance. He is 66 years old and has studied at New York University and St. John\\'s University.\"}, {\\'name\\': \\'Mr. Donald C. Templin\\', \\'title\\': \\'Executive Vice President & Chief Financial Officer\\', \\'biography\\': \\' Donald C. Templin is a businessperson with over 30 years of corporate finance experience. He recently joined Voya Financial as Executive Vice President and Chief Financial Officer, having previously served as Executive Vice President and CFO of Marathon Petroleum Corp. He has held various positions at Marathon Petroleum since 2011, including President of Refining, Marketing and Supply. He is a certified public accountant and a member of the American Institute of Certified Public Accountants.\\'}, {\\'name\\': \\'Tony D. Oh\\', \\'title\\': \\'Principal Accounting Officer\\', \\'biography\\': \\' Tony Oh has been appointed as the Principal Accounting Officer of Voya Financial, Inc. (NYSE: VOYA), a leading health, wealth and investment company. He has been working as the Chief Accounting Officer & Corporate Controller at Voya Financial for 11 months. Heather Lavallee has been appointed as the President and CEO-elect, and Donald C. Templin has been appointed as the Executive Vice President and Chief Financial Officer. The U.S. Department of Health and Human Services has appointed Dori Salcido as the Chief FOIA Officer.\\'}, {\\'name\\': \\'Mr. Santhosh  Keshavan\\', \\'title\\': \\'Executive Vice President & Chief Information Officer\\', \\'biography\\': \" Santhosh Keshavan is the Executive Vice President and Chief Information Officer of Voya Financial, Inc. (NYSE: VOYA). He has over 25 years of experience in the public and private sector, including expertise in financial services, technology, and operations. He is responsible for the firm\\'s technology systems, data, digital organization, information security, and infrastructure. He is also a trustee of New York Institute of Technology and owns 18,312 shares of Voya Financial stock worth more than $1,355,271. In addition, he recently joined Voya Financial as Senior Vice President and Chief Information Security Officer (CISO).\"}, {\\'name\\': \\'Mr. Charles Patrick Nelson\\', \\'title\\': \\'Chief Growth Officer & Vice Chairman\\', \\'biography\\': \" Charles Patrick Nelson is the Chief Growth Officer & Vice Chairman at Voya Inc. He is responsible for shaping Voya\\'s growth strategy, including customer segmentation and targeting new customer segments and solutions. His total compensation is $1,956,708 and he is also the President & Director at Voya Retirement Insurance & Annuity Co. and a Member of National Assn of Gvt Defined Contribution Adm, Inc. He is also the Senior Officer responsible for all aspects of Great-West Retirement Services® defined contribution and defined benefit business.\"}, {\\'name\\': \\'Mr. Charles Patrick Nelson\\', \\'title\\': \\'Vice Chairman & Strategic Advisor\\', \\'biography\\': \\' Charles Patrick Nelson is the Chief Growth Officer & Vice Chairman at Voya Financial, Inc. He has been working at Voya for 8 years and his total compensation is $1,956,708. He is also the Chief Executive Officer for Voya Retirement Insurance & Annuity Co. and President & Director at Voya Retirement Insurance & Annuity Co. His estimated net worth is at least $20.3 million. He is also a Vice President, Fiduciary at Eastern Bank Wealth Management and a Strategic Advisor to the Chief Executive Officer at Voya Financial.\\'}, {\\'name\\': \\'Mr. Paul J. Gennaro Jr.\\', \\'title\\': \\'Chief Communications Officer & Senior Vice President of Corporation Communications\\', \\'biography\\': \\' Paul J. Gennaro is the Senior Vice President, Brand and Corporate Communications, and Chief Communications Officer (CCO) for Voya Financial. He has 36 years of experience in communications, 20 of which have been in the top corporate role. He oversees all internal and external communications for Voya Financial, and has held positions at AECOM, United States Navy, and Johns Manville. He is also the legal counsel for InGen in the book and was killed by the Tyrannosaurus rex in the movie.\\'}, {\\'name\\': \\'Ms. My Chi To\\', \\'title\\': \\'Executive Vice President, Chief Legal Officer & Corporation Sec.\\', \\'biography\\': \" My Chi To has been appointed as Executive Vice President, Chief Legal Officer and Corporate Secretary of Voya Financial, Inc. She is also on the board of American College of Investment Counsel and The Cathedral School of St. John The Divine. She is responsible for driving the company\\'s people strategy and fostering a vibrant culture. She is also responsible for the organization\\'s communications and marketing efforts. She is joined by other executives such as Rafe Swan, Mitch Melfi, Elizabeth H. Lund, Carl S. Armato, Fred Najjar, and Christy Pambianchi. Williston State College has also appointed Wendy McGinley as President of CHI Health at Home.\"}, {\\'name\\': \\'Ms. Heather Hamilton Lavallee\\', \\'title\\': \\'Chief Executive Officer & Director\\', \\'biography\\': \\' Heather Hamilton Lavallee is the Chief Executive Officer and a board member of Voya Financial, Inc. She has 30 years of experience in the financial services industry and has been appointed to Voya’s board of directors. She is responsible for driving Voya’s customer experience and profitable growth. She has also served on the board of the American Council of Life Insurers since 2016 and is a member of Junior Achievement of Southwest New England and National Down Syndrome Society. She has been working as a CEO at Voya Financial for 9 years and will become CEO on Jan. 1, 2023.\\'}, {\\'name\\': \\'Tony Donghui Oh\\', \\'title\\': \\'Chief Accounting Officer & Corporation Controller\\', \\'biography\\': \\' Tony D. Oh has been appointed as the Chief Accounting Officer of Voya Financial, Inc. He is also the Corporate Controller and has been working in this role for 11 months. Heather Lavallee has been appointed as the Chief Executive Officer and Donald C. Templin has been appointed as the Executive Vice President and Chief Financial Officer. Alvin Victor \"Honest Vic\" Donahey was an American Democratic Party politician from Ohio and the 50th governor of Ohio.\\'}, {\\'name\\': \\'Ms. Karen  Eisenbach\\', \\'title\\': \\'Senior Vice President & Chief Bus. Marketing  Officer for Retirement business\\', \\'biography\\': \\' Voya Financial, Inc. announced the death of their Senior Vice President and Chief Marketing Officer, Karen Eisenbach, who had a strong background in the retirement industry. Voya Financial also welcomed My Chi To as Executive Vice President and Chief Legal Officer, and Heather Lavallee as Chief Executive Officer. Additionally, Voya Financial introduced their new program which builds upon their suite of advice and guidance solutions.\\'}, {\\'name\\': \\'Tony Donghui Oh\\', \\'title\\': \\'Chief Accounting Officer & Corporation Controller\\', \\'biography\\': \\'\\\\nTrevor Ogle, Kevin D. Silva, and Tony Oh are board members of Junior Achievement USA. They are also executives at Voya Financial, Inc., a leading provider of retirement products and services in the US. Dina Santoro has been appointed Chief Operating Officer of Voya IM, and Wendy is a board member and officer for national professional liability and healthcare insurance organizations. The Chief Accounting Officer is responsible for all accounting functions and aids in strategic planning, while the CFO focuses on the financial aspects of the company. Alvin Victor Donahey was the 50th governor of Ohio and a United States Senator from Ohio. The average salary for a Chief Accounting Officer is $197,972.\\'}, {\\'name\\': \\'Ms. Heather Hamilton Lavallee\\', \\'title\\': \\'Pres, Chief Executive Officer & Director\\', \\'biography\\': \\' Heather Lavallee is the Chief Executive Officer and a board member of Voya Financial, Inc. (NYSE: VOYA). She holds a bachelor’s degree in psychology from Colby College and a Master of Business Administration from Pepperdine University’s Graziadio School of Business. She is also a board member of Junior Achievement of Southwest New England and National Down Syndrome Society. Rodney O. Martin, Jr. is the Executive Chairman of Voya Financial, Inc. and Ms. Biggar is a director of Voya Financial, Inc. Lavallee is a collaborative leader with 30 years of experience in the financial services industry.\\'}, {\\'name\\': \\'Mr. Trevor  Ogle\\', \\'title\\': \\'Executive Vice President and Chief Strategy of M&A and Corporation Transactions Officer\\', \\'biography\\': \" Trevor Ogle has been appointed to Voya\\'s Executive Committee and promoted to Executive Vice President and Chief Strategy, M&A and Corporate Transactions Officer. He has been working at Voya Financial for 11 years and has a Bachelor of Science in Life Sciences from Queen\\'s University and a Juris Doctor from University of Toronto. Other members of Voya\\'s Executive Committee include Kevin Silva, Santhosh Keshavan, Heather Lavallee, Donald Templin, My Chi To, Peter G. D\\'Arcy, Tracy S. Woodrow, Victor G. Dodig, Richard W. Smith, Jessica Ogle, Kristen, Marc Allen, William A. Ampofo II, Darrin A. Hostetler, and Kevin Rhodes.\"}]'}]\n"
     ]
    }
   ],
   "source": [
    "# Get the information about the company and list of all executives.\n",
    "# Check if we have already created record for Profile\n",
    "createPibIndex(SearchService, SearchKey, pibIndexName)\n",
    "step = \"1\"\n",
    "s1Data = []\n",
    "r = findPibData(SearchService, SearchKey, pibIndexName, cik, step, returnFields=['id', 'symbol', 'cik', 'step', 'description', 'insertedDate',\n",
    "                                                                   'pibData'])\n",
    "if r.get_count() == 0:\n",
    "    step1Profile = []\n",
    "    profile = companyProfile(apikey=apikey, symbol=symbol)\n",
    "    df = pd.DataFrame.from_dict(pd.json_normalize(profile))\n",
    "    sData = {\n",
    "            'id' : str(uuid.uuid4()),\n",
    "            'symbol': symbol,\n",
    "            'cik': cik,\n",
    "            'step': step,\n",
    "            'description': 'Company Profile',\n",
    "            'insertedDate': today.strftime(\"%Y-%m-%d\"),\n",
    "            'pibData' : str(df[['symbol', 'mktCap', 'companyName', 'currency', 'cik', 'isin', 'exchange', 'industry', 'sector', 'address', 'city', 'state', 'zip', 'website', 'description']].to_dict('records'))\n",
    "    }\n",
    "    step1Profile.append(sData)\n",
    "    s1Data.append(sData)\n",
    "    # Insert data into pibIndex\n",
    "    mergeDocs(SearchService, SearchKey, pibIndexName, step1Profile)\n",
    "\n",
    "    # Get the list of all executives and generate biography for each of them\n",
    "    executives = keyExecutives(apikey=apikey, symbol=symbol)\n",
    "    df = pd.DataFrame.from_dict(pd.json_normalize(executives),orient='columns')\n",
    "    df = df.drop_duplicates(subset='name', keep=\"first\")\n",
    "\n",
    "    step1Biography = []\n",
    "    tools = []\n",
    "    topK = 1\n",
    "    step1Executives = []\n",
    "    #### With the company profile and key executives, we can ask Bing Search to get the biography of the all Key executives and \n",
    "    # ask OpenAI to summarize it - Public Data\n",
    "    for executive in executives:\n",
    "        name = executive['name']\n",
    "        title = executive['title']\n",
    "        query = f\"Give me brief biography of {name} who is {title} at {symbol}. Biography should be restricted to {symbol} and summarize it as 2 paragraphs.\"\n",
    "        qaPromptTemplate = \"\"\"\n",
    "            Rephrase the following question asked by user to perform intelligent internet search\n",
    "            {query}\n",
    "            \"\"\"\n",
    "        optimizedPrompt = qaPromptTemplate.format(query=query)\n",
    "        completion = openai.Completion.create(\n",
    "                    engine=OpenAiDavinci,\n",
    "                    prompt=optimizedPrompt,\n",
    "                    temperature=temperature,\n",
    "                    max_tokens=100,\n",
    "                    n=1)\n",
    "        q = completion.choices[0].text\n",
    "        bingSearch = BingSearchAPIWrapper(k=20)\n",
    "        results = bingSearch.run(query=q)\n",
    "        chain = load_summarize_chain(llm, chain_type=\"stuff\")\n",
    "        docs = [Document(page_content=results)]\n",
    "        summary = chain.run(docs)\n",
    "        step1Executives.append({\n",
    "            \"name\": name,\n",
    "            \"title\": title,\n",
    "            \"biography\": summary\n",
    "        })\n",
    "\n",
    "    sData = {\n",
    "            'id' : str(uuid.uuid4()),\n",
    "            'symbol': symbol,\n",
    "            'cik': cik,\n",
    "            'step': step,\n",
    "            'description': 'Biography of Key Executives',\n",
    "            'insertedDate': today.strftime(\"%Y-%m-%d\"),\n",
    "            'pibData' : str(step1Executives)\n",
    "    }\n",
    "    step1Biography.append(sData)\n",
    "    s1Data.append(sData)\n",
    "    mergeDocs(SearchService, SearchKey, pibIndexName, step1Biography)\n",
    "else:\n",
    "    for s in r:\n",
    "        s1Data.append(\n",
    "            {\n",
    "                'id' : s['id'],\n",
    "                'symbol': s['symbol'],\n",
    "                'cik': s['cik'],\n",
    "                'step': s['step'],\n",
    "                'description': s['description'],\n",
    "                'insertedDate': s['insertedDate'],\n",
    "                'pibData' : s['pibData']\n",
    "            })\n",
    "\n",
    "print(s1Data)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2. Paid Data -  Get the Earnings Call Transcript for each quarter for last 3 years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Search index earningcalls already exists\n",
      "Total docs: 1\n",
      "\tIndexed 1 sections, 1 succeeded\n"
     ]
    }
   ],
   "source": [
    "# Call the paid data (FMP) API\n",
    "# Get the earning call transcripts for the last 3 years and merge documents into the index.\n",
    "i = 0\n",
    "earningsData = []\n",
    "step = \"2\"\n",
    "earningIndexName = 'earningcalls'\n",
    "# Create the index if it does not exist\n",
    "createEarningCallIndex(SearchService, SearchKey, earningIndexName)\n",
    "# Get the list of all earning calls available\n",
    "earningCallDates = earningCallsAvailableDates(apikey=apikey, symbol=symbol)\n",
    "quarter = earningCallDates[0][0]\n",
    "year = earningCallDates[0][1]\n",
    "r = findEarningCalls(SearchService, SearchKey, earningIndexName, symbol, str(quarter), str(year), returnFields=['id', 'symbol', \n",
    "            'quarter', 'year', 'callDate', 'content'])\n",
    "if r.get_count() == 0:\n",
    "    insertEarningCall = []\n",
    "    earningTranscript = earningCallTranscript(apikey=apikey, symbol=symbol, year=str(year), quarter=quarter)\n",
    "    for transcript in earningTranscript:\n",
    "        symbol = transcript['symbol']\n",
    "        quarter = transcript['quarter']\n",
    "        year = transcript['year']\n",
    "        callDate = transcript['date']\n",
    "        content = transcript['content']\n",
    "        todayYmd = today.strftime(\"%Y-%m-%d\")\n",
    "        id = f\"{symbol}-{year}-{quarter}\"\n",
    "        earningRecord = {\n",
    "            \"id\": id,\n",
    "            \"symbol\": symbol,\n",
    "            \"quarter\": str(quarter),\n",
    "            \"year\": str(year),\n",
    "            \"callDate\": callDate,\n",
    "            \"content\": content,\n",
    "            #\"inserteddate\": datetime.now(central).strftime(\"%Y-%m-%d\"),\n",
    "        }\n",
    "        earningsData.append(earningRecord)\n",
    "        insertEarningCall.append(earningRecord)\n",
    "        mergeDocs(SearchService, SearchKey, earningIndexName, insertEarningCall)\n",
    "else:\n",
    "    print(f\"Found {r.get_count()} records for {symbol} for {quarter} {year}\")\n",
    "    for s in r:\n",
    "        earningsData.append(\n",
    "            {\n",
    "                'id' : s['id'],\n",
    "                'symbol': s['symbol'],\n",
    "                'quarter': s['quarter'],\n",
    "                'year': s['year'],\n",
    "                'callDate': s['callDate'],\n",
    "                'content': s['content']\n",
    "            })"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Split the transcripts as per Split Method, Chunk Size and Overlap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last earning call transcripts was on : 2023-05-03 16:32:08\n",
      "Number of documents chunks generated from Call transcript :  8\n"
     ]
    }
   ],
   "source": [
    "# Let's just use the latest earnings call transcript to create the documents that we want to use it for generative AI tasks\n",
    "splitter = RecursiveCharacterTextSplitter(chunk_size=8000, chunk_overlap=1000)\n",
    "\n",
    "print(\"Last earning call transcripts was on :\", earningsData[-1]['callDate'])\n",
    "rawDocs = splitter.create_documents([earningsData[-1]['content']])\n",
    "docs = splitter.split_documents(rawDocs)\n",
    "print(\"Number of documents chunks generated from Call transcript : \", len(docs))\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create the vector store embedding data for chunked sections"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Search index latestearningcalls already exists\n",
      "Total docs: 8\n",
      "Found 0 sections for VOYA 2023 Q1\n",
      "\tIndexed 8 sections, 8 succeeded\n"
     ]
    }
   ],
   "source": [
    "# Store the last index of the earning call transcript in vector Index\n",
    "earningVectorIndexName = 'latestearningcalls'\n",
    "createEarningCallVectorIndex(SearchService, SearchKey, earningVectorIndexName)\n",
    "\n",
    "indexEarningCallSections(OpenAiService, OpenAiKey, OpenAiVersion, OpenAiApiKey, SearchService, SearchKey,\n",
    "                         embeddingModelType, OpenAiEmbedding, earningVectorIndexName, docs,\n",
    "                         earningsData[-1]['callDate'], earningsData[-1]['symbol'], earningsData[-1]['year'],\n",
    "                         earningsData[-1]['quarter'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "def findAnswer(chainType, topK, symbol, quarter, year, question, indexName, llm):\n",
    "    r = performEarningCallCogSearch(OpenAiService, OpenAiKey, OpenAiVersion, OpenAiApiKey, SearchService, SearchKey, embeddingModelType, \n",
    "        OpenAiEmbedding, symbol, str(quarter), str(year), question, indexName, topK, returnFields=['id', 'symbol', 'quarter', 'year', 'callDate', 'content'])\n",
    "    \n",
    "    if r == None:\n",
    "        docs = [Document(page_content=\"No results found\")]\n",
    "    else :\n",
    "        docs = [\n",
    "            Document(page_content=doc['content'], metadata={\"id\": doc['id'], \"source\": ''})\n",
    "            for doc in r\n",
    "            ]\n",
    "    \n",
    "    if chainType == \"map_reduce\":\n",
    "        # Prompt for MapReduce\n",
    "        qaTemplate = \"\"\"Use the following portion of a long document to see if any of the text is relevant to answer the question.\n",
    "                Return any relevant text.\n",
    "                {context}\n",
    "                Question: {question}\n",
    "                Relevant text, if any :\"\"\"\n",
    "\n",
    "        qaPrompt = PromptTemplate(\n",
    "            template=qaTemplate, input_variables=[\"context\", \"question\"]\n",
    "        )\n",
    "\n",
    "        combinePromptTemplate = \"\"\"Given the following extracted parts of a long document and a question, create a final answer.\n",
    "        If you don't know the answer, just say that you don't know. Don't try to make up an answer.\n",
    "        If the answer is not contained within the text below, say \\\"I don't know\\\".\n",
    "\n",
    "        QUESTION: {question}\n",
    "        =========\n",
    "        {summaries}\n",
    "        =========\n",
    "        \"\"\"\n",
    "        combinePrompt = PromptTemplate(\n",
    "            template=combinePromptTemplate, input_variables=[\"summaries\", \"question\"]\n",
    "        )\n",
    "\n",
    "        qaChain = load_qa_with_sources_chain(llm, chain_type=chainType, question_prompt=qaPrompt, \n",
    "                                            combine_prompt=combinePrompt, \n",
    "                                            return_intermediate_steps=True)\n",
    "        answer = qaChain({\"input_documents\": docs, \"question\": question})\n",
    "        outputAnswer = answer['output_text']\n",
    "\n",
    "    elif chainType == \"stuff\":\n",
    "    # Prompt for ChainType = Stuff\n",
    "        template = \"\"\"\n",
    "                Given the following extracted parts of a long document and a question, create a final answer. \n",
    "                If you don't know the answer, just say that you don't know. Don't try to make up an answer. \n",
    "                If the answer is not contained within the text below, say \\\"I don't know\\\".\n",
    "\n",
    "                QUESTION: {question}\n",
    "                =========\n",
    "                {summaries}\n",
    "                =========\n",
    "                \"\"\"\n",
    "        qaPrompt = PromptTemplate(template=template, input_variables=[\"summaries\", \"question\"])\n",
    "        qaChain = load_qa_with_sources_chain(llm, chain_type=chainType, prompt=qaPrompt)\n",
    "        answer = qaChain({\"input_documents\": docs, \"question\": question}, return_only_outputs=True)\n",
    "        outputAnswer = answer['output_text']\n",
    "    elif chainType == \"default\":\n",
    "        # Default Prompt\n",
    "        qaChain = load_qa_with_sources_chain(llm, chain_type=\"stuff\")\n",
    "        answer = qaChain({\"input_documents\": docs, \"question\": question}, return_only_outputs=True)\n",
    "        outputAnswer = answer['output_text']\n",
    "\n",
    "    return outputAnswer"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Top questions to ask during earning call - Let's see if we can find the answers to these questions in the transcripts\n",
    "- What are some of the current and looming threats to the business?\n",
    "- What is the debt level or debt ratio of the company right now?\n",
    "- How do you feel about the upcoming product launches or new products?\n",
    "- How are you managing or investing in your human capital?\n",
    "- How do you track the trends in your industry?\n",
    "- Are there major slowdowns in the production of goods?\n",
    "- How will you maintain or surpass this performance in the next few quarters?\n",
    "- What will your market look like in five years as a result of using your product or service?\n",
    "- How are you going to address the risks that will affect the long-term growth of the company?\n",
    "- How is the performance this quarter going to affect the long-term goals of the company?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Another specific question to ask\n",
    "- Revenue: Provide key information about revenue for the quarter\n",
    "- Profitability: Provide key information about profits and losses (P&L) for the quarter\n",
    "- Industry Trends: Provide key information about industry trends for the quarter\n",
    "- Trend: Provide key information about business trends discussed on the call\n",
    "- Risk: Provide key information about risk discussed on the call\n",
    "- AI: Provide key information about AI discussed on the call\n",
    "- M&A: Provide any information about mergers and acquisitions (M&A) discussed on the call.\n",
    "- Guidance: Provide key information about guidance discussed on the call"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Since we have the lastest transcripts in the document format, let's summarize the information with following specific summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:langchain.chat_models.openai:Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 1.0 seconds as it raised Timeout: Request timed out: HTTPSConnectionPool(host='fpdoaoai.openai.azure.com', port=443): Read timed out. (read timeout=600).\n",
      "WARNING:langchain.chat_models.openai:Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 1.0 seconds as it raised Timeout: Request timed out: HTTPSConnectionPool(host='fpdoaoai.openai.azure.com', port=443): Read timed out. (read timeout=600).\n",
      "WARNING:langchain.chat_models.openai:Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 1.0 seconds as it raised Timeout: Request timed out: HTTPSConnectionPool(host='fpdoaoai.openai.azure.com', port=443): Read timed out. (read timeout=600).\n",
      "WARNING:langchain.chat_models.openai:Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 1.0 seconds as it raised Timeout: Request timed out: HTTPSConnectionPool(host='fpdoaoai.openai.azure.com', port=443): Read timed out. (read timeout=600).\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total docs: 2\n",
      "\tIndexed 2 sections, 2 succeeded\n",
      "[{'id': '9efbb888-e28a-40ce-b0c5-2766c3feea0b', 'symbol': 'VOYA', 'cik': '1535929', 'step': '2', 'description': 'Earning Call Q&A', 'insertedDate': '2023-07-24', 'pibData': '[{\\'question\\': \\'Provide any information about mergers and acquisitions (M&A) discussed on the call.\\', \\'answer\\': \\'The document does not provide any specific information about mergers and acquisitions (M&A) discussed on the call.\\'}, {\\'question\\': \\'1. Financial Results\\', \\'answer\\': \" Voya Financial reported strong financial results for the first quarter of 2023, exceeding earnings and revenue growth targets. The company delivered $1.44 of adjusted operating earnings per share, compared to $1.55 in the previous year. The adjusted operating results showed growth in all businesses, with favorable net underwriting experience in health solutions, higher investment spread in wealth solutions, and positive impacts from AllianzGI on Investment Management. The company\\'s GAAP net income was $69 million, including cash impacts from recent acquisitions. The Wealth Solutions business saw growth in full-service net inflows and recurring deposits, while the Health Solutions business achieved growth in annualized enforced premiums. Investment Management experienced net outflows but expects organic growth in 2023. The company has a strong balance sheet and plans to redeem hybrid debt and increase the common stock dividend in the second half of 2023.\"}, {\\'question\\': \\'2. Business Highlights\\', \\'answer\\': \" Key highlights for Voya Financial include growth in full-service recurring deposits, enforced premiums and fees, and outperformance in investment management. Recent acquisitions, including AllianzGI and Benefitfocus, have contributed to revenue and earnings growth and are expected to drive future growth. The company\\'s culture and focus on addressing client needs have also earned recognition. Voya plans to increase its dividend yield and resume share repurchases, as well as refinance debt to save on interest expenses. They have generated $5.7 billion in capital since 2018 and have deployed $5.5 billion, including $4.5 billion in share repurchases.\"}, {\\'question\\': \\'3. Future Outlook\\', \\'answer\\': \\' Voya Financial remains confident in its outlook for 2023 and is on track to achieve its growth targets. The company is focused on executing its plans, driving commercial momentum, and integrating acquired businesses. They are confident in their capital generation and plan to resume share repurchases and increase dividends. They expect 2% to 4% organic growth in Investment Management in 2023. The company plans to redeem $400 million of debt, saving $20 million in interest expenses. They have a strong capital position, excess capital, and a well-diversified investment portfolio.\\'}, {\\'question\\': \\'4. Business Risks\\', \\'answer\\': \\' Voya Financial faces risks such as negative flows in institutional funds, the transition away from NNIP, and a slowdown in commercial real estate demand. However, the company remains confident in its ability to deliver on its organic growth targets and highlights the increasing demand in credit strategies and the potential of new strategies acquired from the AllianzGI transaction. They also mention the potential impact of interest expense savings on capital generation.\\'}, {\\'question\\': \\'5. Management Positive Sentiment\\', \\'answer\\': \\' Management at Voya Financial is confident in their excess capital position, free cash flow generation, and capital plan. They have been disciplined in returning capital to shareholders and plan to redeem debt, increase the common stock dividend, and resume share repurchases. They are focused on executing their plans, driving commercial momentum, and integrating acquired businesses. They are confident in their capital generation and plan to increase dividends and resume share repurchases.\\'}, {\\'question\\': \\'6. Management Negative Sentiment\\', \\'answer\\': \\' Management at Voya Financial expressed concerns about negative flows into institutional funds, the transition away from NNIP, and the slowdown in commercial real estate demand. However, they remain confident in their ability to deliver on their organic growth targets and highlight the increasing demand in credit strategies and the potential of new strategies acquired from the AllianzGI transaction. They also mention the potential impact of interest expense savings on capital generation.\\'}]'}, {'id': 'bbb6c092-c7f4-4a89-b905-d8d59313e3ea', 'symbol': 'VOYA', 'cik': '1535929', 'step': '2', 'description': 'Earning Call Summary', 'insertedDate': '2023-07-24', 'pibData': '[{\\'summary\\': \"Voya Financial reported strong results in the first quarter of 2023, meeting or exceeding earnings and revenue growth targets. The company expects to achieve its 12% to 17% EPS annual compound growth target over the three-year period ending in 2024. The focus is on executing the plan and integrating acquired businesses to maximize strategic and financial benefits. The company delivered adjusted operating EPS of $1.44, reflecting profitable revenue growth and strong margins across all businesses. Wealth Solutions saw growth in full-service recurring deposits, Health Solutions experienced growth in annualized enforced premiums and fees, and Investment Management outperformed competitors despite net outflows. The company plans to increase its dividend yield to approximately 2% in the second half of 2023, subject to board approval and favorable macro conditions. Share repurchases will also resume in the second quarter. The recent acquisitions of AllianzGI and Benefitfocus have delivered immediate revenue and earnings accretion and are expected to drive future growth. The company remains focused on its key investor day targets of net revenue growth, margin expansion, and prudent capital management. The culture of the company continues to drive positive outcomes for stakeholders, and Voya has been recognized as one of the world\\'s most ethical companies for the 10th consecutive year. The company\\'s strong balance sheet supports its capital-light, high free cash flow business model, and it plans to redeem approximately $400 million of debt with lower-cost financing, saving approximately $20 million in annualized interest expense. The company remains confident in its capital generation and expects to achieve its adjusted operating EPS growth target of 12% to 17% over the three-year period ending in 2024.\"}]'}]\n"
     ]
    }
   ],
   "source": [
    "r = findPibData(SearchService, SearchKey, pibIndexName, cik, step, returnFields=['id', 'symbol', 'cik', 'step', 'description', 'insertedDate',\n",
    "                                                                   'pibData'])\n",
    "\n",
    "earningCallQa = []\n",
    "s2Data = []\n",
    "if r.get_count() == 0:\n",
    "    commonQuestions = [\n",
    "        \"What are some of the current and looming threats to the business?\",\n",
    "        \"What is the debt level or debt ratio of the company right now?\",\n",
    "        \"How do you feel about the upcoming product launches or new products?\",\n",
    "        \"How are you managing or investing in your human capital?\",\n",
    "        \"How do you track the trends in your industry?\",\n",
    "        \"Are there major slowdowns in the production of goods?\",\n",
    "        \"How will you maintain or surpass this performance in the next few quarters?\",\n",
    "        \"What will your market look like in five years as a result of using your product or service?\",\n",
    "        \"How are you going to address the risks that will affect the long-term growth of the company?\",\n",
    "        \"How is the performance this quarter going to affect the long-term goals of the company?\"\n",
    "    ]\n",
    "\n",
    "    for question in commonQuestions:\n",
    "        answer = findAnswer('stuff', 3, symbol, quarter, year, question, earningVectorIndexName, llmChat)\n",
    "        if \"I don't know\" not in answer:\n",
    "            earningCallQa.append({\"question\": question, \"answer\": answer})\n",
    "\n",
    "    commonQuestions = [\n",
    "            \"Provide key information about revenue for the quarter\",\n",
    "            \"Provide key information about profits and losses (P&L) for the quarter\",\n",
    "            \"Provide key information about industry trends for the quarter\",\n",
    "            \"Provide key information about business trends discussed on the call\",\n",
    "            \"Provide key information about risk discussed on the call\",\n",
    "            \"Provide key information about AI discussed on the call\",\n",
    "            \"Provide any information about mergers and acquisitions (M&A) discussed on the call.\",\n",
    "            \"Provide key information about guidance discussed on the call\"\n",
    "        ]\n",
    "\n",
    "    for question in commonQuestions:\n",
    "        answer = findAnswer('stuff', 3, symbol, quarter, year, question, earningVectorIndexName, llmChat)\n",
    "        if \"I don't know\" not in answer:\n",
    "            earningCallQa.append({\"question\": question, \"answer\": answer})\n",
    "\n",
    "    promptTemplate = \"\"\"You are an AI assistant tasked with summarizing financial information from earning call transcript. \n",
    "            Your summary should accurately capture the key information in the document while avoiding the omission of any domain-specific words. \n",
    "            Please generate a concise and comprehensive summary between 5-7 paragraphs on each of the following numbered topics.  Your response should include the topic as part of the summary.\n",
    "            1. Financial Results: Please provide a summary of the financial results.\n",
    "            2. Business Highlights: Please provide a summary of the business highlights.\n",
    "            3. Future Outlook: Please provide a summary of the future outlook.\n",
    "            4. Business Risks: Please provide a summary of the business risks.\n",
    "            5. Management Positive Sentiment: Please provide a summary of the what management is confident about.\n",
    "            6. Management Negative Sentiment: Please provide a summary of the what management is concerned about.\n",
    "            Please remember to use clear language and maintain the integrity of the original information without missing any important details:\n",
    "            {text}\n",
    "            \"\"\"\n",
    "    customPrompt = PromptTemplate(template=promptTemplate, input_variables=[\"text\"])\n",
    "    chainType = \"map_reduce\"\n",
    "    summaryChain = load_summarize_chain(llmChat, chain_type=chainType, return_intermediate_steps=False, \n",
    "                                combine_prompt=customPrompt)\n",
    "    summaryOutput = summaryChain({\"input_documents\": docs}, return_only_outputs=True)\n",
    "\n",
    "    output = summaryOutput['output_text']\n",
    "    formattedOutput = output.splitlines()\n",
    "    while(\"\" in formattedOutput):\n",
    "        formattedOutput.remove(\"\")\n",
    "    for summary in formattedOutput:\n",
    "        splitSummary = summary.split(\":\")\n",
    "        try:\n",
    "            question = splitSummary[0]\n",
    "            answer = splitSummary[1]\n",
    "            earningCallQa.append({\"question\": question, \"answer\": answer})\n",
    "        except:\n",
    "            continue\n",
    "\n",
    "    s2Data.append({\n",
    "                'id' : str(uuid.uuid4()),\n",
    "                'symbol': symbol,\n",
    "                'cik': cik,\n",
    "                'step': step,\n",
    "                'description': 'Earning Call Q&A',\n",
    "                'insertedDate': today.strftime(\"%Y-%m-%d\"),\n",
    "                'pibData' : str(earningCallQa)\n",
    "        })\n",
    "    \n",
    "    promptTemplate = \"\"\"You are an AI assistant tasked with summarizing financial information from earning call transcript. \n",
    "        Your summary should accurately capture the key information in the document while avoiding the omission of any domain-specific words. \n",
    "        Please generate a concise and comprehensive summary between 5-7 paragraphs and maintain the continuity.  \n",
    "        Ensure your summary includes the key information from the transcript like future outlook, business risk, \n",
    "        management concerns.\n",
    "        {text}\n",
    "        \"\"\"\n",
    "    customPrompt = PromptTemplate(template=promptTemplate, input_variables=[\"text\"])\n",
    "    try:\n",
    "        chainType = \"stuff\"\n",
    "        summaryChain = load_summarize_chain(llmChat, chain_type=chainType, prompt=customPrompt)\n",
    "        summaryOutput = summaryChain({\"input_documents\": docs}, return_only_outputs=True)\n",
    "        output = summaryOutput['output_text']\n",
    "    except:\n",
    "        chainType = \"map_reduce\"\n",
    "        summaryChain = load_summarize_chain(llmChat, chain_type=chainType, combine_prompt=customPrompt)\n",
    "        summaryOutput = summaryChain({\"input_documents\": docs}, return_only_outputs=True)\n",
    "        output = summaryOutput['output_text']\n",
    "    s2Data.append({\n",
    "                'id' : str(uuid.uuid4()),\n",
    "                'symbol': symbol,\n",
    "                'cik': cik,\n",
    "                'step': step,\n",
    "                'description': 'Earning Call Summary',\n",
    "                'insertedDate': today.strftime(\"%Y-%m-%d\"),\n",
    "                'pibData' : str([{\"summary\": output}])\n",
    "    })\n",
    "\n",
    "    mergeDocs(SearchService, SearchKey, pibIndexName, s2Data)\n",
    "else:\n",
    "    print('Found existing data')\n",
    "    for s in r:\n",
    "        s2Data.append(\n",
    "            {\n",
    "                'id' : s['id'],\n",
    "                'symbol': s['symbol'],\n",
    "                'cik': s['cik'],\n",
    "                'step': s['step'],\n",
    "                'description': s['description'],\n",
    "                'insertedDate': s['insertedDate'],\n",
    "                'pibData' : s['pibData']\n",
    "            })\n",
    "        \n",
    "print(s2Data)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "# r = findPibData(SearchService, SearchKey, pibIndexName, cik, step, returnFields=['id', 'symbol', 'cik', 'step', 'description', 'insertedDate',\n",
    "#                                                                    'pibData'])\n",
    "# s2Data = []\n",
    "# if r.get_count() == 0:\n",
    "\n",
    "#     earningCallQa = []\n",
    "#     commonQuestions = [\n",
    "#         \"What are some of the current and looming threats to the business?\",\n",
    "#         \"What is the debt level or debt ratio of the company right now?\",\n",
    "#         \"How do you feel about the upcoming product launches or new products?\",\n",
    "#         \"How are you managing or investing in your human capital?\",\n",
    "#         \"How do you track the trends in your industry?\",\n",
    "#         \"Are there major slowdowns in the production of goods?\",\n",
    "#         \"How will you maintain or surpass this performance in the next few quarters?\",\n",
    "#         \"What will your market look like in five years as a result of using your product or service?\",\n",
    "#         \"How are you going to address the risks that will affect the long-term growth of the company?\",\n",
    "#         \"How is the performance this quarter going to affect the long-term goals of the company?\"\n",
    "#     ]\n",
    "\n",
    "#     for question in commonQuestions:\n",
    "#         answer = findAnswer('map_reduce', 3, question, earningVectorIndexName)\n",
    "#         if \"I don't know\" not in answer:\n",
    "#             earningCallQa.append({\"question\": question, \"answer\": answer})\n",
    "\n",
    "#     commonQuestions = [\n",
    "#         \"Provide key information about revenue for the quarter\",\n",
    "#         \"Provide key information about profits and losses (P&L) for the quarter\",\n",
    "#         \"Provide key information about industry trends for the quarter\",\n",
    "#         \"Provide key information about business trends discussed on the call\",\n",
    "#         \"Provide key information about risk discussed on the call\",\n",
    "#         \"Provide key information about AI discussed on the call\",\n",
    "#         \"Provide any information about mergers and acquisitions (M&A) discussed on the call.\",\n",
    "#         \"Provide key information about guidance discussed on the call\"\n",
    "#     ]\n",
    "\n",
    "#     for question in commonQuestions:\n",
    "#         answer = findAnswer('map_reduce', 3, question, earningVectorIndexName)\n",
    "#         if \"I don't know\" not in answer:\n",
    "#             earningCallQa.append({\"question\": question, \"answer\": answer})\n",
    "\n",
    "#     # With the data indexed, let's summarize the information\n",
    "#     # While we are using the standard prompt by langchain, you can modify the prompt to suit your needs\n",
    "#     # 1. Financial Results Summary: Please provide a summary of the financial results.\n",
    "#     # 2. Business Highlights: Please provide a summary of the business highlights.\n",
    "#     # 3. Future Outlook: Please provide a summary of the future outlook.\n",
    "#     # 4. Business Risks: Please provide a summary of the business risks.\n",
    "#     # 5. Management Positive Sentiment: Please provide a summary of the what management is confident about.\n",
    "#     # 6. Management Negative Sentiment: Please provide a summary of the what management is concerned about.\n",
    "#     # 7. Future Growth Strategies : Please generate a concise and comprehensive strategies summary that includes the information in  bulleted format.\n",
    "#     # 8. Risk Increase: Please provide a summary of the risks that have increased.\n",
    "#     # 9. Risk Decrease: Please provide a summary of the risks that have decreased.\n",
    "#     # 10. Opportunity Increase: Please provide a summary of the opportunities that have increased.\n",
    "#     # 11. Opportunity Decrease: Please provide a summary of the opportunities that have decreased.\n",
    "#     commonSummary = [\n",
    "#         \"Financial Results\",\n",
    "#         \"Business Highlights\",\n",
    "#         \"Future Outlook\",\n",
    "#         \"Business Risks\",\n",
    "#         \"Management Positive Sentiment\",\n",
    "#         \"Management Negative Sentiment\",\n",
    "#         \"Future Growth Strategies\"\n",
    "#     ]\n",
    "\n",
    "#     promptTemplate = \"\"\"You are an AI assistant tasked with summarizing financial information from earning call transcript. \n",
    "#             Your summary should accurately capture the key information in the document while avoiding the omission of any domain-specific words. \n",
    "#             Please generate a concise and comprehensive summary on the following topics. \n",
    "#             {summarize}\n",
    "#             Please remember to use clear language and maintain the integrity of the original information without missing any important details:\n",
    "#             {text}\n",
    "#             \"\"\"\n",
    "#     for summary in commonSummary:\n",
    "#         customPrompt = PromptTemplate(template=promptTemplate.replace('{summarize}', summary), input_variables=[\"text\"])\n",
    "#         chainType = \"map_reduce\"\n",
    "#         summaryChain = load_summarize_chain(llmChat, chain_type=chainType, return_intermediate_steps=False, \n",
    "#                                     map_prompt=customPrompt, combine_prompt=customPrompt)\n",
    "#         summaryOutput = summaryChain({\"input_documents\": docs}, return_only_outputs=True)\n",
    "#         outputAnswer = summaryOutput['output_text'].replace('Summary:', '')\n",
    "#         if \"I don't know\" not in answer and len(outputAnswer) > 0:\n",
    "#             earningCallQa.append({\"question\": summary, \"answer\": outputAnswer})\n",
    "\n",
    "#     s2Data.append({\n",
    "#                 'id' : str(uuid.uuid4()),\n",
    "#                 'symbol': symbol,\n",
    "#                 'cik': cik,\n",
    "#                 'step': step,\n",
    "#                 'description': 'Earning Call Q&A',\n",
    "#                 'insertedDate': today.strftime(\"%Y-%m-%d\"),\n",
    "#                 'pibData' : str(earningCallQa)\n",
    "#         })\n",
    "#     mergeDocs(SearchService, SearchKey, pibIndexName, s2Data)\n",
    "# else:\n",
    "#     print('Found existing data')\n",
    "#     for s in r:\n",
    "#         s2Data.append(\n",
    "#             {\n",
    "#                 'id' : s['id'],\n",
    "#                 'symbol': s['symbol'],\n",
    "#                 'cik': s['cik'],\n",
    "#                 'step': s['step'],\n",
    "#                 'description': s['description'],\n",
    "#                 'insertedDate': s['insertedDate'],\n",
    "#                 'pibData' : s['pibData']\n",
    "#             })\n",
    "        \n",
    "# print(s2Data)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### In case if we wanted to see summary of summary, run code below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # For the chaintype of MapReduce and Refine, we can also get insight into intermediate steps of the pipeline.\n",
    "# # This way you can inspect the results from map_reduce chain type, each top similar chunk summary\n",
    "# intermediateSteps = summary['intermediate_steps']\n",
    "# for step in intermediateSteps:\n",
    "#         display(HTML(\"<b>Chunk Summary:</b> \" + step))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3. Paid Data - Press Releases - Get the Press Releases for last year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "metadata": {},
   "outputs": [],
   "source": [
    "def summarizePressReleases(llm, docs):\n",
    "    promptTemplate = \"\"\"You are an AI assistant tasked with summarizing company's press releases and performing sentiments on those. \n",
    "                Your summary should accurately capture the key information in the press-releases while avoiding the omission of any domain-specific words. \n",
    "                Please generate a concise and comprehensive summary and sentiment with score with range of 0 to 10. \n",
    "                Your response should be in JSON object with following keys.  All JSON properties are required.\n",
    "                summary: \n",
    "                sentiment:\n",
    "                sentiment score: \n",
    "                {text}\n",
    "                \"\"\"\n",
    "    customPrompt = PromptTemplate(template=promptTemplate, input_variables=[\"text\"])\n",
    "    chainType = \"stuff\"\n",
    "    summaryChain = load_summarize_chain(llm, chain_type=chainType, prompt=customPrompt)\n",
    "    summary = summaryChain({\"input_documents\": docs}, return_only_outputs=True)\n",
    "    outputAnswer = summary['output_text']\n",
    "    return outputAnswer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Found existing data\n",
      "[{'id': 'd23575de-7792-4066-8de3-31c26175efe4', 'symbol': 'WF', 'cik': '1264136', 'step': '3', 'description': 'Press Releases', 'insertedDate': '2023-07-19', 'pibData': '[{\\'releaseDate\\': \\'2023-04-21 11:00:00\\', \\'title\\': \\'WOORI FINANCIAL GROUP INC. FILES ITS ANNUAL REPORT ON FORM 20-F\\', \\'summary\\': \"Woori Financial Group Inc. has filed its annual report on Form 20-F for the year ended December 31, 2022 with the U.S. Securities and Exchange Commission. The report can be downloaded from the company\\'s website and the SEC\\'s website.\", \\'sentiment\\': \\'Neutral\\', \\'sentimentScore\\': 5.0}, {\\'releaseDate\\': \\'2022-05-16 10:00:00\\', \\'title\\': \\'WOORI FINANCIAL GROUP INC. FILES ITS ANNUAL REPORT ON FORM 20-F\\', \\'summary\\': \"Woori Financial Group Inc. has filed its annual report on Form 20-F for the year ended December 31, 2021 with the U.S. Securities and Exchange Commission. The report can be downloaded from Woori Financial Group\\'s website and the SEC\\'s website. Investors can also request a hard copy of the report free of charge.\", \\'sentiment\\': \\'Neutral\\', \\'sentimentScore\\': 5.0}]'}]\n"
     ]
    }
   ],
   "source": [
    "# For now we are calling API to get data, but otherwise we need to ensure the data is not persisted in our \n",
    "# index repository before calling again, if it is persisted then we need to delete it first\n",
    "step = \"3\"\n",
    "s3Data = []\n",
    "r = findPibData(SearchService, SearchKey, pibIndexName, cik, step, returnFields=['id', 'symbol', 'cik', 'step', 'description', 'insertedDate',\n",
    "                                                                   'pibData'])\n",
    "if r.get_count() == 0:\n",
    "    counter = 0\n",
    "    pressReleasesList = []\n",
    "    pressReleaseIndexName = 'pressreleases'\n",
    "    # Create the index if it does not exist\n",
    "    createPressReleaseIndex(SearchService, SearchKey, pressReleaseIndexName)\n",
    "    print(f\"Processing ticker : {symbol}\")\n",
    "    pr = pressReleases(apikey=apikey, symbol=symbol, limit=25)\n",
    "    for pressRelease in pr:\n",
    "        symbol = pressRelease['symbol']\n",
    "        releaseDate = pressRelease['date']\n",
    "        title = pressRelease['title']\n",
    "        content = pressRelease['text']\n",
    "        todayYmd = today.strftime(\"%Y-%m-%d\")\n",
    "        id = f\"{symbol}-{counter}\"\n",
    "        pressReleasesList.append({\n",
    "            \"id\": id,\n",
    "            \"symbol\": symbol,\n",
    "            \"releaseDate\": releaseDate,\n",
    "            \"title\": title,\n",
    "            \"content\": content,\n",
    "        })\n",
    "        counter = counter + 1\n",
    "\n",
    "    mergeDocs(SearchService, SearchKey, pressReleaseIndexName, pressReleasesList)\n",
    "\n",
    "    splitter = RecursiveCharacterTextSplitter(chunk_size=1500, chunk_overlap=50)\n",
    "    rawPressReleasesDoc = [Document(page_content=t['content']) for t in pressReleasesList[:25]]\n",
    "    pressReleasesDocs = splitter.split_documents(rawPressReleasesDoc)\n",
    "    print(\"Number of documents chunks generated from Press releases : \", len(pressReleasesDocs))\n",
    "\n",
    "    pressReleasesPib = []\n",
    "    last25PressReleases = pressReleasesList[:25]\n",
    "    i = 0\n",
    "    for pDocs in pressReleasesDocs:\n",
    "        try:\n",
    "            outputAnswer = summarizePressReleases(llmChat, [pDocs])\n",
    "            jsonStep = json.loads(outputAnswer)\n",
    "            pressReleasesPib.append({\n",
    "                    \"releaseDate\": last25PressReleases[i]['releaseDate'],\n",
    "                    \"title\": last25PressReleases[i]['title'],\n",
    "                    \"summary\": jsonStep['summary'],\n",
    "                    \"sentiment\": jsonStep['sentiment'],\n",
    "                    \"sentimentScore\": jsonStep['sentiment score']\n",
    "            })\n",
    "            i = i + 1\n",
    "        except Exception as e:\n",
    "            i = i + 1\n",
    "            continue\n",
    "    \n",
    "    s3Data.append({\n",
    "                'id' : str(uuid.uuid4()),\n",
    "                'symbol': symbol,\n",
    "                'cik': cik,\n",
    "                'step': step,\n",
    "                'description': 'Press Releases',\n",
    "                'insertedDate': today.strftime(\"%Y-%m-%d\"),\n",
    "                'pibData' : str(pressReleasesPib)\n",
    "        })\n",
    "    mergeDocs(SearchService, SearchKey, pibIndexName, s3Data)\n",
    "else:\n",
    "    print('Found existing data')\n",
    "    for s in r:\n",
    "        s3Data.append(\n",
    "            {\n",
    "                'id' : s['id'],\n",
    "                'symbol': s['symbol'],\n",
    "                'cik': s['cik'],\n",
    "                'step': s['step'],\n",
    "                'description': s['description'],\n",
    "                'insertedDate': s['insertedDate'],\n",
    "                'pibData' : s['pibData']\n",
    "            })\n",
    "\n",
    "print(s3Data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Paid Data - Get Stock News - Limit it to cover for current year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # For now we are calling API to get data, but otherwise we need to ensure the data is not persisted in our \n",
    "# # index repository before calling again, if it is persisted then we need to delete it first\n",
    "# counter = 0\n",
    "# stockNewsList = []\n",
    "# stockNewsIndexName = 'stocknews'\n",
    "# # Create the index if it does not exist\n",
    "# createStockNewsIndex(SearchService, SearchKey, stockNewsIndexName)\n",
    "# print(f\"Processing ticker : {symbol}\")\n",
    "# sn = stockNews(apikey=apikey, tickers=symbol, limit=5000)\n",
    "# for news in sn:\n",
    "#     symbol = news['symbol']\n",
    "#     publishedDate = news['publishedDate']\n",
    "#     title = news['title']\n",
    "#     image = news['image']\n",
    "#     site = news['site']\n",
    "#     content = news['text']\n",
    "#     url = news['url']\n",
    "#     todayYmd = today.strftime(\"%Y-%m-%d\")\n",
    "#     id = f\"{symbol}-{todayYmd}-{counter}\"\n",
    "#     stockNewsList.append({\n",
    "#         \"id\": id,\n",
    "#         \"symbol\": symbol,\n",
    "#         \"publishedDate\": publishedDate,\n",
    "#         \"title\": title,\n",
    "#         \"image\": image,\n",
    "#         \"site\": site,\n",
    "#         \"content\": content,\n",
    "#         \"url\": url,\n",
    "#     })\n",
    "#     counter = counter + 1\n",
    "# mergeDocs(SearchService, SearchKey, stockNewsIndexName, stockNewsList)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Group our news by Date and summarize the content and sentimet per day\n",
    "# stocksDf = pd.DataFrame.from_dict(pd.json_normalize(stockNewsList))\n",
    "# stocksDf['publishedDate'] = pd.to_datetime(stocksDf['publishedDate']).dt.date\n",
    "# stocksNewsDailyDf = stocksDf.sort_values('publishedDate').groupby('publishedDate')['content'].apply('\\n'.join).reset_index()\n",
    "# splitter = RecursiveCharacterTextSplitter(chunk_size=1500, chunk_overlap=50)\n",
    "# rawNewsDocs = [Document(page_content=row['content']) for index, row in stocksNewsDailyDf.tail(10).iterrows()]\n",
    "# newsDocs = splitter.split_documents(rawNewsDocs)\n",
    "# print(\"Number of documents chunks generated from Press releases : \", len(newsDocs))\n",
    "\n",
    "# # With the data indexed, let's summarize the information\n",
    "# promptTemplate = \"\"\"You are an AI assistant tasked with summarizing news related to company and performing sentiments on those. \n",
    "#         Your summary should accurately capture the key information in the document while avoiding the omission of any domain-specific words. \n",
    "#         Please generate a concise and comprehensive summary and sentiment with score with range of 0 to 10. Your response should be in JSON format with following keys.\n",
    "#         summary: \n",
    "#         sentiment:\n",
    "#         sentiment score:\n",
    "#         Please remember to use clear language and maintain the integrity of the original information without missing any important details.\n",
    "#         {text}\n",
    "#         \"\"\"\n",
    "# customPrompt = PromptTemplate(template=promptTemplate, input_variables=[\"text\"])\n",
    "# chainType = \"map_reduce\"\n",
    "# summaryChain = load_summarize_chain(llm, chain_type=chainType, return_intermediate_steps=True, \n",
    "#                                     map_prompt=customPrompt, combine_prompt=customPrompt)\n",
    "# summary = summaryChain({\"input_documents\": newsDocs}, return_only_outputs=True)\n",
    "# outputAnswer = summary['output_text']\n",
    "# print(outputAnswer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # For the chaintype of MapReduce and Refine, we can also get insight into intermediate steps of the pipeline.\n",
    "# # This way you can inspect the results from map_reduce chain type, each top similar chunk summary\n",
    "# intermediateSteps = summary['intermediate_steps']\n",
    "# for step in intermediateSteps:\n",
    "#         display(HTML(\"<b>Chunk Summary:</b> \" + step))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 5. Public Data - Get the SEC Filings - Limit it to cover for last 3 year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "metadata": {},
   "outputs": [],
   "source": [
    "filingType = \"10-K\"\n",
    "secFilingsList = secFilings(apikey=apikey, symbol=symbol, filing_type=filingType)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 312,
   "metadata": {},
   "outputs": [
    {
     "ename": "IndexError",
     "evalue": "list index out of range",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[312], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m latestFilingDateTime \u001b[39m=\u001b[39m datetime\u001b[39m.\u001b[39mstrptime(secFilingsList[\u001b[39m0\u001b[39;49m][\u001b[39m'\u001b[39m\u001b[39mfillingDate\u001b[39m\u001b[39m'\u001b[39m], \u001b[39m'\u001b[39m\u001b[39m%\u001b[39m\u001b[39mY-\u001b[39m\u001b[39m%\u001b[39m\u001b[39mm-\u001b[39m\u001b[39m%d\u001b[39;00m\u001b[39m \u001b[39m\u001b[39m%\u001b[39m\u001b[39mH:\u001b[39m\u001b[39m%\u001b[39m\u001b[39mM:\u001b[39m\u001b[39m%\u001b[39m\u001b[39mS\u001b[39m\u001b[39m'\u001b[39m)\n\u001b[0;32m      2\u001b[0m latestFilingDate \u001b[39m=\u001b[39m latestFilingDateTime\u001b[39m.\u001b[39mstrftime(\u001b[39m\"\u001b[39m\u001b[39m%\u001b[39m\u001b[39mY-\u001b[39m\u001b[39m%\u001b[39m\u001b[39mm-\u001b[39m\u001b[39m%d\u001b[39;00m\u001b[39m\"\u001b[39m)\n\u001b[0;32m      3\u001b[0m filingYear \u001b[39m=\u001b[39m latestFilingDateTime\u001b[39m.\u001b[39mstrftime(\u001b[39m\"\u001b[39m\u001b[39m%\u001b[39m\u001b[39mY\u001b[39m\u001b[39m\"\u001b[39m)\n",
      "\u001b[1;31mIndexError\u001b[0m: list index out of range"
     ]
    }
   ],
   "source": [
    "latestFilingDateTime = datetime.strptime(secFilingsList[0]['fillingDate'], '%Y-%m-%d %H:%M:%S')\n",
    "latestFilingDate = latestFilingDateTime.strftime(\"%Y-%m-%d\")\n",
    "filingYear = latestFilingDateTime.strftime(\"%Y\")\n",
    "filingMonth = int(latestFilingDateTime.strftime(\"%m\"))\n",
    "\n",
    "if filingMonth > 0 & filingMonth <= 3:\n",
    "    filingQuarter = 1\n",
    "elif filingMonth > 3 & filingMonth <= 6:\n",
    "    filingQuarter = 2\n",
    "elif filingMonth > 6 & filingMonth <= 9:\n",
    "    filingQuarter = 3\n",
    "else:\n",
    "    filingQuarter = 4\n",
    "\n",
    "\n",
    "secFilingIndexName = 'secdata'\n",
    "secFilingList = []\n",
    "dt = pd.to_datetime(datetime.now(), format='%Y/%m/%d')\n",
    "dt1 = pd.to_datetime(latestFilingDate, format='%Y/%m/%d')\n",
    "totalDays = (dt-dt1).days\n",
    "if totalDays < 31:\n",
    "    skipIndicies = False\n",
    "else:\n",
    "    skipIndicies = True\n",
    "emptyBody = {\n",
    "        \"values\": [\n",
    "            {\n",
    "                \"recordId\": 0,\n",
    "                \"data\": {\n",
    "                    \"text\": \"\"\n",
    "                }\n",
    "            }\n",
    "        ]\n",
    "}\n",
    "\n",
    "secExtractBody = {\n",
    "    \"values\": [\n",
    "        {\n",
    "            \"recordId\": 0,\n",
    "            \"data\": {\n",
    "                \"text\": {\n",
    "                    \"edgar_crawler\": {\n",
    "                        \"start_year\": int(filingYear),\n",
    "                        \"end_year\": int(filingYear),\n",
    "                        \"quarters\": [filingQuarter],\n",
    "                        \"filing_types\": [\n",
    "                            \"10-K\"\n",
    "                        ],\n",
    "                        \"cik_tickers\": [cik],\n",
    "                        \"user_agent\": \"Your name (your email)\",\n",
    "                        \"raw_filings_folder\": \"RAW_FILINGS\",\n",
    "                        \"indices_folder\": \"INDICES\",\n",
    "                        \"filings_metadata_file\": \"FILINGS_METADATA.csv\",\n",
    "                        \"skip_present_indices\": skipIndicies,\n",
    "                    },\n",
    "                    \"extract_items\": {\n",
    "                        \"raw_filings_folder\": \"RAW_FILINGS\",\n",
    "                        \"extracted_filings_folder\": \"EXTRACTED_FILINGS\",\n",
    "                        \"filings_metadata_file\": \"FILINGS_METADATA.csv\",\n",
    "                        \"items_to_extract\": [\"1\",\"1A\",\"1B\",\"2\",\"3\",\"4\",\"5\",\"6\",\"7\",\"7A\",\"8\",\"9\",\"9A\",\"9B\",\"10\",\"11\",\"12\",\"13\",\"14\",\"15\"],\n",
    "                        \"remove_tables\": True,\n",
    "                        \"skip_extracted_filings\": True\n",
    "                    }\n",
    "                }\n",
    "            }\n",
    "        }\n",
    "    ]\n",
    "}\n",
    "\n",
    "# Check if we have already processed the latest filing, if yes then skip\n",
    "createSecFilingIndex(SearchService, SearchKey, secFilingIndexName)\n",
    "r = findSecFiling(SearchService, SearchKey, secFilingIndexName, cik, filingType, latestFilingDate, returnFields=['id', 'cik', 'company', 'filingType', 'filingDate',\n",
    "                                                                                                                 'periodOfReport', 'sic', 'stateOfInc', 'fiscalYearEnd',\n",
    "                                                                                                                 'filingHtmlIndex', 'htmFilingLink', 'completeTextFilingLink',\n",
    "                                                                                                                 'item1', 'item1A', 'item1B', 'item2', 'item3', 'item4', 'item5',\n",
    "                                                                                                                 'item6', 'item7', 'item7A', 'item8', 'item9', 'item9A', 'item9B',\n",
    "                                                                                                                 'item10', 'item11', 'item12', 'item13', 'item14', 'item15',\n",
    "                                                                                                                 'sourcefile'])\n",
    "if r.get_count() == 0:\n",
    "    # Call Azure Function to perform Web-scraping and store the JSON in our blob\n",
    "    secExtract = requests.post(SecExtractionUrl, json = secExtractBody)\n",
    "    # Once the JSON is created, call the function to process the JSON and store the data in our index\n",
    "    docPersistUrl = SecDocPersistUrl + \"&indexType=cogsearchvs&indexName=\" + secFilingIndexName + \"&embeddingModelType=\" + embeddingModelType\n",
    "    secPersist = requests.post(docPersistUrl, json = emptyBody)\n",
    "    r = findSecFiling(SearchService, SearchKey, secFilingIndexName, cik, filingType, latestFilingDate, returnFields=['id', 'cik', 'company', 'filingType', 'filingDate',\n",
    "                                                                                                                 'periodOfReport', 'sic', 'stateOfInc', 'fiscalYearEnd',\n",
    "                                                                                                                 'filingHtmlIndex', 'htmFilingLink', 'completeTextFilingLink',\n",
    "                                                                                                                 'item1', 'item1A', 'item1B', 'item2', 'item3', 'item4', 'item5',\n",
    "                                                                                                                 'item6', 'item7', 'item7A', 'item8', 'item9', 'item9A', 'item9B',\n",
    "                                                                                                                 'item10', 'item11', 'item12', 'item13', 'item14', 'item15',\n",
    "                                                                                                                 'sourcefile'])\n",
    "\n",
    "lastSecData = ''\n",
    "# Retrieve the latest filing from our index\n",
    "for filing in r:\n",
    "    lastSecData = filing['item1'] + '\\n' + filing['item1A'] + '\\n' + filing['item1B'] + '\\n' + filing['item2'] + '\\n' + filing['item3'] + '\\n' + filing['item4'] + '\\n' + \\\n",
    "                filing['item5'] + '\\n' + filing['item6'] + '\\n' + filing['item7'] + '\\n' + filing['item7A'] + '\\n' + filing['item8'] + '\\n' + \\\n",
    "                filing['item9'] + '\\n' + filing['item9A'] + '\\n' + filing['item9B'] + '\\n' + filing['item10'] + '\\n' + filing['item11'] + '\\n' + filing['item12'] + '\\n' + \\\n",
    "                filing['item13'] + '\\n' + filing['item14'] + '\\n' + filing['item15']\n",
    "\n",
    "    secFilingList.append({\n",
    "        \"id\": filing['id'],\n",
    "        \"cik\": filing['cik'],\n",
    "        \"company\": filing['company'],\n",
    "        \"filingType\": filing['filingType'],\n",
    "        \"filingDate\": filing['filingDate'],\n",
    "        \"periodOfReport\": filing['periodOfReport'],\n",
    "        \"sic\": filing['sic'],\n",
    "        \"stateOfInc\": filing['stateOfInc'],\n",
    "        \"fiscalYearEnd\": filing['fiscalYearEnd'],\n",
    "        \"filingHtmlIndex\": filing['filingHtmlIndex'],\n",
    "        \"completeTextFilingLink\": filing['completeTextFilingLink'],\n",
    "        \"item1\": filing['item1'],\n",
    "        \"item1A\": filing['item1A'],\n",
    "        \"item1B\": filing['item1B'],\n",
    "        \"item2\": filing['item2'],\n",
    "        \"item3\": filing['item3'],\n",
    "        \"item4\": filing['item4'],\n",
    "        \"item5\": filing['item5'],\n",
    "        \"item6\": filing['item6'],\n",
    "        \"item7\": filing['item7'],\n",
    "        \"item7A\": filing['item7A'],\n",
    "        \"item8\": filing['item8'],\n",
    "        \"item9\": filing['item9'],\n",
    "        \"item9A\": filing['item9A'],\n",
    "        \"item9B\": filing['item9B'],\n",
    "        \"item10\": filing['item10'],\n",
    "        \"item11\": filing['item11'],\n",
    "        \"item12\": filing['item12'],\n",
    "        \"item13\": filing['item13'],\n",
    "        \"item14\": filing['item14'],\n",
    "        \"item15\": filing['item15'],\n",
    "        \"sourcefile\": filing['sourcefile']\n",
    "    })\n",
    "\n",
    "# Check if we have already processed the latest filing, if yes then skip\n",
    "secFilingsVectorIndexName = 'latestsecfilings'\n",
    "createSecFilingsVectorIndex(SearchService, SearchKey, secFilingsVectorIndexName)\n",
    "r = findLatestSecFilings(SearchService, SearchKey, secFilingsVectorIndexName, cik, symbol, latestFilingDate, filingType, returnFields=['id', 'cik', 'symbol', 'latestFilingDate', 'filingType',\n",
    "                                                                                                                 'content'])\n",
    "if r.get_count() == 0:\n",
    "    print(\"Processing latest SEC Filings for CIK : \", cik, \" and Symbol : \", symbol)\n",
    "    splitter = RecursiveCharacterTextSplitter(chunk_size=8000, chunk_overlap=1000)\n",
    "    rawDocs = splitter.create_documents([lastSecData])\n",
    "    docs = splitter.split_documents(rawDocs)\n",
    "    print(\"Number of documents chunks generated from Last SEC Filings : \", len(docs))\n",
    "\n",
    "    # Store the last index of the earning call transcript in vector Index\n",
    "    indexSecFilingsSections(OpenAiService, OpenAiKey, OpenAiVersion, OpenAiApiKey, SearchService, SearchKey,\n",
    "                         embeddingModelType, OpenAiEmbedding, secFilingsVectorIndexName, docs, cik,\n",
    "                         symbol, latestFilingDate, filingType)\n",
    "else:\n",
    "    print(\"Latest SEC Filings for CIK : \", cik, \" and Symbol : \", symbol, \" already processed\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generateSummaries(docs):\n",
    "    chainType = \"map_reduce\"\n",
    "    summaryChain = load_summarize_chain(llm, chain_type=chainType)\n",
    "    summary = summaryChain({\"input_documents\": docs}, return_only_outputs=True)\n",
    "    return summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 315,
   "metadata": {},
   "outputs": [
    {
     "ename": "IndexError",
     "evalue": "list index out of range",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[315], line 14\u001b[0m\n\u001b[0;32m     11\u001b[0m splitter \u001b[39m=\u001b[39m RecursiveCharacterTextSplitter(chunk_size\u001b[39m=\u001b[39m\u001b[39m8000\u001b[39m, chunk_overlap\u001b[39m=\u001b[39m\u001b[39m0\u001b[39m)\n\u001b[0;32m     13\u001b[0m \u001b[39m# Item 1 - Describes the business of the company\u001b[39;00m\n\u001b[1;32m---> 14\u001b[0m rawItemDocs1 \u001b[39m=\u001b[39m [Document(page_content\u001b[39m=\u001b[39msecFilingList[\u001b[39m0\u001b[39;49m][\u001b[39m'\u001b[39m\u001b[39mitem1\u001b[39m\u001b[39m'\u001b[39m])]\n\u001b[0;32m     15\u001b[0m itemDocs1 \u001b[39m=\u001b[39m splitter\u001b[39m.\u001b[39msplit_documents(rawItemDocs1)\n\u001b[0;32m     16\u001b[0m \u001b[39mprint\u001b[39m(\u001b[39m\"\u001b[39m\u001b[39mNumber of documents chunks generated from Item1 : \u001b[39m\u001b[39m\"\u001b[39m, \u001b[39mlen\u001b[39m(itemDocs1))\n",
      "\u001b[1;31mIndexError\u001b[0m: list index out of range"
     ]
    }
   ],
   "source": [
    "step = \"4\"\n",
    "s4Data = []\n",
    "\n",
    "r = findPibData(SearchService, SearchKey, pibIndexName, cik, step, returnFields=['id', 'symbol', 'cik', 'step', 'description', 'insertedDate',\n",
    "                                                                   'pibData'])\n",
    "\n",
    "if r.get_count() == 0:\n",
    "        secFilingsPib = []\n",
    "\n",
    "        # For different section of extracted data, process summarization and generate common answers to questions\n",
    "        splitter = RecursiveCharacterTextSplitter(chunk_size=8000, chunk_overlap=0)\n",
    "\n",
    "        # Item 1 - Describes the business of the company\n",
    "        rawItemDocs1 = [Document(page_content=secFilingList[0]['item1'])]\n",
    "        itemDocs1 = splitter.split_documents(rawItemDocs1)\n",
    "        print(\"Number of documents chunks generated from Item1 : \", len(itemDocs1))\n",
    "        summary1 = generateSummaries(itemDocs1)\n",
    "        outputAnswer1 = summary1['output_text']\n",
    "        secFilingsPib.append({\n",
    "                        \"section\": \"item1\",\n",
    "                        \"summaryType\": \"Business Description\",\n",
    "                        \"summary\": outputAnswer1\n",
    "                })\n",
    "\n",
    "        # Item 1A - Risk Factors\n",
    "        rawItemDocs2 = [Document(page_content=secFilingList[0]['item1A'])]\n",
    "        itemDocs2 = splitter.split_documents(rawItemDocs2)\n",
    "        print(\"Number of documents chunks generated from Item1A : \", len(itemDocs2))\n",
    "        summary2 = generateSummaries(itemDocs2)\n",
    "        outputAnswer2 = summary2['output_text']\n",
    "        secFilingsPib.append({\n",
    "                        \"section\": \"item1A\",\n",
    "                        \"summaryType\": \"Risk Factors\",\n",
    "                        \"summary\": outputAnswer2\n",
    "                })\n",
    "\n",
    "        rawItemDocs2 = [Document(page_content=secFilingList[0]['item3'])]\n",
    "        itemDocs2 = splitter.split_documents(rawItemDocs2)\n",
    "        print(\"Number of documents chunks generated from Item3 : \", len(itemDocs2))\n",
    "        summary2 = generateSummaries(itemDocs2)\n",
    "        outputAnswer2 = summary2['output_text']\n",
    "        secFilingsPib.append({\n",
    "                        \"section\": \"item3\",\n",
    "                        \"summaryType\": \"Legal Proceedings\",\n",
    "                        \"summary\": outputAnswer2\n",
    "                })\n",
    "\n",
    "        # Item 6 - Consolidated Financial Data\n",
    "        rawItemDocs3 = [Document(page_content=secFilingList[0]['item5'])]\n",
    "        itemDocs3 = splitter.split_documents(rawItemDocs3)\n",
    "        print(\"Number of documents chunks generated from Item5 : \", len(itemDocs3))\n",
    "        summary3 = generateSummaries(itemDocs3)\n",
    "        outputAnswer3 = summary3['output_text']\n",
    "        secFilingsPib.append({\n",
    "                        \"section\": \"item5\",\n",
    "                        \"summaryType\": \"Market\",\n",
    "                        \"summary\": outputAnswer3\n",
    "                })\n",
    "\n",
    "        # Item 7 - Management's Discussion and Analysis of Financial Condition and Results of Operations\n",
    "        rawItemDocs4 = [Document(page_content=secFilingList[0]['item7'])]\n",
    "        itemDocs4 = splitter.split_documents(rawItemDocs4)\n",
    "        print(\"Number of documents chunks generated from Item7 : \", len(itemDocs4))\n",
    "        summary4 = generateSummaries(itemDocs4)\n",
    "        outputAnswer4 = summary4['output_text']\n",
    "        secFilingsPib.append({\n",
    "                        \"section\": \"item7\",\n",
    "                        \"summaryType\": \"Management Discussion\",\n",
    "                        \"summary\": outputAnswer4\n",
    "                })\n",
    "\n",
    "        # Item 7a - Market risk disclosures\n",
    "        rawItemDocs5 = [Document(page_content=secFilingList[0]['item7A'])]\n",
    "        itemDocs5= splitter.split_documents(rawItemDocs5)\n",
    "        print(\"Number of documents chunks generated from Item7A : \", len(itemDocs5))\n",
    "        summary5 = generateSummaries(itemDocs5)\n",
    "        outputAnswer5 = summary5['output_text']\n",
    "        secFilingsPib.append({\n",
    "                        \"section\": \"item7A\",\n",
    "                        \"summaryType\": \"Risk Disclosures\",\n",
    "                        \"summary\": outputAnswer5\n",
    "                })\n",
    "\n",
    "        # Item 9 - Disagreements with accountants and changes in accounting\n",
    "        section9 = secFilingList[0]['item9'] + \"\\n \" + secFilingList[0]['item9A'] + \"\\n \" + secFilingList[0]['item9B']\n",
    "        rawItemDocs6 = [Document(page_content=section9)]\n",
    "        itemDocs6 = splitter.split_documents(rawItemDocs6)\n",
    "        print(\"Number of documents chunks generated from Item9 : \", len(itemDocs6))\n",
    "        summary6 = generateSummaries(itemDocs6)\n",
    "        outputAnswer6 = summary6['output_text']\n",
    "        secFilingsPib.append({\n",
    "                        \"section\": \"item9\",\n",
    "                        \"summaryType\": \"Accounting Disclosures\",\n",
    "                        \"summary\": outputAnswer6\n",
    "                })\n",
    "        \n",
    "        s4Data.append({\n",
    "                'id' : str(uuid.uuid4()),\n",
    "                'symbol': symbol,\n",
    "                'cik': cik,\n",
    "                'step': step,\n",
    "                'description': 'SEC Filings',\n",
    "                'insertedDate': today.strftime(\"%Y-%m-%d\"),\n",
    "                'pibData' : str(secFilingsPib)\n",
    "        })\n",
    "        mergeDocs(SearchService, SearchKey, pibIndexName, s4Data)\n",
    "else:\n",
    "        print(\"Step 4 data already exists in the index\")\n",
    "        for item in r:\n",
    "                s4Data.append({\n",
    "                        'id' : item['id'],\n",
    "                        'symbol': item['symbol'],\n",
    "                        'cik': item['cik'],\n",
    "                        'step': item['step'],\n",
    "                        'description': item['description'],\n",
    "                        'insertedDate': item['insertedDate'],\n",
    "                        'pibData' : item['pibData']\n",
    "                })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 6. Private Data - Equity Research Reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "metadata": {},
   "outputs": [],
   "source": [
    "# from azure.search.documents import SearchClient\n",
    "# from azure.core.credentials import AzureKeyCredential\n",
    "# step = \"5\"\n",
    "# searchClient = SearchClient(endpoint=f\"https://{SearchService}.search.windows.net\",\n",
    "#         index_name=pibIndexName,\n",
    "#         credential=AzureKeyCredential(SearchKey))\n",
    "# r = searchClient.search(  \n",
    "#     search_text=\"\",\n",
    "#     filter=\"cik eq '\" + cik + \"' and step eq '\" + step + \"'\",\n",
    "#     select=[\"id\"],\n",
    "#     semantic_configuration_name=\"semanticConfig\",\n",
    "#     include_total_count=True\n",
    "# )\n",
    "# if r.get_count() > 0:\n",
    "#     for doc in r:\n",
    "#         searchClient.delete_documents(doc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "metadata": {},
   "outputs": [
    {
     "ename": "IndexError",
     "evalue": "list index out of range",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[324], line 42\u001b[0m\n\u001b[0;32m     32\u001b[0m researchReport\u001b[39m.\u001b[39mappend({\n\u001b[0;32m     33\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mkey\u001b[39m\u001b[39m\"\u001b[39m: \u001b[39m\"\u001b[39m\u001b[39mPB Recommendation\u001b[39m\u001b[39m\"\u001b[39m,\n\u001b[0;32m     34\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mvalue\u001b[39m\u001b[39m\"\u001b[39m: companyRating[\u001b[39m0\u001b[39m][\u001b[39m'\u001b[39m\u001b[39mratingDetailsPBRecommendation\u001b[39m\u001b[39m'\u001b[39m]\n\u001b[0;32m     35\u001b[0m })\n\u001b[0;32m     36\u001b[0m researchReport\u001b[39m.\u001b[39mappend({\n\u001b[0;32m     37\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mkey\u001b[39m\u001b[39m\"\u001b[39m: \u001b[39m\"\u001b[39m\u001b[39mPE Recommendation\u001b[39m\u001b[39m\"\u001b[39m,\n\u001b[0;32m     38\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mvalue\u001b[39m\u001b[39m\"\u001b[39m: companyRating[\u001b[39m0\u001b[39m][\u001b[39m'\u001b[39m\u001b[39mratingDetailsPERecommendation\u001b[39m\u001b[39m'\u001b[39m]\n\u001b[0;32m     39\u001b[0m })\n\u001b[0;32m     40\u001b[0m researchReport\u001b[39m.\u001b[39mappend({\n\u001b[0;32m     41\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mkey\u001b[39m\u001b[39m\"\u001b[39m: \u001b[39m\"\u001b[39m\u001b[39mAltman ZScore\u001b[39m\u001b[39m\"\u001b[39m,\n\u001b[1;32m---> 42\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mvalue\u001b[39m\u001b[39m\"\u001b[39m: fScore[\u001b[39m0\u001b[39;49m][\u001b[39m'\u001b[39m\u001b[39maltmanZScore\u001b[39m\u001b[39m'\u001b[39m]\n\u001b[0;32m     43\u001b[0m })\n\u001b[0;32m     44\u001b[0m researchReport\u001b[39m.\u001b[39mappend({\n\u001b[0;32m     45\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mkey\u001b[39m\u001b[39m\"\u001b[39m: \u001b[39m\"\u001b[39m\u001b[39mPiotroski Score\u001b[39m\u001b[39m\"\u001b[39m,\n\u001b[0;32m     46\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mvalue\u001b[39m\u001b[39m\"\u001b[39m: fScore[\u001b[39m0\u001b[39m][\u001b[39m'\u001b[39m\u001b[39mpiotroskiScore\u001b[39m\u001b[39m'\u001b[39m]\n\u001b[0;32m     47\u001b[0m })\n\u001b[0;32m     48\u001b[0m researchReport\u001b[39m.\u001b[39mappend({\n\u001b[0;32m     49\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mkey\u001b[39m\u001b[39m\"\u001b[39m: \u001b[39m\"\u001b[39m\u001b[39mEnvironmental Score\u001b[39m\u001b[39m\"\u001b[39m,\n\u001b[0;32m     50\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mvalue\u001b[39m\u001b[39m\"\u001b[39m: esgScores[\u001b[39m0\u001b[39m][\u001b[39m'\u001b[39m\u001b[39menvironmentalScore\u001b[39m\u001b[39m'\u001b[39m]\n\u001b[0;32m     51\u001b[0m })\n",
      "\u001b[1;31mIndexError\u001b[0m: list index out of range"
     ]
    }
   ],
   "source": [
    "step = \"5\"\n",
    "s5Data = []\n",
    "r = findPibData(SearchService, SearchKey, pibIndexName, cik, step, returnFields=['id', 'symbol', 'cik', 'step', 'description', 'insertedDate',\n",
    "                                                                   'pibData'])\n",
    "\n",
    "if r.get_count() == 0:\n",
    "    companyRating = rating(apikey=apikey, symbol=symbol)\n",
    "    fScore = financialScore(apikey=apikey, symbol=symbol)\n",
    "    esgScores = esgScore(apikey=apikey, symbol=symbol)\n",
    "    esgRating = esgRatings(apikey=apikey, symbol=symbol)\n",
    "    ugConsensus = upgradeDowngrades(apikey=apikey, symbol=symbol)\n",
    "    priceConsensus = priceTarget(apikey=apikey, symbol=symbol)\n",
    "    #ratingsDf = pd.DataFrame.from_dict(pd.json_normalize(companyRating))\n",
    "    researchReport = []\n",
    "\n",
    "    try:\n",
    "        researchReport.append({\n",
    "            \"key\": \"Overall Recommendation\",\n",
    "            \"value\": companyRating[0]['ratingRecommendation']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"DCF Recommendation\",\n",
    "            \"value\": companyRating[0]['ratingDetailsDCFRecommendation']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"ROE Recommendation\",\n",
    "            \"value\": companyRating[0]['ratingDetailsROERecommendation']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"ROA Recommendation\",\n",
    "            \"value\": companyRating[0]['ratingDetailsROARecommendation']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"PB Recommendation\",\n",
    "            \"value\": companyRating[0]['ratingDetailsPBRecommendation']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"PE Recommendation\",\n",
    "            \"value\": companyRating[0]['ratingDetailsPERecommendation']\n",
    "        })\n",
    "    except:\n",
    "        logging.info('No data found for companyRating')\n",
    "        pass\n",
    "\n",
    "    try:\n",
    "        researchReport.append({\n",
    "            \"key\": \"Altman ZScore\",\n",
    "            \"value\": fScore[0]['altmanZScore']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"Piotroski Score\",\n",
    "            \"value\": fScore[0]['piotroskiScore']\n",
    "        })\n",
    "    except:\n",
    "        logging.info('No data found for fScore')\n",
    "        pass\n",
    "\n",
    "    try:\n",
    "        researchReport.append({\n",
    "            \"key\": \"Environmental Score\",\n",
    "            \"value\": esgScores[0]['environmentalScore']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"Social Score\",\n",
    "            \"value\": esgScores[0]['socialScore']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"Governance Score\",\n",
    "            \"value\": esgScores[0]['governanceScore']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"ESG Score\",\n",
    "            \"value\": esgScores[0]['ESGScore']\n",
    "        })\n",
    "    except:\n",
    "        logging.info('No data found for esgScores')\n",
    "        pass\n",
    "\n",
    "    try:\n",
    "        researchReport.append({\n",
    "            \"key\": \"ESG RIsk Rating\",\n",
    "            \"value\": esgRating[0]['ESGRiskRating']\n",
    "        })\n",
    "    except:\n",
    "        logging.info('No data found for esgRating')\n",
    "        pass\n",
    "\n",
    "    try:\n",
    "        researchReport.append({\n",
    "            \"key\": \"Analyst Consensus Buy\",\n",
    "            \"value\": ugConsensus[0]['buy']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"Analyst Consensus Sell\",\n",
    "            \"value\": ugConsensus[0]['sell']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"Analyst Consensus Strong Buy\",\n",
    "            \"value\": ugConsensus[0]['strongBuy']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"Analyst Consensus Strong Sell\",\n",
    "            \"value\": ugConsensus[0]['strongSell']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"Analyst Consensus Hold\",\n",
    "            \"value\": ugConsensus[0]['hold']\n",
    "        })\n",
    "        researchReport.append({\n",
    "            \"key\": \"Analyst Consensus\",\n",
    "            \"value\": ugConsensus[0]['consensus']\n",
    "        })\n",
    "    except:\n",
    "        logging.info('No data found for ugConsensus')\n",
    "        pass\n",
    "    # researchReport.append({\n",
    "    #     \"key\": \"Price Target Consensus\",\n",
    "    #     \"value\": priceConsensus[0]['targetConsensus']\n",
    "    # })\n",
    "    # researchReport.append({\n",
    "    #     \"key\": \"Price Target Median\",\n",
    "    #     \"value\": priceConsensus[0]['targetMedian']\n",
    "    # })\n",
    "  \n",
    "    s5Data.append({\n",
    "                'id' : str(uuid.uuid4()),\n",
    "                'symbol': symbol,\n",
    "                'cik': cik,\n",
    "                'step': step,\n",
    "                'description': 'Research Report',\n",
    "                'insertedDate': today.strftime(\"%Y-%m-%d\"),\n",
    "                'pibData' : str(researchReport)\n",
    "        })\n",
    "    mergeDocs(SearchService, SearchKey, pibIndexName, s5Data)\n",
    "else:\n",
    "    for s in r:\n",
    "        s5Data.append(\n",
    "            {\n",
    "                'id' : s['id'],\n",
    "                'symbol': s['symbol'],\n",
    "                'cik': s['cik'],\n",
    "                'step': s['step'],\n",
    "                'description': s['description'],\n",
    "                'insertedDate': s['insertedDate'],\n",
    "                'pibData' : s['pibData']\n",
    "            })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "from azure.search.documents import SearchClient\n",
    "from azure.core.credentials import AzureKeyCredential\n",
    "\n",
    "searchClient = SearchClient(endpoint=f\"https://{SearchService}.search.windows.net\",\n",
    "    index_name=pibIndexName,\n",
    "    credential=AzureKeyCredential(SearchKey))\n",
    "\n",
    "r = searchClient.search(  \n",
    "    search_text=\"\",\n",
    "    filter=\"cik eq '\" + \"789019\" + \"' and step eq '\" + \"2\" + \"'\",\n",
    "    select=[\"id\"],\n",
    "    semantic_configuration_name=\"semanticConfig\",\n",
    "    include_total_count=True\n",
    ")\n",
    "if r.get_count() > 0:\n",
    "    for doc in r:\n",
    "        searchClient.delete_documents(doc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 7. Paid Data - Investor Presentations - Financial Reports (Balance Sheet, Income Statement and Cash Flow) for last 3 years?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "deletePibData(SearchService, SearchKey, pibIndexName, \"1535929\", \"1\", returnFields=['id', 'symbol', 'cik', 'step', 'description', 'insertedDate',\n",
    "                                                                     'pibData'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}