Introduction: Walking or Running Club Mile Recorder
Does your elementary school or track club have a walking or running club? This under $200 DIY recorder will make tracking laps/miles an automated process rather than having to rely on volunteers or staff to organize punch cards, tally sheets, Popsicle sticks, or whatever your organization uses.
Here's what you'll need:
RFID Cards - Need to match same frequency as the scanner you buy. Mine are 125KHZ EM4100. Packs of 100 were ~$24 on Amazon.
RFID Scanner - I found a Generic USB 125KHZ EM4100/4200 RFID Proximity Card Reader for PC on Amazon for ~$13.
Laptop or Minibook- I had an older HP mini laptop that just needed a new battery and works just fine for this purpose. As long as it can run windows and excel, you don't need anything super nice or expensive.
Card Organizer - I used three education pocket calendars to hold all the cards and keep them organized. $24 for two 100 pocket calendars and one donated by one of the teachers in my building.
Time to input the initial data for all the users. I spent around 2-3 hours putting in the data and setting up the excel formulas.
Our school spent under $100 on this since we had a spare laptop already.
Step 1: The Technology
The cards work off of radio frequency. Each card has a unique number assigned to it and when placed next to the scanner, that unique number is recorded on a computer. Think of the scanner being like a keyboard and instead of pressing keys, it sends a number to whatever word processing program you have open. As long as you have a text dialog box open while scanning a card, it will record the card code and move to the next line every time you scan.
Step 2: Equipment Setup
I placed my scanner and mini laptop in a storage container with the scanner on the very bottom so that students can still see the light of the scanner. When they place their card by the scanner, it will beep and the light changes to green. This is then hung on a fence next to all the cards so the kids can easily grab their cards and then scan on each lap they do. The scanner can read cards to up to 2-3 inches away and needs to be flat against the scanner to ensure it reads the card. Mine is able to still read cards even through a plastic bin.
Step 3: The Software
Using Excel to have everything automated will make things a lot easier. Once everything is set up, all you have to do is copy the list from the laptop and paste that into your second sheet in the main Excel workbook.
On my main sheet I put the students name in column A, the unique card code in column B (this can be done easily by just attaching the scanner and scanning each card), column C has the students Name that I want on our leader board (makes it easy to just copy and paste when updating), and column D with the formula:
This will check the numbers on the pasted sheet and count how many instances of each number listed in column B, then divide it by 4 to give you how many miles. Our path is 4 laps for a mile so it makes it easy in calculating the exact mileage.
On the Excel sheet that you have in the laptop to record laps, you'll just need to make sure the cursor is in the cell you want to start with before you start scanning. It will automatically move the the next lower cell after each scan and it will overwrite existing data if you don't place it where you want it to start. I use the following formula to put a timestamp on each scan:
This ensures that multiple scans per lap can be accounted for and taken out with a quick glance over. You'll need to enable iterative calculations in Excel which can be done by going to File>Options>Formulas and then selecting Enable Iterative Calculations. I mark it up to 10,000 just in case the data file gets fairly large. I'm still working on a formula that can tell how far apart each scan is and will highlight if the same card is scanned within 30 seconds of the last time it was scanned. For now, I just eyeball the results and delete the duplicates that are obviously multiple scans on the same lap.
Your Excel sheets are now all set up and ready for you to start collecting scans!
I've heard of these similar set ups costing districts in the upwards of $10,000. If you have some tech savvy and time you can set up and maintain a system that no longer needs to be continually monitored. Our lunch recess duties keep an eye on it to make sure kids are using it correctly and a quick check every once and a while makes this system a lot less labor intensive that having to manually keep track of laps. It could also be used for a track program to log lap times or mileage.
Best of luck!