database_update.yml

name: Database Update

on:
  push:
  schedule:
    - cron: '0 12 * * SUN'

jobs:
  Add-New-Ticker:
    runs-on: ubuntu-latest
    steps:
      - name: checkout repo content
        uses: actions/checkout@v3
      - name: pull changes
        run: git pull https://${{secrets.PAT}}@github.com/JerBouma/FinanceDatabase.git main
      - name: setup python
        uses: actions/setup-python@v4
        with:
          python-version: '3.13'
      - run: pip install "pandas[excel]" financedatabase
      - name: Add New Tickers and Update Old Ones
        uses: jannekem/run-python-script-action@v1
        with:
          script: |
            import numpy as np
            import pandas as pd
            from typing import Optional

            # Market cap thresholds in USD
            MARKET_CAP_THRESHOLDS = {
                'Mega Cap':  200_000_000_000,
                'Large Cap':  10_000_000_000,
                'Mid Cap':    2_000_000_000,
                'Small Cap':    300_000_000,
                'Micro Cap':     50_000_000,
                'Nano Cap':               0,
            }

            def calculate_market_cap(value: Optional[float]) -> Optional[str]:
                """Categorize a market capitalization value into a named tier.
                
                Args:
                    value: Market capitalization in USD, or None/NaN.
                
                Returns:
                    Market cap tier label, or np.nan if value is missing/zero.
                """
                if pd.isna(value) or not value:
                    return np.nan
                for label, threshold in MARKET_CAP_THRESHOLDS.items():
                    if float(value) >= threshold:
                        return label
                return np.nan


            def lookup_industry(row_industry: str, fd_industries: pd.DataFrame) -> Optional[str]:
                """Map an exchange industry label to the FinanceDatabase equivalent.
                
                Args:
                    row_industry: Raw industry string from the exchange data.
                    fd_industries: Lookup DataFrame with FinanceDatabase industry names.
                
                Returns:
                    Mapped industry string, or np.nan if not found.
                """
                try:
                    result = fd_industries.loc[row_industry].iloc[0]
                    return result.iloc[0] if isinstance(result, pd.Series) else result
                except KeyError:
                    return np.nan


            def lookup_industry_group(industry: str, equities: pd.DataFrame) -> Optional[str]:
                """Infer the most common industry group for a given industry.

                Args:
                    industry: FinanceDatabase industry string.
                    equities: The equities reference DataFrame.

                Returns:
                    Most frequent industry_group value, or np.nan if not found.
                """
                if pd.isna(industry):
                    return np.nan
                subset = equities[equities['industry'] == industry]
                return subset['industry_group'].mode()[0] if not subset.empty else np.nan


            def lookup_sector(industry: str, industry_group: str, equities: pd.DataFrame) -> Optional[str]:
                """Infer the most common sector for a given industry and industry group.

                Args:
                    industry: FinanceDatabase industry string.
                    industry_group: FinanceDatabase industry_group string.
                    equities: The equities reference DataFrame.

                Returns:
                    Most frequent sector value, or np.nan if not found.
                """
                if pd.isna(industry) or pd.isna(industry_group):
                    return np.nan
                subset = equities[
                    (equities['industry_group'] == industry_group) &
                    (equities['industry'] == industry)
                ]
                return subset['sector'].mode()[0] if not subset.empty else np.nan


            def build_new_ticker(
                index: str,
                row: pd.Series,
                fd_industries: pd.DataFrame,
                equities: pd.DataFrame,
                market_cap: Optional[str],
            ) -> dict:
                """Build a new ticker entry for the equities database.

                Args:
                    index: Ticker symbol.
                    row: Raw row from the exchange data.
                    fd_industries: Lookup DataFrame for industries.
                    equities: The equities reference DataFrame.
                    market_cap: Pre-calculated market cap tier.

                Returns:
                    Dictionary with all required equity fields.
                """
                industry = lookup_industry(row['industry'], fd_industries)
                industry_group = lookup_industry_group(industry, equities)
                sector = lookup_sector(industry, industry_group, equities)

                return {
                    'name':            row['name'],
                    'summary':         np.nan,
                    'currency':        'USD',
                    'industry':        industry,
                    'industry_group':  industry_group,
                    'sector':          sector,
                    'exchange':        row['exchange'],
                    'market':          row['market'],
                    'country':         row['country'],
                    'state':           np.nan,
                    'city':            np.nan,
                    'zipcode':         np.nan,
                    'website':         np.nan,
                    'market_cap':      market_cap,
                    'isin':            np.nan,
                    'cusip':           np.nan,
                    'figi':            np.nan,
                    'composite_figi':  np.nan,
                    'shareclass_figi': np.nan,
                }


            # ---------------------------------------------------------------------------
            # Data ingestion
            # ---------------------------------------------------------------------------

            # Collect NASDAQ data
            nasdaq = pd.read_json("https://raw.githubusercontent.com/rreichel3/US-Stock-Symbols/main/nasdaq/nasdaq_full_tickers.json")
            nasdaq = nasdaq.set_index('symbol')
            nasdaq['exchange'] = 'NMS'
            nasdaq['market'] = 'NASDAQ Global Select'

            # Collect NYSE data
            nyse = pd.read_json("https://raw.githubusercontent.com/rreichel3/US-Stock-Symbols/main/nyse/nyse_full_tickers.json")
            nyse = nyse.set_index('symbol')
            nyse['exchange'] = 'ASE'
            nyse['market'] = 'NYSE MKT'

            # Collect AMEX data (acquired by NYSE, same exchange/market)
            amex = pd.read_json("https://raw.githubusercontent.com/rreichel3/US-Stock-Symbols/main/amex/amex_full_tickers.json")
            amex = amex.set_index('symbol')
            amex['exchange'] = 'ASE'
            amex['market'] = 'NYSE MKT'

            # Combine all exchange datasets
            exchange_data = pd.concat([nasdaq, nyse, amex])

            # ---------------------------------------------------------------------------
            # Reference data
            # ---------------------------------------------------------------------------

            fd_categories_path = 'compression/categories/github_exchange_categories.xlsx'
            fd_sectors         = pd.read_excel(fd_categories_path, sheet_name='sector',         index_col=1)
            fd_industry_groups = pd.read_excel(fd_categories_path, sheet_name='industry_group', index_col=1)
            fd_industries      = pd.read_excel(fd_categories_path, sheet_name='industry',       index_col=1)

            # Read the equities database
            equities = pd.read_csv('database/equities.csv', index_col=0)

            # ---------------------------------------------------------------------------
            # Main processing loop
            # ---------------------------------------------------------------------------

            ticker_dict: dict = {}

            for index, row in exchange_data.iterrows():
                market_cap = calculate_market_cap(row.get('marketCap'))

                try:
                    fd_data = equities.loc[index]

                    # Update market_cap only when it has changed and the new value is valid
                    if len(fd_data) == 0 and fd_data['market_cap'] != market_cap and pd.notna(market_cap):
                        ticker_dict[index] = {'symbol': index, **fd_data.to_dict(), 'market_cap': market_cap}
                    continue

                except KeyError:
                    # "NA" is parsed as NaN by pandas; normalise the index back to a string
                    if pd.isna(index):
                        index = "NA"

                    ticker_dict[index] = build_new_ticker(index, row, fd_industries, equities, market_cap)

            # ---------------------------------------------------------------------------
            # Merge results back into equities
            # ---------------------------------------------------------------------------

            updated_companies = pd.DataFrame.from_dict(ticker_dict, orient='index')
            updated_companies.index.name = 'symbol'

            # Drop accidental 'symbol' column that may surface from existing-row updates
            updated_companies = updated_companies.drop(columns=['symbol'], errors='ignore')

            print(f"There are {len(updated_companies)} new updates!")

            if not updated_companies.empty:
                # Update existing rows in-place
                existing_indices = updated_companies.index.intersection(equities.index)
                if not existing_indices.empty:
                    equities.update(updated_companies.loc[existing_indices])

                # Append completely new tickers
                new_indices = updated_companies.index.difference(equities.index)
                if not new_indices.empty:
                    equities = pd.concat([equities, updated_companies.loc[new_indices]])
                    
                equities = equities[~equities.index.duplicated(keep='first')]
                equities = equities[equities.index.notna()]

                equities = (
                    equities
                    .sort_index()
                    .loc[equities.index.notna()]   # drop NaN index entries
                )

                equities.to_csv('database/equities.csv')
      - name: Commit files and log
        run: |
          git config --global user.name 'GitHub Action'
          git config --global user.email 'action@github.com'
          git add -A
          git checkout main
          git diff-index --quiet HEAD || git commit -am "Update database with new tickers"
          git push
      - name: Check run status
        if: steps.run.outputs.status != '0'
        run: exit "${{ steps.run.outputs.status }}"

  Update-Compression-Files:
    needs: Add-New-Ticker
    runs-on: ubuntu-latest
    steps:
      - name: checkout repo content
        uses: actions/checkout@v3
      - name: pull changes
        run: git pull https://${{secrets.PAT}}@github.com/JerBouma/FinanceDatabase.git main
      - name: setup python
        uses: actions/setup-python@v4
        with:
          python-version: '3.10'
      - run: pip install "pandas[excel]" financedatabase
      - name: Update Compressions
        uses: jannekem/run-python-script-action@v1
        with:
          script: |
            import financedatabase as fd
            import pandas as pd

            cryptos = pd.read_csv('database/cryptos.csv')
            cryptos.to_csv('compression/cryptos.bz2', index=False, compression='bz2')

            currencies = pd.read_csv('database/currencies.csv')
            currencies.to_csv('compression/currencies.bz2', index=False, compression='bz2')

            equities = pd.read_csv('database/equities.csv')
            equities.to_csv('compression/equities.bz2', index=False, compression='bz2')

            etfs = pd.read_csv('database/etfs.csv')
            etfs.to_csv('compression/etfs.bz2', index=False, compression='bz2')

            funds = pd.read_csv('database/funds.csv')
            funds.to_csv('compression/funds.bz2', index=False, compression='bz2')

            indices = pd.read_csv('database/indices.csv')
            indices.to_csv('compression/indices.bz2', index=False, compression='bz2')

            moneymarkets = pd.read_csv('database/moneymarkets.csv')
            moneymarkets.to_csv('compression/moneymarkets.bz2', index=False, compression='bz2')
      - name: Commit files and log
        run: |
          git config --global user.name 'GitHub Action'
          git config --global user.email 'action@github.com'
          git add -A
          git checkout main
          git diff-index --quiet HEAD || git commit -am "Update Compression Files"
          git push
      - name: Check run status
        if: steps.run.outputs.status != '0'
        run: exit "${{ steps.run.outputs.status }}"

  Update-Categorization-Files:
      needs:  [Add-New-Ticker, Update-Compression-Files]
      runs-on: ubuntu-latest
      steps:
        - name: checkout repo content
          uses: actions/checkout@v3
        - name: pull changes
          run: git pull https://${{secrets.PAT}}@github.com/JerBouma/FinanceDatabase.git main
        - name: setup python
          uses: actions/setup-python@v4
          with:
            python-version: '3.10'
        - run: pip install "pandas[excel]" financedatabase
        - name: Update categories
          uses: jannekem/run-python-script-action@v1
          with:
            script: |
              import financedatabase as fd
              import pandas as pd

              cryptos = pd.read_csv("database/cryptos.csv", index_col=0)
              cryptos_categories = {}
              for column in cryptos:
                  if column in ['name', 'summary']:
                      continue
                      
                  cryptos_categories[column] = cryptos[column].dropna().unique()
                  cryptos_categories[column].sort()
                  
              df_temp = pd.DataFrame.from_dict(cryptos_categories, orient='index').reset_index()
              df_temp.to_csv('compression/categories/cryptos_categories.gzip', index=False, compression='gzip')
                  
              currencies = pd.read_csv("database/currencies.csv", index_col=0)
              currencies_categories = {}
              for column in currencies:
                  if column in ['name']:
                      continue

                  currencies_categories[column] = currencies[column].dropna().unique()
                  currencies_categories[column].sort()

              df_temp = pd.DataFrame.from_dict(currencies_categories, orient='index').reset_index()
              df_temp.to_csv('compression/categories/currencies_categories.gzip', index=False, compression='gzip')
                  
              equities =  pd.read_csv("database/equities.csv", index_col=0)
              equities_categories = {}
              for column in equities:
                  if column in ['name', 'summary', 'website']:
                      continue
                      
                  equities_categories[column] = equities[column].dropna().unique()
                  equities_categories[column].sort()
                  
              df_temp = pd.DataFrame.from_dict(equities_categories, orient='index').reset_index()
              df_temp.to_csv('compression/categories/equities_categories.gzip', index=False, compression='gzip')
                  
              etfs = pd.read_csv("database/etfs.csv", index_col=0)
              etfs_categories = {}
              for column in etfs:
                  if column in ['name', 'summary']:
                      continue
                      
                  etfs_categories[column] = etfs[column].dropna().unique()
                  etfs_categories[column].sort()

              df_temp = pd.DataFrame.from_dict(etfs_categories, orient='index').reset_index()
              df_temp.to_csv('compression/categories/etfs_categories.gzip', index=False, compression='gzip')
                  
              funds = pd.read_csv("database/funds.csv", index_col=0)
              funds_categories = {}
              for column in funds:
                  if column in ['name', 'summary', 'manager_name', 'manager_bio']:
                      continue
                      
                  funds_categories[column] = funds[column].dropna().unique()
                  funds_categories[column].sort()
                  
              df_temp = pd.DataFrame.from_dict(funds_categories, orient='index').reset_index()
              df_temp.to_csv('compression/categories/funds_categories.gzip', index=False, compression='gzip')
                  
              indices = pd.read_csv("database/indices.csv", index_col=0)
              indices_categories = {}
              for column in indices:
                  if column in ['name']:
                      continue
                      
                  indices_categories[column] = indices[column].dropna().unique()
                  indices_categories[column].sort()
                  
              df_temp = pd.DataFrame.from_dict(indices_categories, orient='index').reset_index()
              df_temp.to_csv('compression/categories/indices_categories.gzip', index=False, compression='gzip')
                  
              moneymarkets = pd.read_csv("database/moneymarkets.csv", index_col=0)
              moneymarkets_categories = {}
              for column in moneymarkets:
                  if column in ['name']:
                      continue
                      
                  moneymarkets_categories[column] = moneymarkets[column].dropna().unique()
                  moneymarkets_categories[column].sort()
                  
              df_temp = pd.DataFrame.from_dict(moneymarkets_categories, orient='index').reset_index()
              df_temp.to_csv('compression/categories/moneymarkets_categories.gzip', index=False, compression='gzip')

        - name: Commit files and log
          run: |
            git config --global user.name 'GitHub Action'
            git config --global user.email 'action@github.com'
            git add -A
            git checkout main
            git diff-index --quiet HEAD || git commit -am "Update Categorization Files"
            git push
        - name: Check run status
          if: steps.run.outputs.status != '0'
          run: exit "${{ steps.run.outputs.status }}"

  Check-GICS-Categorisation:
    needs:  [Add-New-Ticker, Update-Compression-Files, Update-Categorization-Files]
    runs-on: ubuntu-latest
    steps:
      - name: checkout repo content
        uses: actions/checkout@v3
      - name: setup python
        uses: actions/setup-python@v4
        with:
          python-version: '3.10'
      - run: pip install "pandas[excel]" financedatabase
      - name: Check GICS Categorisation
        uses: jannekem/run-python-script-action@v1
        with:
          script: |
            import pandas as pd
            import json

            invalid_rows = pd.DataFrame()
            errors = []
            gics = json.load(open("compression/categories/categories.json", "r"))
            equities = pd.read_csv("database/equities.csv", index_col=0)
            filtered_data = equities[equities['sector'].notna() & equities['industry_group'].notna() & equities['industry'].notna()]

            for index, row in filtered_data.iterrows():
                sector, industry_group, industry = row['sector'], row['industry_group'], row['industry']

                try:
                    # Search whether it can find the combination
                    gics[sector][industry_group][industry]
                except KeyError as error:
                    # If it can't, add to invalid_rows DataFrame
                    row['error'] = error
                    invalid_rows = pd.concat([invalid_rows, row], axis=1)


            if not invalid_rows.empty:
                invalid_rows = invalid_rows.T

                print("Invalid Rows for:")
                for index, row in invalid_rows.iterrows():
                    print(f"{index}: {row['error']}")
                
                raise ValueError("There are invalid sector, industry groups and/or industries found. "
                                 "Please check if it adheres to compression/categories/categories.json")