Using Excel To Process Data

Excel is an excellent tool for working with the WSPR data to ultimately arrive at relative gain. I have used two tools for extracting data from the WSPR database (there are others of course). One is using WSPR Rocks and the other is WSPR Live.  WSPR Rocks is limited to 5000 spots while WSPR Live can extract up to 10000 spots. These tools can extract the desired data to a .tsv or .csv file that can then be imported into Excel.

After importing the data, the table will look as shown below. You need to edit the table and add two columns, The first column named "Antenna" identifies the antenna associated with the time period the data was collected for the antenna. If the table is sorted by time, this is easy to do for each of the 30-minute time periods. The inserted column is shown in Yellow and identified as Antenna Q-Wave. Since one goal is to average the SNR values over time it would be mathematically incorrect to average the SNR data as it is presented in logarithmic values (decibels). A column should be inserted after the SNR column which transforms the logarithmic values to a linear power scale. Create a column called Linear Power and insert an equation in each cell "=10^(J2/10)" where as in this case J2 represents the SNR value in column J for row 2. Drag this new cell down the table to create a complete column of linear power values. 

I recommend making two copies of the table after the Antenna column is completed. Designate each copy as a table for one of the antennas, and delete the other antenna data in the table so that you are left with two antenna tables.

Next, you want to create a pivot table for each of the antenna tables. Highlight the table rows and columns and select Inset Pivot Table. This is where you summarize data by receiver location.  From the Pivot Table Fields box do the following

• Drag the rx_sign field down to the Rows dialog box.

• Drag the SNR field down to the Values dialog box. The default field is Count.  This gives you the total number of spots for the receiver station.  

• Drag the Linear Power field down to the Values dialog box. Select the drop down and select Value Field Setting.  Change to Average. This is your average, or mean value of linear power for the given station.

• Drag the Distance field down to the Values dialog box. Select the drop down and select Value Field Setting.  Change to Average. This is a convenient way of retrieving the distance from the receiver station to the transmitting station. This field is convenient for plotting snr results as a function of distance.

• Drag the azimuth field down to the Values dialog box. Select the drop down and select Value Field Setting.  Change to Average. This is a convenient way of retrieving the azmiuth angle  from the transmiiting station station to the receiving station. This field is convenient for plotting snr results as a function of AZ.

When complete, the pivot table should look like the following.

Now repeat the same process in another tab for the 2nd antenna.  In the end you will have two pivot tables summarizing the data per receiver station. From here it is a matter of filtering through the data for each antenna and receiver station to find that ones that show a spot count of 40 or greater. I have found it convenient to copy and paste from the pivot tables all the data for both antennas into a new tab and place the antenna data side by side. I then select the data from just one antenna and sort it by Count. I then just delete the data for < 40 spots. I then resort the table by the receiver call sign.  I then repeat this for the other antenna data.

At this point it is a matter of deleting select receiver stations that don't have a corresponding set for both antennas. Shifting the cells up when you delete helps keep it all in order.  In the end you will have matching receiver station data for both antennas in a single row as shown below. In this case my reference antenna was called "Q-Wave" and the test antenna was called "Whip".

The last three columns are added once the table has been set. We now take the average linear power for each antenna and convert it back to decibels. We again insert an equation into a cell to do the conversion. For example, insert into cell O3 "=10*LOG10(D3). Drag that cell down the rest of the column to populate the cells for the Q-Wave antenna average SNR (dB) values.  Repeat the process for the second antenna, in this case the Whip antenna. The final step is to compute the relative gain of the test antenna to the reference antenna.  Here cell Q3 has the equation "=P3-O3". Drag that cell down to populate the rest of the column.

From here it is simple to prepare scatter plots of relative gain vs. distance or AZ.