Module 10: Time series and market indices

View study sheet (PDF) View SQL cheat sheet (PDF)

Teaching this module? A facilitator guide adds a preparation checklist, timing cues, discussion guidance, slides, and a printable PDF.

This session teaches students to build a market index the way it is actually built, by bucketing cleared prices into time periods and tracking a summary statistic across those periods, and to treat the exercise as a lesson in methodology rather than a shortcut to a market call. Students construct a quarterly demand index for a brand or category from sold-lot records, using date bucketing in SQL to group transactions and a stable statistic such as median realized price or count of sold lots to summarize each quarter, then compare the shape of two brands' indices side by side. The session's central discipline is one that recurs throughout the course and is unavoidable here: the newest quarters in this dataset are still being ingested, so any index that runs up to the present will show an artificial falloff at its tail, and the session insists, repeatedly and explicitly, that this falloff is a data engineering artifact to be flagged, not a market signal to be reported. Students leave able to build the index and able to say, precisely, where it stops being trustworthy.

Target course(s) and level

Time series analysis, applied econometrics, or a market research methods course. Suitable for advanced undergraduates and graduate students who have taken or are concurrently taking an introductory statistics or econometrics course. No prior exposure to this dataset is assumed.

Learning objectives

By the end of this session, students will be able to:

  1. Explain what a market index built from transaction data represents, and distinguish it from a price index built from listed or asking prices.
  2. Bucket sold-lot records into calendar quarters using SQL date functions and compute a quarterly summary statistic for each bucket.
  3. Build a quarterly demand index for a single brand or category and identify the reference period against which later quarters would be compared.
  4. Explain, in specific and concrete terms, why the newest quarters of any such index must be treated as under-ingested rather than as evidence of a market decline, and identify where in a chart that caveat should appear.
  5. Compare the index shapes of two brands or categories and describe the comparison as a methodological exercise in relative pattern, not as a claim about which one is appreciating.
  6. (Extension) Retrieve the underlying sold-lot records programmatically via the production API and reproduce the quarterly bucketing in Python.

Prerequisites

An introductory statistics or econometrics course covering basic time series concepts such as trend and seasonality, or equivalent coursework in market research methods. No prior exposure to SQL or this dataset is assumed. The optional code extension assumes basic Python familiarity.

Materials and access needed

  • Sandbox access at sandbox.altfndata.com, self-registered with a work or school email, auto-approved.
  • Projector or screen share for the instructor demo.
  • The coverage browser tab, used before the demo to confirm how the chosen brands or categories appear in the designer field.
  • A prepared statement of the recency caveat, written on the board or handout, to be repeated at each point in the session where a chart or index reaches its most recent quarters.
  • For the optional extension only: an instructor class API key requested from info@altfndata.com, and the downloadable Python client (altfndata_client.py) or tutorials notebook (altfndata_tutorials.ipynb) at docs.altfndata.com.

Session outline (90 minutes)

  • 0 to 15 min: Introduce the idea of a transaction-based market index, contrast it with a listed-price index, and preview the recency caveat as the session's central discipline.
  • 15 to 25 min: Sandbox orientation. Confirm students can open a category table and locate sale_date, usd_price_decimal, and designer in the data dictionary.
  • 25 to 45 min: Guided demo, build a quarterly demand index for a single brand using date bucketing and a stable summary statistic.
  • 45 to 60 min: Guided demo, build the same index for a second brand and overlay the two, discussing what the comparison can and cannot support as a conclusion.
  • 60 to 75 min: Small-group exercise, each group builds a quarterly index for a different brand or category and marks, on their own chart, the exact quarter after which the recency caveat applies.
  • 75 to 85 min: Class discussion, groups present their index shapes and defend where they drew the recency cutoff.
  • 85 to 90 min: Wrap-up and homework assignment.

