Part 2: Using the Companies House API

Welcome back following part 1…

Getting a list of companies via Python – Advanced Filtering. This example will look at the transport sector, leveraging the SIC codes in companies house. This can be altered accordingly to suit the business nature you are looking for.

The libraries allows you to do more with Python, in this example that will included API calls, referencing the current date time and manipulating data frames. Necessary libraries and the script to import:

import math
import time
from datetime import datetime, timedelta
import requests
import pandas as pd
import re

Variables for reference throughout the rest of the code – see the comments for explanations:

# =====================
# CONFIG
# =====================

CH_BASE = "https://api.company-information.service.gov.uk" # companies house API base URL - all calls build off this
[postcode]_LAT_LON = (52.487, -2.121)  # Approx centroid for your area
COMPANIES_LIMIT = 500 # number of companies to be returned by the initial API call
COMPANIES_HOUSE_API_KEY = "" # unique API Key for authentication
TRANSPORT_LOGISTICS_SIC_PREFIXES = {"41","42","49","50","51","52","53","77"} # SIC codes - companies houses codes to identify industries - this list should be specific to the targeted business industry

# descriptions of specific SIC codes we will likely return
SIC_CODES_DESC = {"52290": "Other transportation support activities",

                  "[INSERT SIC CODE]": "[ INSERT SIC DESCRIPTION]

                }
# ALL UK POSTCODES
postcode_df = pd.read_csv("UK_POST_CODES.csv")
POSTCODE_AREA_CENTROIDS = {

    row["area"]: (row["lat"], row["lon"])

    for _, row in postcode_df.iterrows()

}

# maps a business size term to a numeric value for easy calculations later
SCORES = {

        "group": 60,

        "full": 50,

        "large": 45,

        "medium": 40,

        "total-exemption-full": 35,

        "small": 30,

        "micro-entity": 20,

        "dormant": 10,
    }

 

NOTE: Postcodes CSV –  this is a simple list of postcode prefixes and their central latitude and longitude values. This lets us review the distance from our central location [postcode]_LAT_LON later in the code. You can ask an LLM to produce this for you…

List of post code prefixes and the their latitude and longitude

Now its time to build a set of functions to help create the final output dataset we want. View the comments in the code to learn more about each function. The workflow is quite simple:

  • Get a list of companies that suit a set of filters – SIC codes, active status, incorporation date – using the advanced filter API
  • Iterate through the list, plugging the company number into the company API. Note: the API has limits which need to be adhered to hence a wait in the loop
  • Return the results of each company
  • Determine the health of the company, representing this numerically, through different factors such as recent accounts and documents and size
  • Rank the stores according to a score – I have considered this potential for a sales opportunity
  • Output to a file for consuming

The creation of the base API URL variable earlier is exemplified by the ch_get function that means a single function handles both getting the list of businesses then get info about each business.

 

# =====================
# HELPERS
# =====================

# send API GET request to companies house - can be reused for different URLs due to parameterasation
def ch_get(path, api_key, params=None):

    r = requests.get(

        f"{CH_BASE}{path}",

        params=params,

        auth=(api_key, "")

    )

    if r.status_code != 200:

        raise RuntimeError(r.text[:300])

    return r.json()

# calculate distance in KM from the lat-long of the business versus lat-long in variable
def haversine_km(a, b):

    lat1, lon1 = a

    lat2, lon2 = b

    R = 6371

    dlat = math.radians(lat2 - lat1)

    dlon = math.radians(lon2 - lon1)

    x = (

        math.sin(dlat / 2) ** 2 +

        math.cos(math.radians(lat1)) *

        math.cos(math.radians(lat2)) *

        math.sin(dlon / 2) ** 2

    )

    return 2 * R * math.asin(math.sqrt(x))

# extract the postcode prefix to align with the postcode prefixes in the CSV and variable
def extract_postcode_area(postcode: str):

    if not postcode:

        return None

    match = re.match(r"^[A-Z]{1,2}", postcode.strip().upper())

    return match.group(0) if match else None

# map the size term from the API (small, full, large etc) to the score in the SCORES array
def size_score(profile):

    last = (profile.get("accounts") or {}).get("last_accounts") or {}

    t = (last.get("type") or "").lower()



   

    return SCORES.get(t, 0)



# CHECK - check that the SIC code of the business aligns with the list of SIC we are looking for, in this case the transport sector
def is_transport(sic_codes):

    return any(s[:2] in TRANSPORT_LOGISTICS_SIC_PREFIXES for s in sic_codes or [])



# handle large organisations split into a parent and branch - flag if a parent company is present. this is important as the parent may be under a separate industry
def get_branch_info(profile):

    parent = (profile.get("branch_company_details") or {})

    if parent != {}:

        return parent.get("parent_company_name"), parent.get("parent_company_number"), parent.get("business_activity")

    else:

        return "no parent", "n/a", "n/a"



# map the SIC code prefixes to the specific SIC Codes and desriptions outlines in our variable. This enhance the output dataset from showing "49410" to "Freight transport by road" - much more friendly
def map_sic_descriptions_list(value):

    if value is None or pd.isna(value):

        return []



    if isinstance(value, str):

        codes = [v.strip() for v in value.split(",")]

    else:

        codes = list(value)



    return [

        {

            "sic": code,

            "description": SIC_CODES_DESC.get(code, "Unknown SIC code")

        }

        for code in codes

    ]



