Rockridge Trapezoid Telraam Sensors

Our focus is on data. To support that—and to help you stay focused on the numbers too—we've made it straightforward to get the figures yourself, without having to take our word for it.

We lay this out in three layers: chart views on Telraam's public pages (any 14-day period you select), our consolidated Excel file with the complete dataset, and—when you want to rebuild everything from scratch—Python scripts against Telraam's API.

Background

What is Telraam?

Telraam is like PurpleAir for traffic: neighbor-hosted sensors that continuously count and classify road users (cars, bikes, pedestrians), then publish the data publicly. A sensor sits in an upper window, connects to the internet, and streams counts to Telraam's cloud platform. The household powers it and provides connectivity—Telraam's servers handle everything else.

The data comes straight from Telraam's platform. We don't control it, the neighbor hosting the device doesn't control it, and no one can edit historical counts after they're published. What you see on Telraam's public pages is what we download and summarize in our workbook.

Layer 1 · On the web

Public Telraam pages

Each sensor has a public page where you can select any 14-day window and view traffic counts in chart form. Choose your dates, click Update, and the chart refreshes with data for that period.

This is the fastest way to spot-check: pick a date range you care about and see what the sensor reported.

Motorized daily totals from Telraam data.

Martin Street motorized traffic by day Fourteen bars, daily motorized totals. 0 2k 4k 3,597 vehicles3,597 3,734 vehicles3,734 2,593 vehicles2,593 2,324 vehicles2,324 3,369 vehicles3,369 4,143 vehicles4,143 4,105 vehicles4,105 3,652 vehicles3,652 3,707 vehicles3,707 2,911 vehicles2,911 2,285 vehicles2,285 3,261 vehicles3,261 3,322 vehicles3,322 2,692 vehicles2,692 Apr 23 24 25 26 27 28 29 30 May 1 2 3 4 5 6 Martin Street · Telraam map
Layer 2 · Workbook

Download the consolidated Excel file

Our workbook contains the complete dataset from all three sensors. Open it in Excel, Numbers, LibreOffice, or Google Sheets.

Excel Data Archive (adt_summaries_v7.xlsx)

How it's organized

The workbook has ten sheets: one Summary sheet and three sheets per sensor (Martin, Hillegass, Colby).

Summary sheet: Calculates key metrics across all three sensors—weekday averages, peak weeks, busiest single days, and percentiles. Each cell shows the formula used, so you can click in and trace where the numbers come from.

For each sensor, three sheets:

  1. [Sensor]_data — Hourly raw counts straight from Telraam's API. Each row is one hour: timestamp, pedestrian count, bike count, car count, large vehicle count, and speed data.
  2. [Sensor]_daily — Daily totals. Each row is one calendar day. The formulas sum all hourly records from the _data sheet for that date (using SUMIFS to match the date range). Also calculates weekday number (1 = Monday through 7 = Sunday) and assigns an ISO week key (like 2025-W33).
  3. [Sensor]_weekly — Weekly averages. Each row is one ISO week (Monday-to-Sunday). The formula averages all daily motorized vehicle counts from the _daily sheet that match that week key.

The Summary sheet pulls from these daily and weekly sheets—weekday averages use AVERAGEIFS to filter Monday–Friday only; peak weeks use MAX on the weekly averages; busiest single day finds the highest daily total.

Cross-checking the workbook

Find a value you want to verify. Open the matching sensor on Telraam (Layer 1), select a 14-day span covering the same dates, click Update, and compare the chart to the workbook. You're validating our copy against Telraam's live data.

The formulas are standard Excel—no macros, no hidden logic. Click into any cell to see how it calculates. If something isn't clear: [email protected]

Layer 3 · Developers

Python: rebuild the workbook from scratch

Layers 1 and 2 let you view and verify the data. Layer 3 lets you generate it yourself using Telraam's API.

We publish Python scripts that authenticate to Telraam's API, pull all traffic reports, write CSVs, and regenerate the consolidated Excel file using our same pipeline.

All code lives under telraam_code/ in our GitHub repository:

telraam_code/ on GitHub

Files

FilePurpose
telraam_support.py Shared helpers for API requests, CSV handling, path resolution
telraam_pull_martin.py
telraam_pull_hillegass.py
telraam_pull_colby.py		 Fetch data from Telraam API → daily and hourly CSVs
telraam_merge_adt_workbook.py Merge hourly CSVs into existing workbook template
telraam_rebuild_adt_workbook.py Build fresh workbook from daily CSVs (no template needed)
local_config.example.json Configuration template—copy and customize

Setup

  1. Configure your data directory via TELRAAM_DATA_DIR environment variable or telraam_code/local_config.json
  2. Add your Telraam API token via TELRAAM_API_KEY or ~/.config/telraam/telraam
  3. Install dependencies: pip install openpyxl requests (Python 3.10+)

Under your data directory, pull scripts write CSVs to API_Data/.

Typical workflow

cd /path/to/lowerrockridgeslowstreets.github.io
python3 telraam_code/telraam_pull_martin.py
python3 telraam_code/telraam_pull_hillegass.py
python3 telraam_code/telraam_pull_colby.py

# Option A: Update existing workbook with hourly data
python3 telraam_code/telraam_merge_adt_workbook.py

# Option B: Build fresh workbook from daily CSVs
python3 telraam_code/telraam_rebuild_adt_workbook.py

Questions about the scripts: [email protected]