In-class demo (sandbox-first, no code)

  1. Open sandbox.altfndata.com, sign in, and select the fine art data table (or another category the instructor prefers) from the SQL editor dropdown.
  2. Open the coverage browser tab and confirm how a chosen artist or brand is written in the designer field before filtering.
  3. Return to the SQL editor and run the first guided query below, which buckets sold lots for one artist into calendar quarters and computes a median realized price per quarter.
  4. Open the pre-built charts tab and render the result as a line chart. Explain that this line is a quarterly demand index in miniature, a stable statistic tracked over time, not a single headline number.
  5. Point to the final one or two quarters and state the recency caveat plainly: these quarters are still being ingested, additional records will continue to arrive for them, and any apparent drop at the tail of the line should be treated as incomplete data, not as falling demand. Mark that cutoff visually on the chart, for example with a vertical line or shaded region.
  6. Run the second guided query, the same bucketing and statistic for a second artist or brand, and overlay both lines on one chart.
  7. Ask students what the comparison can support, for example that one brand's quarterly activity has been steadier than the other's over the stable period, and what it cannot support, such as a claim that one brand is appreciating faster than the other based on the most recent quarters.
  8. Run the third guided query, a sold-lot count by quarter for the same brand, as an alternative index built on volume rather than price, and discuss when a volume-based index and a price-based index might tell different stories.

Datasets and queries used

Dataset: fine art data (documented fields: designer, model, item_title, sale_date, usd_price_decimal, sale_estimates_high_usd_price, status, vendor, stock_ticker).

Query 1, quarterly median realized price index for a single artist:

SELECT date_trunc('quarter', CAST(sale_date AS date)) AS sale_quarter,
       approx_percentile(usd_price_decimal, 0.5) AS median_price_usd,
       COUNT(*) AS sold_lots
FROM all_fine_art_data
WHERE status = 'sold'
  AND designer LIKE '%Warhol%'
GROUP BY date_trunc('quarter', CAST(sale_date AS date))
ORDER BY sale_quarter;

Query 2, the same quarterly index for a second artist, to overlay on the first:

SELECT date_trunc('quarter', CAST(sale_date AS date)) AS sale_quarter,
       approx_percentile(usd_price_decimal, 0.5) AS median_price_usd,
       COUNT(*) AS sold_lots
FROM all_fine_art_data
WHERE status = 'sold'
  AND designer LIKE '%Basquiat%'
GROUP BY date_trunc('quarter', CAST(sale_date AS date))
ORDER BY sale_quarter;

Query 3, a volume-based alternative, sold-lot count by quarter for the same artist as query 1:

SELECT date_trunc('quarter', CAST(sale_date AS date)) AS sale_quarter,
       COUNT(*) AS sold_lots
FROM all_fine_art_data
WHERE status = 'sold'
  AND designer LIKE '%Warhol%'
GROUP BY date_trunc('quarter', CAST(sale_date AS date))
ORDER BY sale_quarter;

Optional API extension, retrieving the underlying sold-lot records to reproduce the quarterly bucketing in Python (POST /v1/tables/all_fine_art_data/query, header X-API-Key). The API returns rows rather than server-side date bucketing, so the quarterly grouping is computed client-side after paginating on offset:

{
  "fields": ["designer", "sale_date", "usd_price_decimal"],
  "filters": [
    {"field": "status", "op": "eq", "value": "sold"},
    {"field": "designer", "op": "like", "value": "%Warhol%"}
  ],
  "sort": [{"field": "sale_date", "direction": "asc"}],
  "limit": 1000
}

Discussion questions

  1. What does a quarterly median-price index built from auction transactions represent that a listed asking-price index would not, and what does it miss that a listed-price index would capture?
  2. Why is median realized price a more stable summary statistic for an index than a single record sale or a raw average, given how concentrated value can be among a small number of lots?
  3. Precisely where would you draw the line on an index chart between quarters you trust and quarters you flag as under-ingested, and how would you defend that choice to someone reading the chart?
  4. Two brands' index lines diverge sharply in their final two quarters. What is the more likely explanation given the recency caveat, and what would you need to check before ruling out an ingestion artifact?
  5. What is lost when a demand index is built on price alone, without also tracking sold-lot count or sell-through alongside it?
  6. If you were asked to brief a client on "how a brand's market has performed this year," how would this session change the way you answered that request?
  7. How would seasonality in auction calendars, for example concentrated spring and fall sale seasons, complicate a straightforward quarter-over-quarter reading of an index?
  8. What is the difference between using this index to compare two brands' historical patterns and using it to forecast either brand's future prices?

Homework assignment

Each student selects two brands or two categories represented in the same data table and builds a quarterly demand index for each, using either median realized price or sold-lot count as the summary statistic, consistently applied to both. The submission is a short report, no more than two pages, that includes the SQL queries used, an overlaid chart of the two index lines, an explicit marking of the quarter after which the recency caveat applies, and a written comparison of the two index shapes framed strictly as a methodological observation about relative pattern, with an explicit statement that the comparison is not a claim about which brand is appreciating. Grading criteria: correct quarterly bucketing and choice of a stable summary statistic (30 percent), explicit and correctly placed recency caveat (30 percent), quality and restraint of the comparative interpretation (25 percent), and clarity of the report (15 percent).

