Tag Archives: Bob Colegrove

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.

Spread the radio love

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.

Spread the radio love

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 →

Spread the radio love

Guest Post: Remembering the Radio Shack TRFs

10 Replies

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

Remembering the Radio Shack TRFs

As recalled by Bob Colegrove

There has always been an interest in DXing on the cheap. At the same time, most of us don’t want to sacrifice any more capability than necessary. In the late 1970s and the early 1980s, Radio Shack provided an attractive answer to this conundrum for medium wave DXers. These were identified respectively by their Radio Shack stock numbers 12-655 and subsequently the 12-656A. I remember them being very popular among National Radio Club members of the time.

These radios were commonly known by their sobriquet “TRF.” Initially applied by Radio Shack itself, the term stuck. TRF stands for tuned radio frequency receiver. In the early days of radio, the term referred to the necessity for the operator to manually put an RF amplifier stage on frequency by adjusting the value of a variable capacitor or inductor. As amplifier stages became cascaded in two or three stages, this became a real problem, as each stage had to produce the correct frequency before anything could be heard. Eventually, designers hit on the idea of mechanically connecting all the RF stages together so tuning could be accomplished with a single knob.

Fast forward to the standard AM radios of a later generation. Entry level (read cheap) radios were limited to two stages consisting of a converter and an oscillator. This was standard design practice during the vacuum tube and transistor eras. Better, more sensitive radios added a third stage, an RF amplifier operating ahead of the converter stage. Obviously, this required more circuitry and, consequently, more expense.

Enter the Radio Shack TRFs. The term TRF was a throwback to the days of the tuned radio frequency radios and referred specifically to Radio Shack’s addition of an extra RF amplifier ahead of the converter stage. The TRFs were by no means the first radios to have this feature, but they were obviously marketed to folks who wanted longer than normal distance reception. Further, the radios were AM only uncompromised by FM circuitry, which would have to be integrated into the design and provide a distraction at best and a performance compromise at worst.

I didn’t discover these treasures until late in their production cycle. Consequently, my comments are mostly focused on the 12-656A. In later times, the -656A retailed for $34.95. On final clearance this dropped to $25, and I snapped up several for my friends and my own tinkering. The internal layout was not especially good for repair or modification, but at least was well within this enthusiast’s capability.

The picture below shows the dial of the -656A. The radio was designed prior to the expansion of the AM broadcast band to 1700 kHz; thus, it is only specified to cover 520 kHz through 1620 kHz. Although I don’t recall ever having tried it, the circuitry could possibly be coaxed to 1700 kHz. On one of my “hot rod” units, I replaced the silk-screened dial with a plain piece of Plexiglas backed with a hand-calibrated dial, which permitted accurate calibrations for 10-kHz channel identification. The tuning knob and dial cord mechanism work very well right out of the box.

The controls are aligned along the right front side of the radio. Below the tuning knob are tone and volume controls followed by an off/on switch. The 655 is similar, except the tone switch is replaced by a slider potentiometer like the volume control. In retrospect, the off/on switch should have been recessed into the front panel, as it is easy to accidentally turn on the radio with the protrusion of the switch.

The back of the cabinet (below) features standard 1/8” phone jacks for earphones (left) and an external antenna (right). The TRFs may be powered by either four C cells or from 115-Vac mains. Rather than having a separate 6-Vdc wall wart, the ac power supply components, cord and all, are contained inside the radio. For storage, the power cord is simply wrapped up in its own compartment next to the battery compartment.

Below is a tinker’s view of the innards of a -656A. Could the box have been made smaller? Obviously, one could replace the internal ac power components with a wall wart. Perhaps the C batteries could be replaced with AAs. Problem is with the speaker. The PC board hides a large drum for the tuning mechanism. Given the smooth tuning and large dial, the tuning arrangement is not something I would compromise. So, stacking the speaker with the PC board would require a much thicker box.

The external antenna uses the standard approach of a small transfer coil wrapped around the ferrite bar for signal transfer. Just as an aside, keep in mind for any long- or medium-wave radio having an external antenna coupled to the internal ferrite bar, the ferrite bar will remain active with the external antenna attached. The external antenna is effective only to the extent that the phase and amplitude of its signal compliment or reduce that produced by the ferrite loop.

In addition to two intermediate frequency (IF) stages, the circuitry includes a 455 kHz ceramic filter in the base circuit of the first IF stage. This provides very good selectivity. Never satisfied the way things are, when I first got these radios, I duplicated a third IF stage in one of the units. The result was a nice tight bandwidth still providing good audio.

The “improvements” in the -656A seem to be focused on the reduction of production costs. I’ve already mentioned the tone switch for one. Another example is the replacement of discrete components in the -655’s audio amplifier with a whopping 0.5-watt integrated circuit in the 656A. Having no way to make a performance comparison, I will say my experience with the -656A is that it is still a very hot radio.

