Filling Missing Stock Observations with tidyr and dplyr

Summary

The article explains how missing ticker-date observations can undermine quantitative analysis when data is stored in long form. Causes include new listings, trading halts, delistings and corporate actions; the examples focus on stocks with different starting dates or a missing trading day. These gaps can misalign date lookups and grouped or rolling calculations, so the article recommends making the intended date-by-ticker grid explicit and representing absent observations as missing values.

Its main method uses tidyr’s complete operation to add absent date-ticker combinations, retaining other variables as missing for those rows. As an alternative, it reshapes returns into a date-by-ticker table and back to long form; when the source contains additional fields, it joins those fields back from the original data. Small examples show both approaches producing equal row counts for each ticker. The article does not prescribe how to impute missing prices or returns, and a complete grid does not establish why an observation is absent or whether every ticker should be expected on every date. Researchers still need to define valid trading calendars and handle corporate actions appropriately.

Key ideas

• Missing ticker-date records can misalign grouped and rolling calculations.
• The complete operation adds absent date-ticker combinations and leaves their values missing.
• Reshaping between wide and long formats can expose gaps in a stock universe.
• Additional source variables can be restored by joining them after reshaping.
• An explicit grid helps check row counts but does not explain or impute missing values.

Tags

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.