Logging for field events – paper logs and spreadsheets

I use spreadsheets to record my field day log.  I don’t need to use a computer in the field – I work with computers all day and the last thing I want on a field day is yet another day of working a computer.  A paper log is quite good enough, and avoids odd things happening due to incorrect program logic, or wrong callsign recording.

In the John Moyle Memorial field day rules you need to record the grid locators of stations worked on vhf/uhf. Then you need a column for distance, then you need to work out the points earned by that contact.

I use Tiny Locator, a nice little application from ON6MU, to calculate distances from grid squares.  There are spreadsheet add-ins available for Excel, but I decided to just use an external tool and copy/paste its results into my spreadsheet. By doing the calculations only once for each callsign, the process is cut down to the minimum effort required.

I copy all the callsigns worked, with their locator codes as recorded during the contest, into a new worksheet called Grids.  Then I sort by callsign and remove the duplicates.  Then I do all the distance calculations with the help of Tiny Logger and record the distance for each station worked.

Back in the log page itself I then insert formulas into the Grid locator, distance and points earned cells of each row.

The grid locator can either be the original locator recorded during the contest, or the data copied from the Grids worksheet for that callsign.  Similarly for the distance and points data.

The formula to copy the grid locator for any callsign in the Grids sheet is =VLOOKUP($E2;$GRIDS.$A$1:$C$28;2) for OpenOffice sheet users, or =VLOOKUP($E2,$GRIDS!$A$1:$C$28,2) for Excel.

Similarly you can get the distance using the next column of the Grids sheet.

The points are calculated using a similar technique.  The score table is set up in another sheet, in the following format.

0 2
49.9 5
99.9 10
149.9 20
299.9 30
9999 50

The formula to look up the points for a distance is: =VLOOKUP(K2;$Scoretable.$A$1:$B$6;2) for Openoffice.org Spreadsheets.
Here $K2 is the cell containing the distance for the contact made. Scoretable is the sheet containing the table above in cells A1 to B6. Column 2 in the scoretable is the column containing the points to be scored. VLOOKUP returns the row containing the distance that is equal to the specified search value.  If that exact value cannot be found in column 1 of the table, it matches against the first row found that is greater than the value sought.  Thus a distance of 80 is greater than 49.9 but less than 99.9, so the row chosen is the one with a distance of 99.9, allocating 5 points to the contact.

You could continue to refine the formulas and the fine details, such as dealing with decimal points of distance correctly.  eg. what is the score for a distance of 99.999 km?  The simple table and formulas used would give the wrong result and strictly speaking the scoring table specified in the rules does not state the score for distances between 49 and 50 km, etc.  However as a way of saving typing and avoiding keying errors in distances, this approach works well enough.  Rounding the distances appropriately is also something to consider.

Summary

Using a combination of plain spreadsheets and formulas, you can ensure the grid locator, distance and point score for each contact is consistently recorded in your log sheet.

New antennas for 1296 MHz

I received the two 18 element yagis for 1296 Mhz and they appear to be strongly made. We will see whether they are any better than the previous antenna.  I have borrowed a splitter for them and will use that for the Summer field day in mid January, unless I can make my own before then.

I found a website offering data on stacking distances for yagis.  For a boom length of 1.5m on this frequency the stacking distance recommended is 2.4 wavelengths, which works out at 55cm (23cm wavelength).  The feedlines on the yagis are about 25 cm long but that’s not long enough to reach the power divider, so additional connectors and cable will be needed, eating into the stacking gain.

Next step for 23cm is to configure the power amplifier so it can be operated remotely from the tent.  I read some comments on the UK microwave reflector about power levels from IC910H not being up to the 10w level expected.  Time to get out the power meter and check mine out.

2.4 GHz transverter success

The 2.4 GHz transverter has been completed and I used it in the vhf/uhf field day on the weekend of 15/16 November.

Construction began on Sunday 9 November with the first components soldered onto the sequencer board, finishing on Friday 14th November when I finally measured the transmitter output power.

The next day I was on Mt Ginini, with a 24 db gridpack dish (obtained from The RF Shop) attached to the transverter, receiving a big signal from Ted VK1BL at Mt Coree.  I replied to his call and asked how he was hearing me.  He replied immediately! This was my first contact on 2.4 GHz, made all the sweeter by the knowledge that the transverter was home made.

Together with some other “firsts” on this field day, it made for a very good weekend.

I have written up the details of the design and construction and submitted it as an article for AR magazine.  The article was published in July 2009 and can be found in the AR archives here.

The 13cm transverter under test
The 13cm transverter under test

Preparing for spring VHF/UHF field day, November 15/16

This week’s project is to prepare for next weekend’s VHF/UHF field day.

I plan to operate from a mountain southwest of Canberra, with equipment for 50, 144, 432, 1296, 2403 and 10368 MHz.  The first four bands are bands I have used before but the last two are new for my station.  The 2403 equipment will be a simple transverter driven by a FT290R radio on 144.  The antenna for that band will be a grid type dish.

The 10368 MHz equipment has been borrowed from another local amateur.  It is a transverter driven by an Icom IC202 on 144, the antenna is a dish.  It all mounts on a tripod which readily allows azimuth and elevation adjustements.

On all bands the main mode used will be voice, using upper sideband.  For some contacts with more distant stations, morse (CW) will be used as it is much easier to hear weak morse signals than weak voices.

2008 winter VHF FD at Red Hill, Canberra

The equipment was set up and taken down each day as I was not able to stay overnight.  The morning temperature on Sunday was about 5 which was much warmer than it could have been.  Some contacts into Sydney on 2m and 70cm, with an attempted contact on 23cm with Adrian VK2FZ.  Contacts made around town on all bands and a visit from Ian VK1FOTO (later VK1IS), John VK1JST and Charles VK1CM.  Power was from the GMC 950va alternator.

Amateur Radio, Computing and other activities of Andrew VK1DA/VK2UH