Hidden treasure on your doorstep

Finding local businesses listed on the stock market is easy with the help of SharePad and Google Maps. Richard maps the market to find out what’s good in the ‘hood.

When I wrote about Portmeirion last time, I promised to review a second “old favourite” in this article. In the meantime, though, I have been thinking about the other end of the research pipeline: finding new companies to invest in. We will return to analysing companies, but today I would like to share a project close to my heart.

The local advantage

Naturally we are interested in the areas in which we live, and, it is easier for us to exercise our curiosity about companies we might invest in if they are nearby. We can easily visit a business, informally if it is open to the public, or on formal occasions like the annual general meeting. We are more likely to know employees, customers or suppliers of local businesses and competitors in local industry clusters. These sources can give us insights into how local businesses make money, insights the big money managers in London cannot be bothered to access. The local press may publish stories that do not make it into the national news, or the stockmarket Regulatory News Service. Maybe stories of investors sitting outside factory gates counting trucks in and out are apocryphal, but we can even give that a go if the factory is nearby!

While diversification is a good thing, and we do not want too many eggs in one basket, if we can find decent companies nearby it is probably healthy to inject some local flavour into our portfolios.

The opportunity to invest locally is not limited to where we live. Though I live in Cambridge, most of my family live between Stafford and Stoke-on-Trent and I sometimes combine business and pleasure, staying with relatives and visiting nearby companies in the Staffordshire Potteries (it is one of the reasons I am interested in Portmeirion). When ever he travelled, legendary US fund manager Peter Lynch would take the opportunity to visit local businesses. It must have driven his family nuts.

For years, I have hankered after an atlas of UK listed companies, but it only dawned on me I might be able to create one with the help of SharePad last week.

To find out how to create your own atlas, read on.

To play with one I made earlier, skip to the end.

Creating an address book in SharePad

This article comes to you courtesy of a moment of serendipity. I was wondering how to find local listed companies while SharePad was open in front of me and the Company tab of the Financials section was showing. Among the many details listed is the address of the company selected. Obviously, we do not want to have to look up the details of every company in SharePad to find the ones nearby, there are thousands, but it got me thinking…. If you want to know something about lots of companies in SharePad it is always worth creating a new list table and seeing if SharePad makes the data available as a list column.

[For instructions on how to create a new list, see this tutorial and for how to populate it with columns of data, see this tutorial]

Usually SharePad comes up trumps. In this case doubly so. Not only can we add the address of a company headquarters to a list table, we can also add the location of the AGM. These are the options available if we type “Address” into the search box when we add a column:

Both of these addresses are important because companies do not always hold their annual general meetings at their headquarters. Companies that do should be applauded, especially if they allow shareholders to tour the facilities, but some hold AGMs in the offices of their stockbrokers or public relations firms, and some hold them in other venues, like hotels.

[For advice on attending AGMs, see this article]

To map these locations in Google Maps, we will need to create two address books. I have called mine – Address book and AGM Address book. Each one is a SharePad list table with just two columns: Name (for the company names) and Address / AGM Address. Here is a screenshot of the Address book:

Mapping the market

Plotting the address book in Google Maps is easy because SharePad allows us to export the data into a spreadsheet and Google allows us to import the spreadsheet. There are, though, a couple of hitches we must deal with first. Google only allows us to import up to 2,000 addresses and SharePad has more than 2,000 shares listed in London. Also Google does not recognise some of the addresses in the SharePad database.

To get around the first problem, we can filter out unwanted shares. For my map I have chosen to filter out Equity/Nonequity investment vehicles, collective investments like venture capital trusts and real estate investment trusts. You can see the filter at the top of the list in the screenshot, above. This brings the total number of addresses down to 1,660, which we must export from SharePad by clicking Sharing and then Export table:

[For how to filter, see this tutorial]

We can be much more fussy if we want though, exporting and mapping smaller lists, for example companies listed on the Main Market, AIM listed companies, or the results of any filter.

For a large list, the quick but dirty way around the problem of Google not recognising some addresses is to reduce the addresses down to postcodes, which we can do by adding a third row to the spreadsheet we exported.

Here is an example in Google Sheets, showing the new column, named “Postcode”, and the formula, =TRIM(right(B2,8)), required to isolate the last seven or eight characters of the address, usually the postcode for British addresses. It needs to be copied down into the subsequent rows by dragging the small blue box in the bottom right hand corner of the cell (also outlined in blue).

This hack has the unfortunate consequence of mangling overseas addresses, that have postcodes of different lengths, or no postcodes at all. Gambling company 888 is headquartered in Gibraltar, for example.

To plot the addresses we must create a map in Google Maps, by clicking the “burger menu”, three horizontal lines in the top left corner of the Google Maps screen, and then clicking Your Places, then MAPS, and then CREATE A MAP (at the bottom of the window). That takes us to a blank map and the invitation to import the data:

When we click on Import, we are asked to choose the columns we wish to use to plot the places on the map. The column we need to tick is Postcode. Then Google asks us to pick a column to use as the title for the placemarks, which is Name. I have also given my map a grandiose name! This is a screenshot of the resulting map:

The 253 addresses Google cannot recognise are almost all overseas ones mangled by the spreadsheet, or companies that have no address. If these are important to you, you can edit them manually in Google Maps by clicking on Open data table.

When we are dealing with a few hundred shares and the number of addresses Google fails to recognise is small, we can skip the spreadsheet stage altogether and manually edit the addresses in Google Maps.

What’s good in the ‘hood?

All we need to do now is zoom, the controls are in the bottom right hand corner of the Google Maps screen. By all means make your own atlas, especially if you have come to this article some time after it was published and some of the addresses have changed, but if you would rather jump straight to the maps I made in the course of writing this article, here they are:

Atlas of UK listed company headquarters

Atlas of AGM addresses

You can click on the pins to see the names and addresses, or search the map for a particular company. You can search for your own home, and find the nearest listed companies.

This is a screenshot of my neighbourhood, with one of the pins selected:

I have highlighted LPA because it makes LED lighting systems. I know a bit about the industry as I have owned shares in FW Thorpe, another lighting company, for many years. Studying LPA might help me learn more about lighting and reveal another good investment.

Now I know LPA is just down the road, I have another reason to investigate it. Sadly, though, by consulting the Atlas of AGM addresses, I note LPA holds its AGM in London.

Richard Beddard.

Contact Richard Beddard by email: richard@beddard.net or on Twitter: @RichardBeddard.