Category Archives: Guest Posts

Guest Post: Calculate Station Distances Using Excel Formulas

Many thanks to SWLing Post contributor, Bob Colegrove, who shares the following guest post:

Calculate Station Distances Using Excel

By Bob Colegrove

Introduction

On occasion, I’ve wanted to know just how far away a station was from my home. I’ve never been much of a contester, but I know distance can play a part in the results. There are a number of Internet cites which let you enter latitude and longitude information and then calculate the distance across the surface of the earth. These are alright on an occasional basis, but I often wind up getting the data mixed for the two locations, and it is not handy when you want to make several measurements. Here’s a way to generate the distance from your home to thousands of stations with just a little effort.

Many years ago, armed with my faded knowledge of high school trigonometry, I used Excel to calculate the surface distance between any two points on earth. I managed to find the spreadsheet (file dated 1998) which has no fewer than 11 steps in the algorithm. Although it worked, when I came back to it a few months later to make a change, I couldn’t remember my thought process. There are Internet sites which develop earth surface calculations in highly esoteric terms and heavy-duty math. But life is short, and I wanted to cut to the chase. There are, in fact, several formula variations which have somehow managed to distill all this down to a neat single-cell calculation, and they seem to work very well.

Construction

The spreadsheet figure below is the simplest form used when you have decimal latitude and longitude data as input. The convention is to use negative numbers for the Western and Southern Hemispheres. Home is your reception location and all other locations are compared with that to determine the distances. If you’re curious, the home location (yellow cells) used in these examples is the monument marking the geographic center of all 50 US states in Belle Fourche, South Dakota. Google Maps is one easy source to determine the exact latitude and longitude of any point on earth.

To calculate the distance between any two points on earth, copy the formula below directly into a cell, then change the reference cell names as appropriate, and you’re ready to go.

=ACOS(COS(RADIANS(90-$B$5)) * COS(RADIANS(90-B9)) + SIN(RADIANS(90-$B$5)) * SIN(RADIANS(90-B9)) * COS(RADIANS($C$5-C9))) * 3959

$B$5 and $C$5 are the cell references for your home address (yellow in the figure above). Of course, the dollar signs indicate these data remain fixed in each calculation. B9 and C9 are corresponding latitude and longitude for the example radio station, WTOP (green). Change these four cell locations as necessary. The constant, 3959, at the end of the formula is the average radius of the earth in miles. Use 6371 if you want kilometers. The data cells in Columns D and E are populated with the formula and produce the result. These values are dynamic and can be replicated down the columns for the rest of your station location data.

Degrees, Minutes, and Seconds Format

The US FCC on-line database contains latitude and longitude tower locations for medium wave stations in Region 2, North, South, and Central America. However, coordinates are in degrees, minutes, and seconds format and must be converted to digital format for calculation of distances. The conversion process can also be done in Excel.

In this case, the inclusion of the coordinate hemispheres, N or S, and E or W is important. Whereas, the hemispheres in the decimal example were signed + or -, the inclusion of the appropriate letters here is necessary. Cell L5 reads

=IF(H5=”S”,-I5-(J5/60)-(K5/3600),I5+(J5/60)+(K5/3600))

and cell Q5 is similar for longitude, except “W” is substituted for “S.” These formulas are then replicated in columns L and Q for each data item. Columns R and S contain the distance calculation formulas as described above. Line 14 is not necessary, but can be used to see if your formulas are correct; that is, the distance from home to home should be zero.

Let Excel Get the Information for You

What follows is for anyone tired of copying cumbersome latitude and longitude data. Unfortunately, it only works on the current version of Microsoft 365 Excel, and apparently goes off into the big cloud in the sky to instantly download the information.

Enter the town followed by either the US state, Canadian province, or other country name (Column A).
Copy these locations to the next column (Column B). The cells in Column B will become temporary geography cells. Note: As shown above, the data have already been converted to geography format (Step 4).
Make sure you have all the geography cell locations selected (Column B).
On the Data ribbon select Geography. A map icon will appear at the left of each cell, and the state, province and country will be truncated.
For the first latitude (Cell C7), enter =B7.Latitude; likewise, =B7.Longitude in Cell D7.
The formulas in C7 and D7 can be replicated down your list.
Columns for miles and kilometers (E and F) can be added using the distance formula as described above.

