How to Use Shiller Data (ie_data.csv) for Retirement Backtesting

Step 1: Download the Bellavia Template

Before working with external data, grab Bellavia's CSV template to understand the required format:

1. Log into your Bellavia account
2. Navigate to Settings from the top menu
3. Scroll to the Data Management section
4. Click Download Sample CSV Template

This template shows exactly how Bellavia expects your data to be structured.

Step 2: Access the Shiller Database

The Robert Shiller data is freely available from Yale University:

1. Visit the official database: http://www.econ.yale.edu/~shiller/data.htm
2. Look for the section titled "Stock market data used in my book, Irrational Exuberance"
3. You need the dataset: "U.S. Stock Markets 1871-Present and CAPE Ratio"
4. Click the download link at https://shillerdata.com/ (look for "ie_data Download")
5. An Excel file will automatically download to your computer

The Shiller dataset as it appears when first downloaded from Yale University

The downloaded file contains monthly stock prices, dividends, earnings data, and the Consumer Price Index starting from January 1871.

Step 3: Extract the Required Columns

Open the downloaded Excel file. You'll see multiple tabs and columns, but Bellavia only needs specific data.

From the Data tab, you need these four columns:

• Column A: Date
• Column E: Consumer Price Index (CPI)
• Column J: Real Total Return Price
• Column S: Real Total Bond Returns

To extract this data:

1. Create a new sheet in the workbook
2. Copy these four columns to your new sheet
3. Use Paste Special > Values to paste as values (not formulas)

The relevant columns from the Shiller dataset in a separate workbook

Step 4: Convert to Annual Data

Bellavia uses annual data points, but the Shiller dataset provides monthly data. You need to select one data point per year.

A simple approach is to use January values (December may be a better choice but for our purposes the end difference is small):

1. Select all four columns
2. Apply an AutoFilter (Data > Filter)
3. In the Date column filter, search for .01
4. This displays all January entries (shown as YYYY.01 in the Shiller format)
5. Copy all filtered rows
6. Paste them into a new location as values

You should now have annual data spanning from 1871 to 2025.

The relevant columns from the Shiller dataset with dates in years

Step 5: Clean Up the Date Format

The Shiller dates are in "YYYY.MM" format, but Bellavia needs just the year:

1. Select the Date column
2. Use the Excel formula: =LEFT(Cell, 4) to extract just the year
3. Copy the formula down for all rows
4. Copy and paste as values to replace the original dates

Your Date column should now show: 1871, 1872, 1873, and so on.

Step 6: Add Cash Returns and Rename Columns

Now add the cash returns column and ensure column names match Bellavia's requirements exactly:

1. Add a new column for Cash total return index
2. Fill it with the value 100 for all rows (representing cash keeping pace with inflation)
3. Rename the columns to match these exact names:
   • Year (formerly Date)
   • Equity_TR (formerly Real Total Return Price)
   • Bond_TR (formerly Real Total Bond Returns)
   • Cash_TR (your new column with 100 values)
   • CPI (formerly Consumer Price Index)

Data before upload

Your final spreadsheet should have five columns with these exact headers and annual data from 1871 to 2025.

Step 7: Save as CSV

Excel files won't work—Bellavia requires CSV format:

1. Click File > Save As
2. Choose CSV (Comma delimited) (*.csv) as the file type
3. Name your file (e.g., "Shiller_Data_1871_2025.csv")
4. Click Save
5. If Excel warns about features not compatible with CSV, click Yes to continue

Step 8: Upload to Bellavia

Now you're ready to upload your custom dataset:

1. Return to Bellavia Settings
2. In the Data Management section, find Upload Custom Data
3. Click Select Data File (CSV)
4. Choose your CSV file
5. Enter a display name (e.g., "Shiller Data")
6. Select USD - US Dollar ($) as the currency
7. Tick the box: My data is in real (inflation-adjusted) terms to match the data format
8. (Optional) Add a description like "Robert Shiller data 1871-2025"
9. Click Upload Data

Bellavia will validate your data before uploading. If there are any format issues, you'll see specific error messages.

Step 9: Use Your Custom Dataset

Once uploaded, your Shiller dataset is ready to use:

1. Go to the Bellavia Home page (main simulator)
2. In the Portfolio & Timeline section, click the Equity Index dropdown
3. Your custom "Shiller Data" dataset will appear in the list
4. Select it and run your simulation

You can now test retirement scenarios across 150+ years of real market history, including all major financial crises, wars, and market conditions.

Conclusion

With the Shiller dataset uploaded, you've significantly enhanced Bellavia's analytical power. You can now run simulations across 150+ years of market history, stress-test your retirement plan against every major economic crisis, and gain confidence that your strategy works in both bull and bear markets.

Ready to run your first simulation with the Shiller data? Head to the Home page and select your newly uploaded dataset from the equity index dropdown.

Frequently Asked Questions

Continue reading

• Historical 90% and Monte Carlo 90% Are Not the Same Number
• Monte Carlo vs Historical Simulation: Why They Disagree and Why That's Fine
• The Ultimate Guide to Historical Market Data for Financial Planning