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

# Historical US Stock, ETF & Mutual Fund Prices in Snowflake

> Daily EOD prices for 50,000+ US stocks, ETFs and mutual funds in Snowflake: raw and adjusted open, high, low and close plus volume, back to each listing date.

## Overview

The Historical Stock Prices listing delivers the complete daily end-of-day (EOD) price history for more than 50,000 US stocks, ETFs and mutual funds, going back to each security's first trading day, with no files to download and no APIs to call. That is roughly 18,500 stocks, 5,950 ETFs and 25,800 mutual funds in a single table.

Data is standardized into analytic‑ready tables (security reference, daily price history) with raw open, high, low and close alongside split- and dividend-adjusted open, high, low and close, plus automated pipeline quality checks (unique grain, placeholder price detection, corporate-action restatement). You can query, join, backtest and build dashboards right away, without building or maintaining your own market data pipeline.

> **[Get it on the Snowflake Marketplace](https://fruitstand.dev/contact.md)**
>
> **\$100/month** flat rate — no pipelines to build.

* **50,000+ US securities**: \~18,500 stocks (common and preferred), \~5,950 ETFs and \~25,800 mutual funds
* **Full history**, back to each ticker's listing date, often decades
* **Raw and adjusted OHLC**: as-traded open, high, low and close, plus versions adjusted for splits and dividends/distributions
* **Refreshed daily at 5:00 AM Pacific**, with a full per-ticker restatement twice a week
* **One format for every asset class**, so stocks, ETFs and mutual funds can be analyzed with the same query
* **Automated pipeline quality checks** (unique grain, placeholder price detection, corporate-action restatement)

Fruit Stand also publishes [fund and ETF returns](https://fruitstand.dev/datasets/fund-returns.md), [FRED economic data](https://fruitstand.dev/datasets/fred-series.md) and [earnings call transcripts](https://fruitstand.dev/datasets/earnings-call-transcripts.md) on the Snowflake Marketplace.

## Coverage

As of October 2026:

| Asset class | Securities | Notes |
| - | - | - |
| Stocks | \~18,500 | \~17,800 common stocks and \~750 preferred stocks |
| ETFs | \~5,950 | US-listed exchange-traded funds |
| Mutual funds | \~25,800 | Daily NAV recorded as open, high, low and close |

Every security is US-listed and priced in its trading currency. Securities are keyed by `code` and `exchange_code` together, so exchanges outside the US can be added later without breaking existing queries.

## Schema

Two secure views work together: `security_vw` (one row per security) and `security_price_history_vw` (one row per security per trading day), joined on `code` and `exchange_code`.

### Security Reference

One row per security:

| Column | Type | Description |
| - | - | - |
| `code` | `varchar` | Ticker symbol |
| `exchange_code` | `varchar` | Exchange group the ticker belongs to (`US` for all US-listed securities) |
| `name` | `varchar` | Security name |
| `country` | `varchar` | Country of listing |
| `exchange` | `varchar` | Listing venue (e.g. NYSE, NASDAQ, NYSE ARCA, BATS, NMFQS for mutual funds) |
| `currency` | `varchar` | Trading currency |
| `type` | `varchar` | Security type as reported by the source (e.g. Common Stock, Preferred Stock, ETF, FUND) |
| `asset_class` | `varchar` | Normalized asset class: `stock`, `etf` or `fund` |
| `isin` | `varchar` | International Securities Identification Number |
| `is_inactive` | `boolean` | `true` once the security drops out of the daily US symbol list |
| `inactive_date` | `date` | Date the security was marked inactive, if applicable |

Use `asset_class` rather than `type` to filter by stocks, ETFs or mutual funds. The source's `type` labels aren't consistent (for example `FUND` vs `Mutual Fund`), so `asset_class` is normalized from the listing each security came from.

```sql theme={null}
select code, exchange_code, name, exchange, type, asset_class
from security_vw
where code in ('AAPL', 'VTI', 'VFIAX')
  and exchange_code = 'US';
```

Example output (illustrative):

| code | exchange\_code | name | exchange | type | asset\_class |
| - | - | - | - | - | - |
| AAPL | US | Apple Inc | NASDAQ | Common Stock | stock |
| VTI | US | Vanguard Total Stock Market Index Fund ETF Shares | NYSE ARCA | ETF | etf |
| VFIAX | US | Vanguard 500 Index Fund Admiral Shares | NMFQS | FUND | fund |

### Daily Price History

One row per security per trading day:

| Column | Type | Description |
| - | - | - |
| `code` | `varchar` | Ticker symbol |
| `exchange_code` | `varchar` | Exchange group the ticker belongs to |
| `date` | `date` | Trading date |
| `open` | `number` | Opening price, as traded |
| `high` | `number` | Daily high, as traded |
| `low` | `number` | Daily low, as traded |
| `close` | `number` | Official closing price, as traded (NAV for mutual funds) |
| `adjusted_open` | `number` | Opening price adjusted for splits and dividends/distributions |
| `adjusted_high` | `number` | Daily high adjusted for splits and dividends/distributions |
| `adjusted_low` | `number` | Daily low adjusted for splits and dividends/distributions |
| `adjusted_close` | `number` | Closing price adjusted for splits and dividends/distributions |
| `volume` | `number` | Shares traded, adjusted for splits |

`open`, `high`, `low` and `close` are the prices as they traded on that day. `adjusted_close` is backward-adjusted for every split and dividend/distribution since, so the ratio of two `adjusted_close` values is the total return between those dates.

`adjusted_open`, `adjusted_high` and `adjusted_low` apply the same day's adjustment factor (`adjusted_close / close`) to the raw open, high and low. That gives you a full adjusted OHLC bar for every day, so candlestick charts, daily ranges and indicators like ATR stay continuous across splits and ex-dividend dates. They're calculated in the view from the current `adjusted_close`, so they're never out of step with it after a restatement.

`volume` is adjusted for splits only: after a 4-for-1 split, earlier volumes are multiplied by 4 so share counts are comparable across the split.

Mutual funds don't trade intraday, so their `open`, `high`, `low` and `close` all equal the day's NAV and `volume` is 0.

```sql theme={null}
select code, date, adjusted_open, adjusted_high, adjusted_low, adjusted_close, close, volume
from security_price_history_vw
where code = 'AAPL'
  and exchange_code = 'US'
order by date desc
limit 3;
```

Example output (illustrative):

| code | date | adjusted\_open | adjusted\_high | adjusted\_low | adjusted\_close | close | volume |
| - | - | - | - | - | - | - | - |
| AAPL | 2026-10-19 | XXX.XX | XXX.XX | XXX.XX | XXX.XX | XXX.XX | XXXXXXXX |
| AAPL | 2026-10-16 | XXX.XX | XXX.XX | XXX.XX | XXX.XX | XXX.XX | XXXXXXXX |
| AAPL | 2026-10-15 | XXX.XX | XXX.XX | XXX.XX | XXX.XX | XXX.XX | XXXXXXXX |

### Example: total return between two dates

Because `adjusted_close` folds in splits and dividends, a total return is a single ratio:

```sql theme={null}
with px as (
    select code, date, adjusted_close
    from security_price_history_vw
    where code in ('AAPL', 'VTI', 'VFIAX')
      and exchange_code = 'US'
      and date between '2016-01-01' and '2025-12-31'
)
select
    code,
    min(date) as start_date,
    max(date) as end_date,
    max_by(adjusted_close, date) / min_by(adjusted_close, date) - 1 as total_return
from px
group by code
order by total_return desc;
```

### Example: latest close for every ETF

```sql theme={null}
select s.code, s.name, p.date, p.close
from security_vw s
join security_price_history_vw p
  on p.code = s.code
 and p.exchange_code = s.exchange_code
where s.asset_class = 'etf'
  and not s.is_inactive
qualify row_number() over (partition by s.code, s.exchange_code order by p.date desc) = 1;
```

For a walkthrough of when to use `close` and when to use `adjusted_close`, see [Adjusted Close vs. Close: Which Stock Price to Use](https://fruitstand.dev/blog/adjusted-close-vs-close.md) on the blog.

## Data Ingestion

Data is ingested into Snowflake with best practices in mind, across two chained stages.

**1. Security universe**

The list of US stocks, ETFs and mutual funds is pulled from the pricing API's exchange symbol lists, tagged with a normalized asset class, and loaded into Snowflake, then standardized through staging and internal tables into the security reference table.

**2. Price history**

End-of-day prices (open, high, low, close, adjusted close and volume) are written to parquet and staged in a dated S3 folder before being copied into Snowflake and merged into the price history table on `code`, `exchange_code` and `date`. Dividend and split feeds are checked on every run, and any security with a new corporate action gets its full history refetched, since the provider backward-adjusts the entire adjusted-close history whenever one posts. Adjusted open, high and low are derived from that adjusted close in the view.

**Refresh cadence**: new data is available each day at 5:00 AM Pacific, covering the previous US trading session. Five days a week (the Sunday, Monday, Tuesday, Thursday and Friday runs), an incremental bulk update pulls the last few days of prices for the whole universe, plus a full history refetch for any security with a new dividend or split. Twice a week (the Wednesday and Saturday runs), every security's full price history is refetched and restated.

## Data Quality

* **Unique grain**: every `(code, exchange_code, date)` must be unique in the price history, and every `(code, exchange_code)` must be unique in the reference table. Both are checked automatically on every run.
* **Placeholder price detection**: zero adjusted closes and the source's backward-adjustment overflow value are flagged in the pipeline and excluded from `security_price_history_vw` instead of being passed through as real prices.
* **Stub row detection**: a security's very first price row is excluded when it sits more than 4 days before the next one, a lone placeholder well ahead of where real trading begins.
* **Corporate-action restatement**: new splits and dividends trigger a full history refetch for that security, and a twice-weekly full restatement catches anything the incremental path missed.

Flagged rows are filtered out before they reach the view, so you don't need to exclude them in your own queries.

## Disclosures

Fruit Stand Data LLC is not endorsed by or affiliated with any of the underlying pricing data providers or exchanges. Use [contact@fruitstand.dev](mailto:contact@fruitstand.dev) for questions.

**Survivorship**: the dataset covers securities currently on the US symbol list. Delisted securities aren't included, so backtests over older periods carry survivorship bias.

**No-trade days**: when a security has no trades on a given day, no price row is produced for that date. Thinly traded securities can have gaps in their history.

## F.A.Q.

Frequently asked questions from our clients.

> **What's the difference between close and adjusted close?**
>
> `close` is the price the security actually closed at that day. `adjusted_close` restates that price so it's comparable across splits and dividends: after a 2-for-1 split, for example, every earlier adjusted close is halved, and every dividend lowers the earlier adjusted prices by the payout ratio. Use `close` to see what traded on a given day, and `adjusted_close` for returns, charts and backtests.

> **Are open, high and low adjusted too?**
>
> Yes. `adjusted_open`, `adjusted_high`, `adjusted_low` and `adjusted_close` are all adjusted for splits and dividends/distributions, so you get a complete adjusted OHLC bar for every day. The raw `open`, `high`, `low` and `close` columns stay as traded. `volume` is adjusted for splits only.

> **Does this include mutual fund NAVs?**
>
> Yes. Roughly 25,800 US mutual funds are included next to stocks and ETFs in the same table. Each day's net asset value is recorded as `open`, `high`, `low` and `close`, with `volume` of 0, and the adjusted columns fold in distributions. Filter with `asset_class = 'fund'`.

> **How often is the data refreshed?**
>
> Every day at 5:00 AM Pacific, with the previous US trading session. Five days a week this is an incremental update of recent prices, plus a full refetch for any security with a new split or dividend. On the Wednesday and Saturday runs, every security's full history is refetched and restated.

> **When does replication across cloud regions happen?**
>
> Replication of refreshes across cloud regions begins at 5:00 AM PST each day. If you consume this listing from a Snowflake region other than its primary region, expect each day's updated data to arrive after that replication run.

> **How far back does the historical data go?**
>
> History starts at each security's own listing date rather than a fixed cutoff shared across the universe, so long-listed stocks and funds go back decades.

> **Is this intraday or real-time data?**
>
> No. This is end-of-day data, one row per security per trading day, refreshed daily at 5:00 AM Pacific.

> **Can I request other exchanges or countries to be made available in Snowflake?**
>
> Yes! Securities are already keyed by exchange so that non-US listings can be added. Please reach out to [contact@fruitstand.dev](mailto:contact@fruitstand.dev) with your requirements.


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