The geography data (Column B) cannot be replicated. If you want to add data later, you will have to reapply the geography format for the new data. Or, latitude and longitude can still be inserted manually for any additional entries. The geography data (Column B) are not needed beyond this point and can be deleted or hidden.

Note: I logged on to my first mainframe computer in September 1976 and have never ceased to be amazed at what these confounded things can be made to do. I tried as best I could to trip the system with small, obscure towns in faraway places, as well as duplicate names. I finally succeeded with a relatively large city, Ulaanbaatar, Mongolia. To be fair, I tried to get it to accept alternate spellings. So, if you need that one, you’ll have to enter it manually.

Medium Wave Example

This example is for medium wave DXers in Region 2, the Americas. It makes use of the FCC AM database at https://www.fcc.gov/media/radio/am-query. The database currently contains more than 24,500 entries, many of these are duplicate entries for stations using different daytime and nighttime powers.

Download the database as a pipe-delimited text file.
Import the file into Excel.
Create additional columns to convert the latitude and longitude data from degree-minute-second format to decimal as described above.
Add some rows above and enter your home coordinates in decimal.
Create another column to calculate the distance from home to all the stations, again using the base formula above.
Hide any columns in the FCC database that you don’t need.
Finally, by creating an Excel table from all of the data, except your home location, you can do some on-the-fly filtering.

The example below shows some of the stations near our example home in Belle Fourche, South Dakota. The Distance column on the right has a filter applied to limit the listing in the table to stations within a 150 mile radius, that is, it only lists potential daytime stations. You could also use the conditional formatting feature of Excel to highlight the same information in the unfiltered data.

Shortwave Example

The AOKI log, http://www1.s2.starcat.ne.jp/ndxc/, has listings for all of the recent broadcasting cycles, B21, A21, etc. The Excel format files are zipped for download, and include the latitude and longitude of each station. Unfortunately the coordinates are not only in degrees, minutes and seconds, but they are all mashed together in one cell for each listing. Excel to the rescue again. Select Text to Columns in the Data Tools portion of the Data ribbon. This feature will allow you to divide the single column into four columns each for latitude and longitude, that is, degrees, minutes, seconds and hemisphere. Then you can use the conversion formula to change degrees-minutes-seconds to decimal. Note that the first three digits used for longitude are minutes (they go up to 180); the remaining numerical columns have two digits each (up to 60 or 90), and the hemisphere columns (alpha) one character each.

Accuracy

Here are a few things affecting accuracy:

The constants 3959 or 6371 used in the formula for miles and kilometers are generally accepted averages for the earth’s radius. The difference between the equatorial (longer) and polar (shorter) radii is about 13 miles.
If you are using town locations in your data, remember that the actual distance to the tower in that town is likely to be different. The FCC and AOKI data are assumed to be station tower locations.
Some decimal sources of latitude and longitude data have less resolution, which could lead to a slight error.

You’re on Your Own

You may have noticed the examples shown in the figures all have multiple station locations. My thought in doing this was provide some test for accuracy and secondly to provide a seed for developing the spreadsheet into a more inclusive log of stations. There is likely enough basic Excel knowledge among the folks gathered here, and each person will likely have an individual preference in designing a spreadsheet. Nevertheless, the spreadsheet shown in the figures can be downloaded by clicking this link.

The first sheet shows Figures 1 and 2 from this article; and the second sheet, Figure 3. The link in Cell I2 of the second sheet describes how to use the geography feature of Microsoft 365 Excel. The third sheet is a recent copy of the FCC AM database (Figure 4). To facilitate storage and downloading, only stations from 530 kHz to 600 kHz are included. Numerous unused columns from the FCC AM database have been hidden; so you can still copy the full, pipe-delimited FCC database into Columns A through AH. The FCC database has been converted to an Excel table; the Home location is not part of the table. Try substituting your own location for Home (Cells AI2, latitude and AJ2, longitude) and setting a distance filter from your home in Cell AK4. In the example, the distance filter has been set limiting the list of stations to less than 600 miles from our example in South Dakota. Note also that the Conditional Formatting feature on the Home ribbon has been used to highlight stations less than 100 miles from home.

