> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dataville.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Build a sector dataset in DuckDB from filings and your own notes

> Join a sector's recent SEC filings with your notes on each company in DuckDB, and save them as a CSV file.

export const sectorScript = (String.raw)`-- Stop at the first error instead of running the rest of the script.
.bail on
-- Don't print the results of the setup statements.
.mode trash

-- Stop with a clear message when the API key isn't set.
SELECT CASE WHEN coalesce(getenv('DATAVILLE_API_KEY'), '') = ''
    THEN error('Set DATAVILLE_API_KEY to your Dataville API key first.')
END;

-- Send your API key with every request to Dataville. It comes from the
-- DATAVILLE_API_KEY environment variable, so it never appears in a URL.
CREATE SECRET dataville (
    TYPE http,
    BEARER_TOKEN getenv('DATAVILLE_API_KEY'),
    SCOPE 'https://api.dataville.com'
);

-- Fetch each file in one request instead of reading it in parts. Every
-- request counts toward your limits, and the edgar export is small enough
-- to fetch whole.
SET force_download = true;

-- Look up each ticker in sector.csv in the edgar source. Each lookup returns
-- the company's CIK and adds its recent filings to Dataville's edgar export.
SET VARIABLE lookup_urls = (
    SELECT list('https://api.dataville.com/edgar/' || ticker)
    FROM read_csv('sector.csv')
);
CREATE TABLE companies AS
SELECT
    regexp_extract(filename, '[^/]+$') AS ticker,
    data.metadata.cik AS cik,
    data.metadata.company_name AS company_name
FROM read_json(getvariable('lookup_urls'), filename = true, union_by_name = true);

-- Match each company to its filings in the edgar export by CIK, and keep the
-- filings from the last 12 months.
CREATE TABLE sector_filings AS
SELECT
    sector.ticker,
    sector.segment,
    companies.company_name,
    filings.form_type,
    filings.filing_date,
    -- An 8-K's items say what it reports, such as 2.03 for new debt.
    nullif(regexp_extract(filings.body, 'Items: ([^\n]*)', 1), '') AS items,
    filings.document_url
FROM read_csv('sector.csv') AS sector
JOIN companies ON companies.ticker = sector.ticker
JOIN read_parquet('https://api.dataville.com/api/v1/export/edgar?format=parquet') AS filings
    ON filings.cik = companies.cik
-- filing_date is text, so cast it to compare dates.
WHERE CAST(filings.filing_date AS DATE) >= current_date - INTERVAL 1 YEAR
ORDER BY sector.ticker, filings.filing_date DESC;

-- Save the dataset for your report.
COPY sector_filings TO 'sector-filings.csv' (HEADER);

-- Print how many filings each company has in the dataset.
.mode duckbox
SELECT ticker, count(*) AS filings
FROM sector_filings
GROUP BY ticker
ORDER BY ticker;
`;

export const sectorCompanies = (String.raw)`ticker,segment
HD,Home improvement
LOW,Home improvement
FND,Flooring
`;

A sector dataset lists the recent SEC filings of the public companies in a client's sector, next to your own notes on each company. You can build one in DuckDB from Dataville's `edgar` export and a CSV file of your own.

In this guide, you'll run a DuckDB script that looks up each company by ticker and joins its filings from the last 12 months to your notes. The script saves the result as a CSV file for your report.

## Before you start

You'll need:

