DEV Community: Wishbone-Data

One table of US grid interconnection queues: solar, storage, wind and data center requests

Wishbone-Data — Sat, 10 Oct 2026 00:55:57 +0000

Every new solar farm, battery, wind project, gas plant and large load (data centers, chip fabs, hydrogen) has to file an interconnection request with its grid operator before it can connect. The queues are public. For anyone selling into energy construction, they are a pipeline list: project size, location, developer, status and the date the developer expects to come online.

The problem is that each operator publishes its queue differently.

Why combining queues is tedious

Each ISO uses its own spreadsheet layout, sheet names and column headers.
Fuel types and statuses are coded, and the codes differ by operator. NYISO, for example, uses short codes for both fuel and study status.
Some files split active, withdrawn and completed projects into separate sheets.
The file locations and names change with each release.

Doing it by hand

Download the latest queue file from each operator you care about (CAISO's public queue report, ERCOT's monthly GIS report, NYISO's interconnection queue, SPP's active queue).
Map each operator's columns to one schema: MW, fuel, state, county, point of interconnection, developer, status, queue date, proposed commercial operation date.
Decode the fuel and status codes using each operator's own legend.
Stack the results and keep a column saying which ISO each row came from.

Doing it in one run

I packaged those steps as an Apify Actor: US Power Grid Interconnection Queues. It reads the latest file from CAISO, ERCOT, NYISO and SPP, decodes the codes, and returns one normalised table you can filter by ISO, state, fuel and size, then export to CSV or Excel.

How to read a queue

Most queued projects never get built; many withdraw after studies show what the grid upgrades would cost. Treat a queue entry as a lead to watch, and give more weight to projects with a signed interconnection agreement or a construction start date than to new requests.

Finding FEMA-funded rebuild and mitigation projects before they go to bid (OpenFEMA)

Wishbone-Data — Sat, 10 Oct 2026 00:54:13 +0000

After a declared disaster, FEMA obligates money to cities, counties, utilities and other public applicants to repair roads, bridges, water systems and public buildings. Separately, hazard mitigation programs fund elevations, drainage, generators, seismic retrofits and wildfire work, often months before anything is advertised for bid.

All of it is public through the OpenFEMA API. For contractors and engineering firms that work in the public sector, it is a list of agencies that have federal money to spend.

The two datasets that matter

Public Assistance (PA) funded projects: every obligated repair or replacement project after a declared disaster, with the damage category, county, project amount and federal share.
Hazard Mitigation Assistance (HMA) sub-applications: HMGP, BRIC, FMA and related programs, including the project status (for example pending, submitted to FEMA, approved). The early statuses are the interesting ones: those projects have not been bid yet.

What makes the raw API awkward

PA project records carry an applicant ID rather than a name, so you need a second lookup to learn which agency it is.
The two datasets use different field names for the same ideas (state, county, amount, status).
Results are paged, and a useful pull for a single state can run to tens of thousands of rows.

Doing it by hand

Query the PA funded projects and HMA sub-application endpoints at fema.gov/api/open with a filter on state and date.
Page through the results with $skip and $top.
Join PA records to the applicants dataset to get agency names and addresses.
Normalise the field names so both datasets fit one table.

Doing it in one run

I packaged those steps as an Apify Actor: FEMA Disaster Recovery & Mitigation Projects. It pulls both datasets, resolves applicant names, and returns one flat schema you can filter by state, program, status and amount, then export to CSV or Excel.

A note on using it well

An obligated amount is money FEMA has committed, not a contract you can bid on tomorrow. Use the list to find the agencies and projects worth watching, then follow each agency's own procurement notices for the actual solicitation.

US building permits by county and city, as one tidy table (Census Building Permits Survey)

Wishbone-Data — Sat, 10 Oct 2026 00:53:12 +0000

If you sell building products, build homes or lend on construction, monthly building permit counts are one of the earliest demand signals you can get. The US Census Bureau publishes them for every state, county and permit-issuing place through the Building Permits Survey (BPS), free.

The catch is the format.

Why the raw files are hard to use