If you have any interest in developing your own spreadsheet, perhaps you can comment on what you have done, or provide the rest of us with something I have missed. Hopefully, I have provided enough information to get you started.

Jock explores “The Essential Listening Post Part II – When the lights go out”

40 Replies

Many thanks to SWLing Post contributor, Jock Elliott, who shares the following guest post:

Photo by Parker Coffman on Unsplash

When the lights go out: The Essential Listening Post – Part II

By Jock Elliott, KB2GOM

What’s the most valuable commodity in an emergency? Information.

Without information, it is very difficult to make decisions of what actions you should – or shouldn’t – take. Fortunately, as swling.com readers know, radio can come to your rescue.

As an example, I offer for your approval this minor incident that happened just a few mornings ago.

At 4:30 am, I awoke. That’s not particularly unusual; I get up early lots of mornings to run the Commuter Assistance Network on ham radio.

What made this morning unusual were the things I couldn’t see: the digital clock across the room, the tiny LED lamp that illuminates the way to the bathroom in the middle of the night. They were both dark. In fact, the only light that I could see was the LED from the uninterruptable power supply for the computer in the next room. It was pulsing, indicating the power from the mains was out.

With the help of a flashlight kept within easy reach of the bed, I made my way downstairs. A peek out the windows revealed the surrounding area was dark; no lights in local houses, no street lights. A house across the ravine behind my house had a single light, but it had the bright white look of an emergency lantern. So this outage was wider spread than just the lane where I live. But how widespread was it? In early February in upstate New York, it’s winter; temperature about 6 degrees Fahrenheit on this particular morning. The thermostat on the wall has already dropped below where the furnace should have kicked on. With no electricity; no furnace.

With no house power, I had no internet, so I couldn’t look things up to find out why there was no house power. Because we use Voice Over Internet Protocol (VOIP), with no internet, no house phone.

Now, I know what you’re thinking: “Well, dummy, fire up your smart phone, and in a few moments you’ll have your answers.”

To that I say: “Not so fast there, pardner.”

I consulted with a ham radio friend who makes his living in the commercial radio business. He consults with many companies, including cell phone companies, so he knows what he is talking about.

It turns out there are three things that could render your smart phone useless.

The first is whether your local cell tower(s) have battery back-up. Most do, but how many hours the batteries will run the cell tower can vary widely from just a couple of hours to perhaps eight. Depending upon when the power went out, you may or may not be able to connect.

The second is that many cell phone towers themselves connect to the rest of the network through wire or fiber optic cable. If a vehicle has taken down a pole, or a falling tree has taken down a cable, the network may be disrupted.

Finally, if there is high demand for your local cell phone tower, you may not be able to make a connection. My commercial radio “guru” relates that he went to an event at a local community college. There is a cell tower right on the property, but he had great difficulty connecting simply because so many people were trying to use the tower.

During emergencies, cell phone networks frequently go into gridlock because of high demand, so it’s a good idea to have other means of gathering information. An interesting aside: some years ago, I heard a presentation from one of the hospital administrators who was in New Orleans during Hurricane Katrina. They were unable to make voice phone calls, but apparently they could sometimes send and receive text messages.

Getting back to my small lights-out incident, I was in the actual act of firing up a radio to check out what local broadcasters on the AM (medium wave) band had to say, when the lights came on, the furnace started, and internet and phone service were restored. My greatest inconvenience was having to reset a couple of digital clocks.

But it raised a serious question: what should be your essential listening post if the lights go out, the fertilizer hits the ventilation equipment?

First and foremost, a battery-powered radio capable of receiving your local broadcasters. You need to know – or find out – which ones have back-up power so they can keep transmitting. Knowing that will do two things for you: first, tuning in to a station with back-up power will hopefully get you the information you need, and second, if stations that don’t have back-up power are off the air, that will give you an indication of how widespread the power outage is.