* A Dataville account and its API key, from the [API Keys](https://app.dataville.com/api-keys) page.
* The DuckDB command-line tool, from the [DuckDB installation](https://duckdb.org/docs/installation) page.

## Build the dataset

Put both files from these steps in one folder, and run the script from that folder.

<Steps>
  <Step title="Download the company list">
    The company list is a CSV file with a `ticker` column and a `segment` column for your own notes. It has the three example companies from [Profile a client's competitors](/guides/competitor-scan).

    <a href={"data:text/csv;charset=utf-8," + encodeURIComponent(sectorCompanies)} download="sector.csv" className="block">
      <Card title="sector.csv" icon="file-csv">
        Download the company list with the example companies.
      </Card>
    </a>

    The file looks like this:

    ```csv theme={null}
    ticker,segment
    HD,Home improvement
    LOW,Home improvement
    FND,Flooring
    ```
  </Step>

  <Step title="Download the script">
    The script is for the DuckDB command-line tool. It looks up each ticker in the `edgar` source, which returns the company's CIK, the SEC's number for a filer. It then matches each company to its filings in the `edgar` export by CIK, keeps the last 12 months, and saves the result as `sector-filings.csv`.

    <a href={"data:application/sql;charset=utf-8," + encodeURIComponent(sectorScript)} download="sector.sql" className="block">
      <Card title="sector.sql" icon="file-code">
        Download the DuckDB script.
      </Card>
    </a>

    <Accordion title="View sector.sql" icon="code">
      ```sql theme={null}
      -- Stop at the first error instead of running the rest of the script.
      .bail on
      -- Don't print the results of the setup statements.
      .mode trash

      -- Stop with a clear message when the API key isn't set.
      SELECT CASE WHEN coalesce(getenv('DATAVILLE_API_KEY'), '') = ''
          THEN error('Set DATAVILLE_API_KEY to your Dataville API key first.')
      END;

      -- Send your API key with every request to Dataville. It comes from the
      -- DATAVILLE_API_KEY environment variable, so it never appears in a URL.
      CREATE SECRET dataville (
          TYPE http,
          BEARER_TOKEN getenv('DATAVILLE_API_KEY'),
          SCOPE 'https://api.dataville.com'
      );

      -- Fetch each file in one request instead of reading it in parts. Every
      -- request counts toward your limits, and the edgar export is small enough
      -- to fetch whole.
      SET force_download = true;

      -- Look up each ticker in sector.csv in the edgar source. Each lookup returns
      -- the company's CIK and adds its recent filings to Dataville's edgar export.
      SET VARIABLE lookup_urls = (
          SELECT list('https://api.dataville.com/edgar/' || ticker)
          FROM read_csv('sector.csv')
      );
      CREATE TABLE companies AS
      SELECT
          regexp_extract(filename, '[^/]+$') AS ticker,
          data.metadata.cik AS cik,
          data.metadata.company_name AS company_name
      FROM read_json(getvariable('lookup_urls'), filename = true, union_by_name = true);

      -- Match each company to its filings in the edgar export by CIK, and keep the
      -- filings from the last 12 months.
      CREATE TABLE sector_filings AS
      SELECT
          sector.ticker,
          sector.segment,
          companies.company_name,
          filings.form_type,
          filings.filing_date,
          -- An 8-K's items say what it reports, such as 2.03 for new debt.
          nullif(regexp_extract(filings.body, 'Items: ([^\n]*)', 1), '') AS items,
          filings.document_url
      FROM read_csv('sector.csv') AS sector
      JOIN companies ON companies.ticker = sector.ticker
      JOIN read_parquet('https://api.dataville.com/api/v1/export/edgar?format=parquet') AS filings
          ON filings.cik = companies.cik
      -- filing_date is text, so cast it to compare dates.
      WHERE CAST(filings.filing_date AS DATE) >= current_date - INTERVAL 1 YEAR
      ORDER BY sector.ticker, filings.filing_date DESC;

      -- Save the dataset for your report.
      COPY sector_filings TO 'sector-filings.csv' (HEADER);

      -- Print how many filings each company has in the dataset.
      .mode duckbox
      SELECT ticker, count(*) AS filings
      FROM sector_filings
      GROUP BY ticker
      ORDER BY ticker;
      ```
    </Accordion>
  </Step>

  <Step title="Run the script">
    Set your API key, then run the script in the same terminal:

    ```bash theme={null}
    export DATAVILLE_API_KEY=dataville_xxxxxxxxxxxxxxxx
    duckdb < sector.sql
    ```

    The script sends your key in a request header, so it never appears in a URL. It writes `sector-filings.csv` to the same folder and prints how many filings each company has. For the example companies on September 25, 2026, it printed:

    ```text theme={null}
    ┌─────────┬─────────┐
    │ ticker  │ filings │
    │ varchar │  int64  │
    ├─────────┼─────────┤
    │ FND     │       5 │
    │ HD      │       6 │
    │ LOW     │       5 │
    └─────────┴─────────┘
    ```

    If the script stops with "Set DATAVILLE\_API\_KEY to your Dataville API key first.", set the key in the same terminal and run it again.

    Here are the Floor & Decor rows of `sector-filings.csv`, without the `company_name` and `document_url` columns:

    | ticker | segment | form\_type | filing\_date | items |
    | - | - | - | - | - |
    | FND | Flooring | 8-K | 2026-07-30 | 2.02,9.01 |
    | FND | Flooring | 10-Q | 2026-07-30 | None |
    | FND | Flooring | 8-K | 2026-06-25 | 1.01,1.02,2.03,9.01 |
    | FND | Flooring | 8-K | 2026-05-07 | 5.07,9.01 |
    | FND | Flooring | 10-Q | 2026-04-30 | None |

    An 8-K's items say what it reports. Item 2.02 is an earnings release, and 5.02 is a change of directors or officers. Item 5.07 is a shareholder vote, and 2.03 is a new financial obligation, such as a loan. Floor & Decor's 8-K from June 25, 2026 lists items 1.01, 1.02, and 2.03. Those are a new material agreement, the end of another, and a new financial obligation.

    Each run makes one data lookup per ticker and one request to the export, which counts toward the export limit of 1,000 per day per account.

    Dataville adds a company's filings to the export within five minutes of its first lookup. If a company you've added has no rows, run the script again after five minutes. If SEC EDGAR is unavailable, the lookups come from Dataville's stored copy and may miss the newest filings, so run the script again later. See [Response format](/response-format#stale-results) to learn more about stale results.
  </Step>

  <Step title="Optional: Build a dataset for your own client">
    To build a dataset for a real client, replace the rows in `sector.csv` with the tickers of the companies in your client's sector and your notes on each one. Then run the script again.
  </Step>
</Steps>

## Next steps

To learn more about the export the script reads, or to compare revenue across competitors instead, see these pages.

<CardGroup cols={2}>
  <Card title="Export datasets" icon="download" href="/export-datasets">
    Every format Dataville exports, and how to read Parquet without downloading.
  </Card>

  <Card title="Profile a client's competitors" icon="chart-line" href="/guides/competitor-scan">
    Build a revenue comparison table from Claude and Data Studio.
  </Card>
</CardGroup>


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.