{"id":52026,"date":"2022-08-06T00:00:00","date_gmt":"2022-08-06T07:00:00","guid":{"rendered":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/blog\/building-an-sp-500-dashboard-using-streamlit-and-griddb\/"},"modified":"2022-08-06T00:00:00","modified_gmt":"2022-08-06T07:00:00","slug":"building-an-sp-500-dashboard-using-streamlit-and-griddb","status":"publish","type":"post","link":"https:\/\/www.griddb.net\/en\/blog\/building-an-sp-500-dashboard-using-streamlit-and-griddb\/","title":{"rendered":"Building an S&P 500 Dashboard using Streamlit and GridDB"},"content":{"rendered":"

A dashboard is a comprehensive snapshot of your company\u2019s performance. Because the nature of a dashboard is dynamic, one can click on any area of performance and \u2018drill-down\u2019 to see more details. The function of a dashboard is to provide reliable results by aggregating and extracting value from all the data collected in your data warehouse, otherwise known as your key performance indicators (KPIs). This way, it becomes easy for users to understand data, even non-technical users. We will use streamlit and GridDB to help us built the dashboard webapp. Streamlit lets you turn data scripts into sharable web apps in minutes.<\/p>\n

As a first step, we will create a simple we scraper of S&P500 data. After retrieving the ticker symbols, the resulting list will be used to fetch the stock price data from the yfinance library in Python. In the end, we will create the web app combining both functions.<\/p>\n

The outline of the tutorial is as follows:<\/p>\n

    \n
  1. Prerequisites and Environment setup<\/li>\n
  2. Dataset overview<\/li>\n
  3. Importing required libraries <\/li>\n
  4. Loading datasets<\/li>\n
  5. Building the dashboard<\/li>\n
  6. Conclusion<\/li>\n<\/ol>\n

    Prerequisites and Environment setup<\/h2>\n

    For this project, we will need to install Streamlit and have GridDB installed along with its Python Client. The following packages need to be installed before you continue with the tutorial \u2013<\/p>\n

      \n
    1. \n

      Pandas<\/p>\n<\/li>\n

    2. \n

      matplotlib<\/p>\n<\/li>\n

    3. \n

      griddb_python<\/p>\n<\/li>\n

    4. \n

      Streamlit<\/p>\n<\/li>\n

    5. \n

      seaborn<\/p>\n<\/li>\n

    6. \n

      numpy<\/p>\n<\/li>\n

    7. \n

      yfinance<\/p>\n<\/li>\n<\/ol>\n

      You can install these packages in Conda\u2019s virtual environment using conda install package-name<\/code>. In case you are using Python directly via terminal\/command prompt, pip install package-name<\/code> will do the work.<\/p>\n

      GridDB installation<\/h3>\n

      While loading the dataset, this tutorial will cover two methods \u2013 Using GridDB as well as Using Pandas. To access GridDB using Python, the following packages also need to be installed beforehand:<\/p>\n

        \n
      1. GridDB C-client<\/a><\/li>\n
      2. SWIG (Simplified Wrapper and Interface Generator)<\/li>\n
      3. GridDB Python Client<\/a><\/li>\n<\/ol>\n

        Dataset Overview<\/h2>\n

        The S&P 500 stock market index is maintained by S&P Dow Jones Indices. It comprises 504 common stocks which are issued by 500 large-cap companies traded on American stock exchanges. Although called the S&P 500, the index contains 505 stocks because it includes two share classes of stock from 5 of its component companies.<\/p>\n

        The data used are available using these two links (https:\/\/en.wikipedia.org\/wiki\/List_of_S%26P_500_companies<\/code>) and (https:\/\/pypi.org\/project\/yfinance\/<\/code>).<\/p>\n

        Importing Required Libraries and the datasets<\/h2>\n
        \n
        import griddb_python as griddb\nimport streamlit as st\nimport pandas as pd\nimport base64\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport numpy as np\nimport yfinance as yf\nst.set_option('deprecation.showPyplotGlobalUse', False)<\/code><\/pre>\n<\/div>\n
        2022-04-25 14:04:27.637 INFO    numexpr.utils: Note: NumExpr detected 12 cores but \"NUMEXPR_MAX_THREADS\" not set, so enforcing safe limit of 8.\n2022-04-25 14:04:27.663 INFO    numexpr.utils: NumExpr defaulting to 8 threads.\n<\/code><\/pre>\n
        Loading the Dataset<\/h2>\n
        We should now load the data into the GridDB server. Although it is possible to read the data directly from the CSV file, we can take advantage of GridDB\u2019s hybrid in-memory architecture to increase the speed of the application. We will also be able to load the necessary data into dataframes, boosting the performance of our queries.<\/p>\n
        Using GridDB<\/h3>\n
        Toshiba GridDB\u2122 is a highly scalable NoSQL database best suited for IoT and Big Data. The foundation of GridDB\u2019s principles is based upon offering a versatile data store that is optimized for IoT, provides high scalability, tuned for high performance, and ensures high reliability.<\/p>\n
        To store large amounts of data, a CSV file can be cumbersome. GridDB serves as a perfect alternative as it in open-source and a highly scalable database. GridDB is a scalable, in-memory, No SQL database which makes it easier for you to store large amounts of data. If you are new to GridDB, a tutorial on reading and writing to GridDB<\/a> can be useful.<\/p>\n
        To start, we will load all of the data (in CSV form) into our GridDB server. Though we could easily load the data directly from CSV into dataframes objects, loading them into our database first will significantly increase the speed of the application because of GridDB\u2019s hybrid in-memory architecture.<\/p>\n
        \n
        factory = griddb.StoreFactory.get_instance()\n\n# Initialize the GridDB container (enter your database credentials)\ntry:\n    gridstore = factory.get_store(host=host_name, port=your_port, \n            cluster_name=cluster_name, username=admin, \n            password=admin)\n\n    info = griddb.ContainerInfo(\"S&P500\",\n                    [[\"Symbol\", griddb.Type.STRING],[\"Security\", griddb.Type.STRING],[\" SEC filings\", griddb.Type.STRING],\n                     [\"GICS Sector\", griddb.Type.STRING],[\"GICS Sub-Industry\", griddb.Type.STRING],\n                     [\"Headquarters Location\", griddb.Type.STRING],[\"Date first added\", griddb.Type.TIMESTAMP]\n                     [\"CIK\",griddb.Type.INTEGER]], [\"Founded\",griddb.Type.INTEGER]],\n                    griddb.ContainerType.COLLECTION, True)\n    cont = gridstore.put_container(info) \n\n\nsql_statement = ('SELECT * FROM S&P500')\ndataset = pd.read_sql_query(sql_statement, cont)<\/code><\/pre>\n<\/div>\n
        Note that the cont<\/code> variable has the container information where our data is stored. Replace the S&P500<\/code> with the name of your container. More info can be found in this tutorial reading and writing to GridDB<\/a>.<\/p>\n
        When it comes to IoT and Big Data use cases, GridDB clearly stands out among other databases in the Relational and NoSQL space. Overall, GridDB offers multiple reliability features for mission-critical applications that require high availability and data retention.<\/p>\n
        Using pandas to read_html<\/h3>\n
        In Python you need to give access to a file by opening it. You can do it by using the open() function. Open returns a file object, which has methods and attributes for getting information about and manipulating the opened file. After loading the data we download the file in csv format.<\/p>\n
        \n
        # @st.cache\ndef load_data():\n    url = 'https:\/\/en.wikipedia.org\/wiki\/List_of_S%26P_500_companies'\n    html = pd.read_html(url, header = 0)\n    df = html[0]\n    return df\n\ndf = load_data()\nsector = df.groupby('GICS Sector')<\/code><\/pre>\n<\/div>\n
        \n
        # Sidebar - Sector selection\nsorted_sector_unique = sorted( df['GICS Sector'].unique() )\nselected_sector = st.sidebar.multiselect('Sector', sorted_sector_unique, sorted_sector_unique)\n\n# Filtering data\ndf_selected_sector = df[ (df['GICS Sector'].isin(selected_sector)) ]<\/code><\/pre>\n<\/div>\n
        \n
        # Download S&P500 data\n# https:\/\/discuss.streamlit.io\/t\/how-to-download-file-in-streamlit\/1806\ndef filedownload(df):\n    csv = df.to_csv(index=False)\n    b64 = base64.b64encode(csv.encode()).decode()  # strings <-> bytes conversions\n    href = f'<a href=\"data:file\/csv;base64,{b64}\" download=\"SP500.csv\">Download CSV File<\/a>'\n    return href\n\nst.markdown(filedownload(df_selected_sector), unsafe_allow_html=True)<\/code><\/pre>\n<\/div>\n
        DeltaGenerator(_root_container=0, _provided_cursor=None, _parent=None, _block_type=None, _form_data=None)\n<\/code><\/pre>\n
        \n
        # https:\/\/pypi.org\/project\/yfinance\/\n\ndata = yf.download(\n        tickers = list(df_selected_sector[:10].Symbol),\n        period = \"ytd\",\n        interval = \"1d\",\n        group_by = 'ticker',\n        auto_adjust = True,\n        prepost = True,\n        threads = True,\n        proxy = None\n    )<\/code><\/pre>\n<\/div>\n
        [*********************100%***********************]  10 of 10 completed\n<\/code><\/pre>\n
        \n
        df.head()<\/code><\/pre>\n<\/div>\n
        \n