Knowing the extent of the blackout can be important. A couple of decades ago, on an August afternoon, my better half and I took our young son to a local park where there was a water fountain that the kids could run through. When we got home later, the power was out. I saw the neighbor standing in her yard and asked if she had reported the outage. “No point,” she said. “Why?” I asked. “Because the lights are out from Canada to Virginia.” Oh.

In addition to knowing which stations are likely to be on the air, it’s also good to know which local stations have news staff that are likely to collect and broadcast information that is needed during an emergency.

Second, if you live in the United States or Canada, you need a weather radio. Every state in the Union has bad weather of one sort or another . . . and some of them can kill you. NOAA weather radio is an excellent source of information. It’s free, and it does a fine job of delivering weather-related info in a concise and useful format.

Third, it would be very useful to have a scanner or ham radio capable of receiving your local 2 meter repeaters. This could be an additional source of useful information in a crisis.

So, are there any radios that I would recommend for “The Essential Listening Post” when the lights go out?

Yes, there are.

The C.Crane CCRadio 2E

First on my list would be the C.Crane CCRadio 2E (or CCRadio3). It receives AM, FM, NOAA Weather Band with Weather Alert and the 2-Meter Ham Band. It will run on house power or, if the lights are out, over 200 hours on batteries. By all accounts, it offers excellent performance on AM and FM, and it is one of the most sensitive NOAA weather radio receivers I have tested. I bought one and can heartily recommend it.

CC Skywave SSB

The CCrane Skywave SSB receives AM, FM, NOAA Weather band plus Alert, Shortwave (1711-29.999MHz) with SSB, VHF Aviation Band. It doesn’t receive the 2 meter ham band, but it will receive hams on HF frequencies, which might come in handy in an emergency. It is not quite as sensitive as the CCrane 2E on NOAA weather frequencies, but, as I reported last year it was the most sensitive NOAA weather radio receiver I took to Sodus, NY. It is very small and portable and will run for over 50 hours on batteries. I bought one and can heartily recommend it.

The Eton FRX3+

The Eton FRX3+ is an interesting alternative for a “when the lights go out” radio. This battery-powered radio receives AM, FM, and NOAA weather radio with alert. It has a couple of LED lights for navigating in the dark and can be charged by a built-in solar panel, hand-crank, or USB cable, and can even be used to charge your cell phone. Eton Corp. sent me one of these, and I find that it offers worthy performance on AM and FM, and excellent sensitivity on NOAA weather radio. Recommended.

In the future, I hope to offer some additional useful information about NOAA weather radio as well as a comparison of different ways to receive NOAA weather radio, including dedicated weather radio, consumer radio, scanner, and ham handi-talkie.

-Jock Elliott

Guest Post: Control of Electromagnetic Radiation (CONELRAD)

11 Replies

Many thanks to SWLing Post contributor, Bob Colegrove, who shares the following guest post:

Control of Electromagnetic Radiation (CONELRAD)

As recalled by Bob Colegrove

In his comment on my recent posting, Tinkering with History, Mario noted the dial on the featured radio, the General Electric P755A, sported two small triangles, one between 6 and 7, and the other between 11 and 14. He noted that these were civil defense markers intended to show the frequencies of 640 kHz and 1240 kHz, respectively, and that these were characteristic of AM radios produce in the US roughly between 1953 and 1963. Since two full generations have been born and raised to adulthood since that time, and I can’t find any related posting here, I thought it might be useful to bring this subject to light.

In spite of otherwise economic prosperity and general wellbeing, these years were nevertheless filled with anxiety about the prospects of all-out war. Children of the time (myself included) were being shown how to hide under their school desks, and some of their parents were going so far as to construct air-raid shelters in their basements, and stock them with enough provisions to supposedly outlast any catastrophe. So it was that CONELRAD came into being in 1951. The idea was, that in case of a National emergency, all radio and TV stations would go off the air, and only certain medium wave radio stations would stay on either 640 kHz or 1240 kHz. They would remain on for a few minutes and then other stations would take over in a round robin arrangement – this to deter homing by hostile bombers. Needless to say, quickly changing over transmitters and antennas to one of these two frequencies did not bode well for the equipment and there were many failures in subsequent tests. Note that, as originally conceived, the system did not provide for local weather emergencies or other situations.