# scoring - this assesses the completeness of the latest accounts, size and documents present in companies house to score the 'health' of the business.
def lead_score(profile: dict) -> int:

    score = 0

    now = datetime.now()



    # ---------- Status ----------

    if profile.get("company_status") == "active":

        score += 30

    else:

        return 0  # hard stop for non-active companies



    # ---------- Accounts ----------

    accounts = profile.get("accounts") or {}

    last_accounts = accounts.get("last_accounts") or {}

    if accounts:

        if not accounts.get("overdue", False):

            score += 25

        else:

            score -= 40



        acct_type = (last_accounts.get("type") or "").lower()



        if acct_type in {"small", "medium", "full", "group"}:

            score += 10

        elif acct_type in {"dormant", "micro-entity"}:

            score -= 25



        made_up_to = last_accounts.get("made_up_to")

        if made_up_to:

            try:

                filed_date = datetime.fromisoformat(made_up_to)

                if filed_date >= now - timedelta(days=548):  # ~18 months

                    score += 10

            except ValueError:

                pass



    # ---------- Confirmation Statement ----------

    cs = profile.get("confirmation_statement") or {}

    if cs:

        if not cs.get("overdue", False):

            score += 20

        else:

            score -= 30



        cs_date = cs.get("last_made_up_to")

        if cs_date:

            try:

                cs_dt = datetime.fromisoformat(cs_date)

                if cs_dt >= now - timedelta(days=730):

                    score += 10

            except ValueError:

                pass



    # ---------- Risk flags ----------

    if profile.get("has_insolvency_history"):

        score -= 50

    if profile.get("undeliverable_registered_office_address"):

        score -= 30



    # ---------- Activity signals ----------

    if profile.get("has_charges"):

        score += 5



    return max(score, 0)

# =====================
# MAIN - main trigger point for the code and other helper functions above
# =====================
def main():

    ch_key = COMPANIES_HOUSE_API_KEY
    #active: company is currently in operation - interest in buying trucks?
    #receivership: when a creditor appoints someone to sell assets and recover debts - trucks available for sale?
    #voluntary-arrangement: currently trading but is restructuring to pay off debts - trucks available for sale?
    #liquidation: no longer trading but is selling assets to settle debts and distribute funds to shareholders - trucks available for sale?
    #administration: administrator is appointed to manage the company, often with the goal of rescuing it or obtaining the best possible return for creditors - trucks available for sale?
    #insolvency-proceedings: process before liquidation to try and rescue the company - trucks available for sale?

    print("beginning advanced search.....")

    search = ch_get(

        "/advanced-search/companies",

        ch_key,

        params={

            "company_status": "active",

            "sic_codes": "49410,52103",

            "size": COMPANIES_LIMIT,

            "incorporated_from": "2020-01-01"

        },

    )

    counter = 1

    companies = []

    df_companies = pd.DataFrame(search.get("items", []))

    df_companies.to_csv("companies_list.csv")



    print("getting company profiles......")
    for item in search.get("items", []):

        number = item.get("company_number")
        addr = item.get("registered_office_address") or {}
        postcode = addr.get("postal_code")



        if not number or not postcode:

            continue



        profile = ch_get(f"/company/{number}", ch_key)
        incorporation_date = profile.get("date_of_creation")
        sic_codes = profile.get("sic_codes", [])


        if not is_transport(sic_codes):

            continue

        area = extract_postcode_area(postcode)
        if area not in POSTCODE_AREA_CENTROIDS:

            continue

        size = size_score(profile)
        distance = haversine_km(

            [postcode]_LAT_LON,

            POSTCODE_AREA_CENTROIDS[area]
        )

        company_score = lead_score(profile)

        parent_name, parent_number, activity = get_branch_info(profile)

        companies.append({

            "company": profile.get("company_name"),
            "company_status": profile.get("company_status"),
            "parent_company": parent_name,
            "company_number": number,
            "parent_company_number": parent_number,
            "incorporation_date": incorporation_date,
            "postcode": postcode,
            "sic_codes": ", ".join(sic_codes),
            "activity": activity,
            "size_score": size,
            "distance_km": round(distance, 2),
            "company_score": company_score,
        })

        print(str(counter) + " Company: "+profile.get("company_name") + ", score: " + str(company_score) + ", size score: " + str(size))
        counter += 1

        time.sleep(0.6) #wait 0.6 seconds to loop through 600 companies in at least 5 mins - limit imposed by API



    df = pd.DataFrame(companies)

    df = df[(df["size_score"] >= 20) & (df["company_score"] >= 45)] # small and above, healthy companies

    df['sic_codes'] = df['sic_codes'].map(map_sic_descriptions_list).replace(r"[\[\]]","", regex=True)

    #new businesses
    df = df.sort_values(

        by=["size_score","company_score","incorporation_date","distance_km"],

        ascending=[False,False,False,True]

    ).head(25)

    print(df.to_string(index=False))
    df.to_csv("transport_logistics_healthy_companies.csv", index=False)

if __name__ == "__main__":

    main()

Tags: , , ,

« Previous Post
Next Post »