Going deeper

Key terms

  • Demand index: a time series built from a stable summary statistic, such as median realized price or sold-lot count, tracked across calendar periods.
  • Date bucketing: grouping transaction records into fixed calendar periods, such as quarters, using a SQL date-truncation function.
  • Recency under-ingestion: the newest quarters in the dataset have fewer records because ingestion is still catching up, not because market activity fell.
  • Reference period: the baseline quarter or period against which later values in an index are implicitly or explicitly compared.
  • Seasonality: a recurring pattern tied to the calendar, such as concentrated spring and fall auction seasons, that can shape an index independent of underlying demand.
  • Volume-based index: an index built on the count of sold lots per period, as distinct from a price-based index built on a price statistic.
  • Trailing artifact: a distortion that appears specifically at the most recent end of a time series because of how the underlying data was collected, not because of any real change in the world.
  • Overlay comparison: placing two time series on the same chart to compare their shapes directly, without claiming a causal or predictive relationship between them.

Common pitfalls

  • Reading a downturn in the final one or two quarters of an index as a market decline instead of as a recency ingestion artifact.
  • Using a single large sale to represent a whole quarter's price level instead of a stable statistic like the median.
  • Comparing two brands' index lines and concluding one is "beating" the other without acknowledging that the comparison covers different underlying volumes or different concentrations of value.
  • Bucketing by calendar year when quarterly resolution is needed to see the pattern the assignment asks for, or the reverse, bucketing too finely and over-reading noise in a low-volume quarter.
  • Building a price-based index without also checking sold-lot count per quarter, which can hide the fact that a quarter's median price is based on very few transactions and is therefore unstable.
  • Treating the index as a forecasting tool and projecting its trailing shape forward, rather than treating it as a description of the historical record only.

Additional queries to explore

-- Quarterly sell-through alongside the price index, to check whether low volume is driving instability
SELECT date_trunc('quarter', CAST(sale_date AS date)) AS sale_quarter,
       COUNT(*) FILTER (WHERE status = 'sold') AS sold_lots,
       COUNT(*) AS offered_lots
FROM all_fine_art_data
WHERE designer LIKE '%Warhol%'
GROUP BY date_trunc('quarter', CAST(sale_date AS date))
ORDER BY sale_quarter;

-- Annual instead of quarterly bucketing, for a coarser and more stable long-run view
SELECT date_trunc('year', CAST(sale_date AS date)) AS sale_year,
       approx_percentile(usd_price_decimal, 0.5) AS median_price_usd,
       COUNT(*) AS sold_lots
FROM all_fine_art_data
WHERE status = 'sold'
  AND designer LIKE '%Basquiat%'
GROUP BY date_trunc('year', CAST(sale_date AS date))
ORDER BY sale_year;

-- Category-level quarterly index, to compare a single brand's pattern against its broader category
SELECT date_trunc('quarter', CAST(sale_date AS date)) AS sale_quarter,
       approx_percentile(usd_price_decimal, 0.5) AS median_price_usd,
       COUNT(*) AS sold_lots
FROM all_fine_art_data
WHERE status = 'sold'
GROUP BY date_trunc('quarter', CAST(sale_date AS date))
ORDER BY sale_quarter;

Extension activities

  1. Rebuild the session's index at annual instead of quarterly resolution and discuss how the coarser bucketing changes both the visual stability of the line and how far into the recent past the recency caveat needs to extend.
  2. Add a sold-lot count series alongside the price index for the same brand, on a second axis or as a paired chart, and identify any quarter where a striking price move coincides with an unusually low lot count.
  3. Using the optional API extension, reproduce one of the session's indices in Python and compare the resulting values against the sandbox's SQL-computed version to confirm the two bucketing approaches agree.

Connections to other modules

This module is the direct sequel to Module 8, data visualization and storytelling, since every index built here must be charted with the same honesty discipline, particularly around the recency caveat at the trailing edge. It shares its point-in-time reasoning with Module 9, machine learning on auction data, where the same recency under-ingestion that threatens an index also threatens a naively chosen train/test cutoff. It also extends the pricing power and sell-through concepts from Module 7, the business of the art market, from single summary numbers into full time series.