The banner photo at the top of this posting shows a portion of the Hallicrafters S-38E receiver which conformed to Government law of the time required for marking all AM dials. An S-38E just like it was my first genuine multi-band radio in 1959. Assuming good alignment, the dots next to the CD triangles indicated the 640 kHz and 1240 kHz frequencies. When a test came on, you didn’t have to fish for it, since CONELRAD was the only service transmitting.

Going back to the radios described in Tinkering with History, GE took this one step further. The figure below shows a portion of the dial on a GE P806A. Note the nub on the outer edge of the dial under the triangle at 1240 kHz. There is another nub on the edge at 640 kHz. Together with the raised triangular dial pointer molded on the cabinet, they provided a braille system, so that someone visually impaired could easily tune to a CONELRAD frequency.

As technology improved, CONELRAD transitioned to the Emergency Broadcast System (EBS) in 1963, and subsequently the Emergency Alert System in 1997. A more thorough description of CONELRAD can be found on Wikipedia https://en.wikipedia.org/wiki/CONELRAD. Reprint of an April 1955 Radio & Television News article describing the construction of a transistor CONELRAD receiver is at https://www.rfcafe.com/references/radio-news/conelrad-radio-television-news-april-1955.htm.

Pavel fixes a stereo lock in the Eton E1

4 Replies

Many thanks to SWLing Post contributor, Pavel Kraus, who shares the following guest post:

Eton E1 – fault in stereo reception

I recently became the owner of an Eton E1 receiver, which I obtained on eBay from the USA.

The receiver is great, everything worked, error-free display. The only problem was that even FM and strong local stations did not play stereo even though stereo reception was set in the menu. The stereo text on the display flashed several times when the stations were not tuned in precisely, but after the stereo tuned, the text went out. I know that stereo reception is not the most important thing for this receiver, but it bothered me that there was a defect at all.

The Sanyo 3335 stereo decoder is used in this radio. The stereo reception switching threshold can be set with a 10kohm potentiometer which is connected to terminal 4 of the integrated circuit:

I disassembled the radio by loosening the screws on the back of the radio. The receiver is divided into two parts. I removed the XM module and disconnected the part of the radio with the display from the flat wires on the second printed circuit board of the radio.

I then removed the screws on the circuit board located at the back of the radio.

I removed the printed circuit board and found a matching resistor trimmer on the other side of the circuit.

Then I connected these two points with a wire (when running on batteries) so that I could turn on the receiver:

After tuning in to a strong local transmitter, I carefully turned the trimmer until the stereo sign lit up and listening to the headphones made sure the sound matched the stereo. I repeated this at several local stations.

The receiver now plays stereo perfectly and the settings do not affect other parameters of the receiver. After assembling the radio, I was able to enjoy quality stereo reception.

Guest Post: “Tinkering with History”

7 Replies

Many thanks to SWLing Post contributor, Bob Colegrove, who shares the following guest post:

Tinkering with History

By Bob Colegrove

One of the attractive aspects of radio as a hobby is that it has so many specialties to channel our time. Just for the sake of classification, I would group these into two categories, listening and tinkering. I think the meaning of each category is fairly intuitive. Probably few of us approach our interest in radio in the same way. Most of us have dabbled in more than one listening or tinkering specialty. Perhaps we have been drawn to one particular area of interest, or we may have bounced around from one to another over a period of time. I know the latter has been my case.

