Introduction: Arduino Airgun Chronograph With Live Excel 365 Data Streaming
3D Printed Arduino Nano Airgun Chronograph with 4x20 LCD display and Microsoft Excel 365 real time shot by shot analysis.
Barrel mounted 4x20 LCD display shows pellet velocity in Feet per Second including average, deviation, minimum & maximum.
The display also shows power in Foot Pound including average, deviation, minimum & maximum.
The chronograph and airgun connections are all 3D printed.
The chronograph is powered by 4 AAA batteries or USB.
Connecting a USB cable to a PC (not available on Apple devices) with Office 365 and running the enclosed spreadsheet allows detailed live shot by shot analysis of your Airgun performance.
The sketch has calculation ready for use in m/Sec and power in joules if required.
The display code and Excel sheet can be modified to suit.
Arduino sketch and further details can be found here
Supplies
Part Qty
4x20 Green/Yellow LCD display with I2C module 1
Plastic Cap 6x6mm Tactile Push Button Switch Hat Cover 1 pack
Tactile Tact Switch Momentary Tact 6x6x8mm 1 pack
OP240A Sensor Infrared (IR) EMITTER IR 890NM 50MA 2
OPL550A - Optical Sensor, OPL550 Series, Photologic®, Totem Pole 2
Pot 10K 17mm diameter 1
Power Switch latching 1
Potentiometer knob 14mm diameter 1
4x1.5V（6V） AAA Battery Holder 1
AAA (LR03) Batteries Alkaline 4
M2 x 5mm self tapper 17
M2 x 12mm self tapper 4
M2 x 12mm Bolt 3
M2 Nut 9
M2 x 5mm Bolt 4
M2 Washer 10
M3 Nut 10
M3 x 25mm Bolt 4
M3 Washer 8
M3 x 16mm Bolt 4
M3 x 30mm Bolt 2
Arduino NANO 1
Veroboard 1
Male & Female 2.54mm header plug & sockets as required
Resistor R1 and R1 150R 2
Diode 1N1004 1
Optional only if required All 3D printed part files can be downloaded from here
Pic 1 NANO mounted on Veroboard
Pic 2 LCD Display get the Green display as the backlight is not required in daylight
Pic 3 LCD rear showing I2C module
Pic 4 Infrared emmiter
Pic 5 Infrared receiver
Pic 6 Batt holder
Pic 7 10K pot max diameter Note the 10K Potentiometer should have a max diameter of 18mm to fit in the LCD housing.
Pic 8 power Switch
Step 1: Airgun Chronograph Demonstration
The animation and video above shows the chronograph in use connected to a PC running Excel 365.
The Excel graphs automatically build shot after shot.
The included sheet can be modified to use any of the features of Excel to analyse your guns performance.
Step 2: Airgun Connection
Connection to your airgun is possible by using off the shelf parts see this instructable for details.
3D printed barrel adaptors have been designed for standard 1/2inch UNF (Unified National Thread) barrels and also a universal slip on moderator fitting adaptor.
I have set this for my Remington Airacobra moderator but it can be changed in seconds to suit your moderator using the FreeCAD files if you download the 3D design files.
Details of how to do this on my site at the bottom of the 3D parts section.
There is also an adaptor for fitting over larger third party moderators.
Pic 1 completed threaded barrel connector constructed from 2 parts
Pic 2 customizable moderator adaptor
Pic3 customizable adaptor for larger moderator/silencers
Step 3: Power
The chronograph can be powered from 4 AAA (LR03) batteries or USB.
The chronograph draws 68mA with the LCD backlight off and 83mA with the LCD backlight on.
Using alkaline batteries this will give approx 10.5 hours use with the LCD backlight on and 13 hours use with the LCD backlight off.
Note I have used a Yellow/Green LCD display as the screen is visible in daylight with the LCD backlight off.
Pic 1 The mini USB connector from the Arduino Nano is available through a cut in the main body.
Pic 2 The batteries are house in the rear of the body.
Step 4: Controls
Controls
There are 8 controls on the chronograph 6 momentary push buttons, a latching button for battery power and a rotary control for pellet weight setting.
he momentary push buttons are labeled
RESET to reset the chronograph.
WEIGHT to adjust the pellet weight.
DOWN to adjust the pellet weight down and in results mode select display screen 1.
UP to adjust the pellet weight up and in results mode select display screen 2.
LCD B.LIGHT toggles the LCD backlight on or off.
START sets the chrono into measure mode waiting for a pellet to be fired.
There is a latching POWER button to turn the chronograph on or off in battery mode.
The is a rotary control to set the WEIGHT INCREMENT when setting the pellet weight.
Step 5: Microsoft Data Streamer Setup
Data Streamer
Pic 1 Data Streamer is a COM Add-in for Microsoft Excel that allows users to stream low latency data into Microsoft Excel from connected devices and apps.
Supported devices include microcontrollers such as Arduino Uno, micro:bit, Circuit Playground, MXChip Azure IoT DevKit, and any other serial capable device.
Data Streamer is a bidirectional data transfer utility that streams data between Excel and a microcontroller via the serial port.
This connection provides interactivity between a data source and the powerful calculation engine of Microsoft Excel. By using a set of commands and techniques users can design highly interactive Excel dashboards to visualize data and control program flow.
Enable Data Streamer Add-in
Data Streamer is pre-installed with Microsoft Excel but needs to be enabled.
To Enable Data Streamer in Excel:
Pic 2 Go to File > Options
In the Excel Options dialogue click Add-ins
At the bottom of the dialogue in the Manage: dropdown select COM Add-ins and click Go
Check the box for Microsoft Data Streamer for Excel
Pic 3 You should now see the Data Streamer tab in the Excel ribbon.
Requirements
Data Streamer comes with Excel 365 Desktop.
How the Data Streamer Works
Data Streamer connects to a data source which can be a serial device, such as the Arduino UNO microcontroller, or a Windows 10 app via a UWP (Universal Windows App) App Service.
The data source collects data and packages it into a CSV format and sends the data at a frequency to Data Streamer.
Data Streamer displays the data into an Excel worksheet. Data can also be sent from Excel to the device or app.
Pic 4 Once connected and the Start Data button is clicked the add-in will generate 3 worksheets: Data In, Data Out, and Settings.
Data In
Streaming data is injected into a range in the Data In worksheet. This range is updated whenever a new data packet is received.
Data Out
Data that is entered into the active range in the Data Out worksheet is packaged in CSV format and sent to the connected device or app.
Settings
Sets the parameters for the Data In and Data Out ranges and user settings. For more information go here.
Advanced Window
Use the Advanced Window to manage device settings, view the serial data console, or to change global user settings. For more information go here.
Sending Data To Your Sheet
In your code data is sent out using the Serial.print() command
Each item of data or channel must be separated by a comma. Serial.print(",");
Each line of data must be on a new line by using Serial.println();
In my Chronograph all my serial printing is completed under a function called SerialPrint()
counter_shots will be inserted into Ch1 & pWeight into Ch2 etc.
void SerialPrint() {
Serial.print(counter_shots);
Serial.print(",");
Serial.print(pWeight);
Serial.print(",");
Serial.print(fps);
Serial.print(",");
Serial.print(average);
Serial.print(",");
Serial.print(fpsMin);
Serial.print(",");
Serial.print(fpsMax);
Serial.print(",");
Serial.print(fpsDEV);
Serial.print(",");
Serial.print(energy);
Serial.println();
}
Step 6: Optional 3D Printed Parts
All the 3D printed parts to this project can be downloaded from my CULTs page if you don't want to use your own designs. A good example of off the shelf design can be found here .
There are 3 basic building blocks to my 3D design.
1 Pic 1 main body
2 Pic2 sensor housing
3 Pic 3 airgun connector
These building blocks are made up of many 3D parts some of which are shown above.
Pic 3 to 5 main body halves
Pic 7 threaded barrel connector
Pic 8 & 9 sensor housing
Full details of every part can be found here
Step 7: Schematic
Schematic
The circuit works by using a matched pair of high speed infrared transmitters. One pair on the start of the sensor housing and another pair 60mm along the sensor housing.
As a pellet breaks the link between the 1st pair of sensors a timer is started and is stopped as the pellet breaks the link on the 2nd pair of sensors.
This time is then used to calculate the velocity of the pellet.
Step 8: Veroboard Layouts
I have included Veroboard layouts. This is a quick and easy project to build on Veroboard but if you are not confortable with veroboard contruction a Nano expansion board should work OK.
I have designed 2 veroboard layouts for this project the main board and and the switch board.
Pic 1 to 3 show the main board layout
Pic 4 & 5 the switch board
Pic 6 show the boards in the main chronograph body
Step 9: Instructions for Use - Pellet Weight
Setting Pellet Weight
Pic 1
Press the power button or plug into a USB socket to turn on the chronograph.
When the main screen is loaded you can press the LCD B.LIGHT button to turn on the backlight.
Pic 2
To adjust the pellet weight press the WEIGHT button.
This will bring up the pellet weight adjustment screen.
Turning the WEIGHT INCREMENT knob will change the adjustment step value on the screen.
This value will be added to or taken off the displayed pellet value depending if the DOWN or UP buttons are pressed.
For example if you wanted to change the pellet weight from 9.57 to 9.48 grains.
Turn the WEIGHT INCREMENT knob until the adjust steps changes to 0.10.
Press the DOWN button to change the weight to 9.47.
Now turn the WEIGHT INCREMENT knob until the adjust steps changes to 0.01.
Press the UP button and the pellet weight will now show 9.48 grains.
To save this new weight just press the WEIGHT button.
The chrono will restart and the new pellet weight will be changed.
The pellet weight is saved in EEPROM and will be remembered on power-up.
Pic 3
Note display zeroing
A quick way to zero the display is to turn the WEIGHT INCREMENT knob all the way clockwise.
The adjust steps will show the same number as the pellet weight in this case 9.57.
Pic 4
Press the DOWN button will will make the pellet weight and adjust steps 0.
Step 10: Instructions for Use - Measuring Your Airgun Performance No PC
Once your pellet weight is set you can now measure your Airgun performance.
Checking without a PC connection
Pic 1 & 2
Connect the chronograph to the front of your airgun using an adaptor.
Press the power button to turn on the chronograph.
When the main screen is loaded you can press the LCD B.LIGHT button to turn on the backlight if required.
Pic 3
Press the START button and the display will change to "Awaiting shot"
Pic 4 & 5
The display will stay like this until a shot is fired or the RESET button is pressed.
When a shot is fired the display will briefly show the screen number 1 and the shot number followed by the 1st screen of the performance info.
Pic 6 & 7
Pressing the UP button will briefly show the screen number 2 and the shot number followed b the 2nd performance screen.
Pressing the DOWN button will return to the 1st screen.
Pic 3
To measure another shot press START.
Step 11: Instructions for Use - Measuring Your Airgun Performance Using a PC
Checking performance with a PC connection using Data Streamer to Excel 365
Pic 01
Connect the chronograph to the front of your airgun using an adaptor.
Plug a mini USB cable into the chronograph and into your PC to turn on the chronograph.
The power button can be left off to save the battery.
When the main screen is loaded you can press the LCD B.LIGHT button to turn on the backlight if required.
Pic 02
Load the sheet AirgunChrono06.xlsm
Click on the "Data Streamer" option on the top menu.
Click on the "Connect a Device" icon far left to show connected devices.
Select your device from the list.
Pic 03
Once your device is selected the "Start Data" icon that was greyed out will now go live.
The sheet will be cleared of data ready for the first shot.
Pic04
Click the "Start Data" icon.
"Start Data" will go greyed out and "Stop Data" will come live.
Pic 05
The sheet is now ready to accept data from the chronograph.
When the airgun is fired the data is recorded on the chronograph and the excel sheet.
Just press "START" on the chronograph to measure the next shot.
Once the series of shots are completed press "Stop Data" on the excel sheet.
Step 12: Control Panel Lettering
Transfer Lettering
I have used Inkjet transfer paper for the Contol panel lettering.
Pic 1
Print out the image onto the transfer paper.
Carry out instructions supplied with your paper (you may need to spay with clear varnish now or after applying).
Pic 2
Slide the transfer in place lining up the crosses to the center of the holes.
Pic 3
Once dry Seal the transfer with clear varnish
Pic 4
Remove transfer from the holes with a craft knife before giving a final coat of varnish.