BPS is published as dozens of fixed-layout text files per month, one per geography level and region.
Columns are positional and split by structure type: 1 unit, 2 units, 3-4 units and 5+ units, each with buildings, units and valuation.
State files report valuation in thousands of dollars while place files report dollars, so a naive join gives you numbers that are off by 1,000x.
The newest month appears with a lag, and you have to work out which month is the latest one published.

Doing it by hand

Browse the BPS data directory on www2.census.gov and pick the geography (state, county, place, metro) and period (monthly, year-to-date, annual).
Parse the fixed-width columns using the layout notes for that file type.
Convert state valuations from thousands to dollars before comparing them with county or place figures.
Add FIPS codes so the table can be joined to anything else you have.

Doing it in one run

I packaged those steps as an Apify Actor: US Building Permits by City, County & State. It finds the latest published month, downloads every file for the periods and geography you choose, and returns one row per place per period with named columns such as units1, units5plus and totalValueUsd, already in dollars. You can filter by state, by name and by a minimum number of units, and export to CSV or Excel.

Reading the numbers sensibly

Permits are authorisations, not starts or completions, and some places issue none at all, so a missing small town is not the same as zero construction. Single-family and 5+ unit counts move for different reasons, so look at them separately rather than only at the total.

Davis-Bacon prevailing wage rates as a spreadsheet: pulling SAM.gov wage determinations by county

Wishbone-Data — Fri, 09 Oct 2026 23:53:54 +0000

Anyone who bids federally funded construction in the US runs into Davis-Bacon wage determinations. Each one is a long text document on SAM.gov listing every craft (electricians, laborers, operators and so on) with a base hourly rate and a fringe rate for a given state, set of counties and construction type.

Estimators usually open the determination, scroll through dozens of classifications and retype them into a spreadsheet. When a modification is published, they do it again.

What a wage determination contains