Tinkering might start with a simple curiosity about what makes the radio play, or hum, or buzz, and progress to an obsessive, compulsive disorder in making it play, hum or buzz better. Unfortunately, over the past 30 years or so, the use of proprietary integrated circuits, as well as robotically-installed, surface-mounted components have greatly short-circuited what the average radio tinker can do. For example, I have noticed a lot more interest in antennas over that period, and I think the reason is simple. The antenna is one remaining area where a committed tinker can still cobble up a length of wire and supporting structure and draw some satisfaction. But the complexity and lack of adequate documentation have largely kept newer radio cabinets intact and soldering irons cold. Bill Halligan knew you were going to tinker with his radios, so he told you how they were put together. The fun began when you took your radio out of warranty. If you did get in over your head, there was usually somebody’s cousin not far away who could help you out. The following is a sample of how one resolute tinker managed to overcome the problem of locked-down radios in the modern age. Continue reading →

Guest Post: Mark explores a 1983 Voice of America information pack

6 Replies

Many thanks to SWLing Post contributor, Mark Hirst, who shares the following guest post:

VOA Information Pack 1983

by Mark Hirst

Introduction

A recent guest post on this blog by Jock Elliott asked the question, ‘Why Listen to Shortwave?’

The comment I left at the time was my interest in how nations view themselves, and how they project that view to the world. This might be in the form of cultural exports like music, or teaching us about famous people or revered institutions in their country.

When I first started listening to shortwave in the early eighties, I never got into the habit of asking for QSL cards, being quite thrilled enough to receive programme guides in envelopes stamped with the postmark of other countries.

At the time, the primary stations for me included Radio Netherlands, Radio Sweden, Swiss Radio International, and the Voice of America. While most might send a small leaflet about their country with a frequency schedule, the information pack I received from the Voice of America stands head and shoulders above the others.

I thought readers might be interested in a brief description of this pack and with it a glimpse back into the world of 1983.

Please note that as you read the following sections, you can click on the images to view a larger version.

Package Contents

The package arrived in a manila envelope, with the logo and address of the VOA printed in the top left corner. In the top right corner is the logo of US Mail, with a declaration that postage and fees where paid for by the US Information Agency.

Package contents included:

Compliments Card
VOA Sticker
Steering the Course Magazine
VOA Magazine
May-October 1983 Programme Schedule

VOA – The Voice of America

This guide begins by outlining the mission of the VOA, emphasising its aim to be an authoritative and reliable source of news. Continue reading →

Guest Post: Comparing the Reuter Pocket and the Icom IC-705 from an SWL’s perspective

11 Replies

Many thanks to SWLing Post contributor, Uli (DK5ZU), who shares the following guest post:

SWL with a Reuter Pocket and the Icom IC-705

by Uli (DK5ZU)

Some time ago I asked how the IC-705 performs on longwave and I got some great feedback. Thanks a lot again. Since the HAM bug bit me again, I wanted to do SWL and HAM Radio portable with one rig. I started with SWL some weeks ago (just before the bug bit). I acquired a second hand Reuter Pocket RDR 51 Version B2. It is a standalone SDR Receiver 0 … 30 MHz / 50 ..71 MHz, and in my B2 version it has also FM (Stereo/RDS) and Digital Audio Broadcasting (DAB). You may find the detailed specs here:
https://www.reuter-elektronik.com/html/pocket.html

The Reuter Pocket could, at one point, be configured as an QRP Transceiver, but it is no longer supported. There is a new RDR 52 small tabletop models, which can be ordered as a transceiver, too. But due to Covid related supply chain problems and price changes for the components, the new model is currently postponed.

The IC-705 is available, though. And for portable HAM operations it is a no brainer; obviously with a high price tag, but comparable with a new Reuter RDR 52 tabletop. And since my budget for the hobby is limited, I thought about funding part of the IC-705 price by selling the Reuter Pocket. But I wanted to do a side-by-side comparison so I ordered the 705 and was able to check them both on one antenna. The goal was to compare their sensitivity and selectivity on the lower bands: BC on AM and HAM bands for SSB. I did not compare CW since I am not a CW operator.

The antenna is a MiniWhip from PA0RDT which works quite well on the lower bands.

This comparison is not at all scientific and reflects just my opinion and what I heard. But anyway, there may be some people out there interested in this. So much for the intro.

Let’s start with my overall findings. Continue reading →