The TRFs filled a relatively minor marketing niche, namely DXing enthusiasts and perhaps a small number of expatriates who wanted to listen to broadcasts from their old hometowns. The format evolution of medium wave broadcasting was already well on the way toward news-talk-ethnic broadcasting, and the appeal to rock ‘n rollers or virtually any music lovers just wasn’t there. A sensitive radio had to include an FM band. So, the TRFs faded into history sometime in the early ‘80s.

Enter the long-distance “superadios,” notably General Electric’s Superadios I, II, and III in the 1990s. Radio Shack itself produced a clone-like Optimus 12-603. The “super” by that time referred more to the audio quality than the sensitivity. This later generation featured two higher-quality speakers packaged in a cabinet with somewhat better acoustics, separate base and treble controls, all driven by a higher-powered audio amplifier.

Having worked in the industry for a couple years as an assembler, I have never been convinced that inclusion of multiple band coverage does not result in some performance compromise. Radios such as the TRFs have a special appeal to me. The General Electric P780 is another well-regarded example of an AM-only, high-sensitivity, radio. Maybe you have a favorite of your own. If you’re into tinkering, and even if you’re not, a functional TRF or such radio can provide a lot of cheap entertainment.

Spread the radio love

Bob’s Updated Passive, Resonant, Transformer-Coupled Loop Antenna for Shortwave

5 Replies

Figure 1. A Passive, Resonant, Transformer?Coupled Loop Antenna for Shortwave

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

A Passive, Resonant, Transformer?Coupled Loop Antenna for Shortwave

By Bob Colegrove

Over the years I have resisted the level?of?effort necessary to construct and maintain outdoor antennas. Rather, I have focused on squeezing out all of the microvolts I could get inside the house. Many years ago I had access to a well?stocked engineering library, and used my advantage to gather information about the theory and development of loop antennas – a daunting undertaking for an English major. Ultimately, by adhering to a few basic rules, some of them dating back 100 years, I found quite acceptable performance can be had with an indoor passive antenna intersecting just a few square feet of electromagnetic energy.

Theory

There are a couple of advantages of resonant loops as opposed to non?resonant ones. The first is the fact that the signal dramatically increases when you reach the point of resonance. The second follows from the first in that resonance provides a natural bandpass which suppresses higher and lower frequencies. This gives the receiver a head start reducing intermodulation or other spurious responses. The downside of all this is that the resonant loop is, by design, a narrow?band antenna, which must be retuned every time the receiver frequency is changed by a few kHz. On the other hand, there is nothing quite as rewarding as the sight (S?meter) and sound you get when you peak up one of these antennas – you know when you are tuned in.

There is nothing new about the loop antenna described here. It’s just the distillation of the information I was able to collect and apply. There are a number of recurring points throughout the literature, one of which is the equation for “effective height” of a loop antenna. It basically comes down to the “NA product,” where N is the number of turns in the loop and A is the area they bound. In other words, provide the coil with as much inductance as possible.

Unfortunately, for resonant loops, the maximum coil size diminishes with frequency.
With this limitation on inductance, the challenge becomes minimizing unusable capacitance in the resonant frequency formula in order to get the highest inductance?to?capacitance (L/C) ratio possible. Some of the unusable capacitance is built into the coil itself in the form of distributed capacitance, or self?capacitance between the coil turns. This cannot be totally eliminated, but can be minimized by winding the coil as a flat spiral rather than a solenoid, and keeping the turns well separated.

The second trick is with the variable capacitor. Even with the plates fully open, there is residual capacitance on the order of 10 to 20 picofarads which can’t be used for tuning purposes. A simple solution is to insert a capacitor in series, about 1?4 the maximum value of the variable capacitor. This effectively decreases the minimum capacity and extends the upper frequency range. In order to restore the full operating range of the variable capacitor, the fixed capacitor can be bypassed with a ‘band switch.’ With the series capacitor shorted, the variable capacitor operates at its normal range and extends coverage to the lower frequencies. Continue reading →

Spread the radio love

Bob Colegrove on “The Joys and Challenges of Tuning Analog Radios”

6 Replies

Many thanks to SWLing Post contributor, Bob Colegrove, who recently shared this excellent article and has kindly allowed me to share it here in the the Post. Bob prefaced it by saying, “Being a retired technical writer, I started the attached article some time ago for my own amusement, but it quickly got out of hand.”

“Got out of hand” in a very good way, Bob!

An excerpt from Bob’s article.

I love how this piece takes us through receiver history and explains, in detail, the mechanics and innovations. It’s also a very accessible piece that both the beginner and seasoned radio enthusiast can appreciate.

But don’t take my word for it, download it and enjoy!

Click here to download The Joys and Challenges of Tuning Analog Radios as a PDF.

Thank you again, Bob. This is a most enjoyable and informative read! This was obviously a labor of love. Thanks for sharing it with our radio community!

Spread the radio love