Construction type: Building, Heavy, Highway or Residential. The same county can have four different determinations.
Rate identifiers: each rate block is tagged as a union rate (the identifier starts with the union's code), a survey rate (SU...) or a union average (UAVG...). That tells you where the number came from.
Modification number and effective date: rates change when a new modification is published, so the mod number matters as much as the WD number.

Doing it by hand

Go to sam.gov, open Wage Determinations, and search Davis-Bacon by state, county and construction type.
Open the active determination and note the WD number and modification.
Copy each classification, base rate and fringe into your sheet.
Re-check the determination named in the contract documents before bid day.

Doing it in one run

I packaged the lookup and the parsing as an Apify Actor: Davis-Bacon & SCA Prevailing Wage Determinations. Search by state, county and construction type, or give it exact WD numbers. Each output row is one classification with craft, rate, fringe, rateIdentifier and effectiveDate, ready to export to Excel or CSV. No SAM.gov account or API key is needed.

A caution worth repeating

The determination that governs a job is the one incorporated in the contract, not whatever is current on the day you look. Use a pulled sheet to build the estimate quickly, then confirm the WD number and modification against the bid documents.

How to pull every NOAA hail report near an address (free data, no API key)

Wishbone-Data — Fri, 09 Oct 2026 23:51:53 +0000

If you run a roofing or restoration company, you have probably paid for a hail map. The raw data behind most of them is public: the NOAA Storm Prediction Center (SPC) publishes a CSV of every hail, damaging-wind and tornado report for every day since 2004.

The files are awkward to use directly:

There is one file per day per hazard.
Hail size is stored in hundredths of an inch (175 means 1.75").
An SPC "day" runs from 12:00 UTC to 11:59 UTC the next day, so a report logged at 01:00 UTC carries the previous day's date.

Doing it by hand

Download the daily files from https://www.spc.noaa.gov/climo/reports/ for the dates you need (YYMMDD_rpts_hail.csv, _wind.csv, _torn.csv).
Divide the Size column by 100 to get inches.
For any report timed between 0000 and 1159 UTC, add one day to get the real calendar date.
Filter to your state, or compute distance from your office with the haversine formula:

from math import radians, sin, cos, asin, sqrt

def miles(lat1, lon1, lat2, lon2):
    lat1, lon1, lat2, lon2 = map(radians, (lat1, lon1, lat2, lon2))
    a = sin((lat2 - lat1) / 2) ** 2 + cos(lat1) * cos(lat2) * sin((lon2 - lon1) / 2) ** 2
    return 3958.8 * 2 * asin(sqrt(a))

Doing it in one run

I packaged those steps as an Apify Actor: NOAA Hail, Wind & Tornado Storm Reports. Give it a date range, a state or a point and radius, and a minimum hail size. It returns one row per report with hail size in inches, wind in mph, UTC time, coordinates, county, state and distance in miles. You can export the result as CSV or Excel.

What the data can and can't tell you

These are ground reports from storm spotters and National Weather Service offices, not radar estimates. Dense areas are well covered; storms over open country can go unreported. For checking a customer's date of loss that is usually what you want, because a logged report is evidence an adjuster recognises. For mapping every square mile a storm touched, you would need radar-derived hail swaths instead.

Questions about the data or the edge cases (the UTC day boundary catches most people) are welcome in the comments.

1:30 AM Happens Twice on November 1. Most AI Models Picked One and Moved On.

Wishbone-Data — Wed, 30 Sep 2026 00:52:53 +0000

This is a submission for the Kaggle Benchmarking Challenge

What I Benchmarked

Time zones look like arithmetic. Add some hours, maybe cross midnight, done.

They aren't. Twice a year a local time disappears or happens twice. The US and Europe change their clocks on different weekends, so for a few weeks "New York is five hours behind London" is wrong. Some places sit on :30 or :45 offsets. Some countries changed their rules in the last few years, so a model trained on older text can be confidently out of date.

So I built Wall-Clock Traps: 59 scenarios, each asked two ways.

Clean: "Local date and time in Chicago, USA: 2026-03-08 02:30. What is the local date and time in London, UK at that same moment?"
Messy: "Backup job on the Chicago server is set for 2:30 am local on Sunday March 8th. London team wants to watch it run. What time is that in London?"

Same facts, same answer. (The answer is that 2:30 am never happens in Chicago that night. The clocks jump from 2:00 to 3:00.)

That gives 118 items in nine groups: basic conversions, US/EU gap weeks, odd offsets (Nepal, Chatham Islands, Lord Howe Island), the date line, flights that land on a clock-change day, countries that changed their rules recently, times that never happen, times that happen twice, and controls that look like traps but aren't.

Design choices that mattered:

The answer key is code, not me. Python's zoneinfo computes every answer from the IANA time zone database. I checked 25 by hand.
Exact-match grading. Every prompt asks for a last line like ANSWER: 2026-03-16 14:00, or ANSWER: NONEXISTENT / ANSWER: AMBIGUOUS. No LLM judge.
Wrong answers get sorted. Off by exactly an hour is a DST mistake. Off by a day is a date-line mistake. Matching a country's old rule is a stale-knowledge mistake. Flagging a valid time as impossible is a false alarm.
Four messy prompts carry a bad hint on purpose, like a CFO who "always just adds five hours."

Models Tested

Gemini 3.7 Flash (Kaggle's default model)
Gemma 4 26B A4B (small open-weights model)
Claude Sonnet 4.5
Gemini 2.5 Flash
Claude Haiku 4.5
GPT-5.4 mini

Same prompts for everyone, one attempt per item. I ran the benchmark twice: once on the Kaggle leaderboard, and once in an analysis notebook that keeps every answer so I could see why models missed.

Findings

Leaderboard (run 1):

Model	Accuracy
Gemini 3.7 Flash	100%
Gemma 4 26B A4B	96%
Claude Sonnet 4.5	91%
Gemini 2.5 Flash	88%
Claude Haiku 4.5	73%
GPT-5.4 mini	66%

Full breakdown (run 2):

Model	Accuracy	Clean	Messy	Traps caught	False alarms	Stale-rule errors
Gemini 3.7 Flash	100%	100%	100%	18/18	0/12	0/16
Gemma 4 26B A4B	97%	97%	97%	17/18	0/12	2/16
Gemini 2.5 Flash	91%	88%	93%	11/18	0/12	4/16
Claude Haiku 4.5	78%	80%	76%	9/18	1/12	10/16
GPT-5.4 mini	68%	75%	61%	7/18	1/12	5/16

Claude Sonnet 4.5 is missing from run 2. 102 of its 118 calls came back as API errors, so the row would measure the API, not the model. Its leaderboard score from run 1 stands.

1. "Happens twice" is the real trap. "Never happens" mostly isn't.

When a time doesn't exist (spring forward), nearly every model noticed: 100% for three of the five, 80% for Haiku, 60% for GPT-5.4 mini. When a time happens twice (fall back), the picture flips. Gemini 3.7 Flash got all of them and Gemma got 88%. Gemini 2.5 Flash, Haiku and GPT-5.4 mini each got 12%. They did the math on one of the two possible moments and gave a confident answer. The hardest single item in the set was 1:45 am on Lord Howe Island, where the clock only moves back 30 minutes. Almost nobody flagged it.

2. Models still carry old rules for Egypt, Greenland and Paraguay.

The "changed rules" group split the field too. Haiku gave the old answer on 10 of 16 items, GPT-5.4 mini on 5, and Gemini 2.5 Flash on 4. The worst was Paraguay, which dropped its winter clock change in 2024: most models got the July time in Asunción wrong. Egypt (DST back in 2023) and Nuuk, Greenland (new offset in 2023) tripped up several models too. These aren't math mistakes. The models know a rule, just not the current one.

3. Messy wording hurt the weak models and didn't touch the strong ones.

The top two scored the same clean and messy. GPT-5.4 mini dropped 14 points (75% to 61%) and Haiku 4. Oddly, Gemini 2.5 Flash did better on messy (93% vs 88%). The planted hints worked on some: the "just add five hours" message (New York to London during the gap week) flipped two models from right to wrong.

Accuracy by group (run 2):

Model	Basic	US/EU gap	Odd offset	Date line	Flight	Changed rules	Never happens	Happens twice	Control
Gemini 3.7 Flash	100%	100%	100%	100%	100%	100%	100%	100%	100%
Gemma 4 26B A4B	100%	100%	94%	100%	100%	88%	100%	88%	100%
Gemini 2.5 Flash	100%	100%	100%	100%	100%	75%	100%	12%	100%
Claude Haiku 4.5	100%	100%	88%	83%	94%	38%	80%	12%	83%
GPT-5.4 mini	100%	58%	69%	75%	75%	69%	60%	12%	58%

Where the wrong answers came from (run 2, counts):

Model	Missed trap	Off by 1 hour	Stale rule	Wrong day	False alarm	Other
Gemma 4 26B A4B	1	0	2	0	0	1
Gemini 2.5 Flash	6	0	4	0	0	1 (format)
Claude Haiku 4.5	9	4	10	0	1	2
GPT-5.4 mini	11	11	5	1	1	9

What surprised me

Thinking time bought accuracy almost one for one. GPT-5.4 mini wrote about 80 tokens per answer and was the cheapest run ($0.06 for all 118 items). It was also the only model that failed plain US/EU gap-week conversions, the kind of thing you'd put in a calendar invite. Gemma, a much smaller open model, wrote about 2,500 tokens per answer and scored 97%. The other surprise was run-to-run noise: Haiku scored 73% on the leaderboard and 78% on the second pass, and GPT-5.4 mini 66% then 68%, with the same prompts. A single run isn't the whole story.

What I'd measure next

Give the models a time zone tool and see whether they call it or still do the math in their head.
Ask about dates in 2028 and 2030, where a rule change could land after the training data.
Run each item several times. A model that's right 60% of the time on a trap isn't the same as one that's right every time.

My Benchmark

Kaggle benchmark: https://www.kaggle.com/benchmarks/wishbonedata/wall-clock-traps

Task (code, answer key and grader): https://www.kaggle.com/benchmarks/tasks/wishbonedata/wall-clock-traps

Everything is deterministic. The answer key is rebuilt from the time zone database every time the notebook runs, and the notebook prints a fingerprint so you can check whether your machine's time zone data gives the same key.