How to get Company Geographic Sales Breakdown

Published in

LSEG Developer Community

9 min readApr 19, 2021

The original article on the Refinitiv Developer Community is available here.

Overview

Today, each company does not limit the sale target just only their region. All companies expand their customers across global thanks to the efficiency of the internet and transportation that connects customers from all over the world to the company.

However, each country has different preferences and habits that affect sales performance and revenue. Understand geographic sales breakdown help company and investor analyze the revenue from each geographic region to prepare the investment decision or manage the sale strategy for each region.

The article uses geographic breakdown sale revenues of the semiconductor companies such as Advanced Micro Devices, Intel and TSMC data from Eikon Data API as an example of a dataset.

Note:

All figures and reports demonstrate data queried on 19th April 2021.

Refinitiv’s Reuters Fundamentals Overview

Reuters Fundamentals has over 35 years of experience in collecting and delivering the most timely and highest quality fundamentals data in the industry, including an unmatched depth and breadth of primary financial statements, footnote items, segment data, industry specific operating metrics, financial ratios, and much more.

Reuters Fundamentals standardized and As Reported financial statement data — both interim and annual — along with per-share data, calculated financial ratios, company profile information, security data, Officers & Directors and market content for over 90,000 issuers.

Geographic Breakdown Fundamentals Data

The Company’s geographic breakdown fundamentals data are listed under the following Eikon Data fields:

TR.BGS.GeoTotalRevenue.segmentName: Segment (Geographic) data
TR.BGS.GeoTotalRevenue: Each segment revenue value

This information can be search in Refinitiv Workspace (or Refinitiv Eikon desktop) Data Item Browser application under Content Classification -> Reuters Fundamentals -> Business and Geographic Segments - Geographic Segment.

Implementation Process

The demo application is available at GitHub page. Please check the project README.md file for more detail regarding how to run the Jupyter Notebook application.

There are three main steps to get and display company’s geographic sale data.

Get the Company Geographic Sale Data.
Restructure Company Geographic Sale Data Dataframe object that returned from Eikon Data API.
Plotting the graph.

Introduction to Eikon Data API

The Eikon Data API (aka DAPI) provides access to certain Refinitiv Eikon/Refinitiv Workspace data with seamless workflow with the same data across all applications running on the desktop. The API allows developers to tap into the full breadth of community tools through a modern API with native Python support.

If you are not familiar with Eikon Data API or Python, the following resources are highly recommended to read before you proceed with further steps.

Note:

This project is based on Eikon Data API versions 1.1.8.
Pleases see Eikon Data API Usage and Limits Guideline regarding the API data coverage and data limit.

Connecting to Eikon Data API

This code sets the app_id to connect to the Eikon Data API via the Refinitiv Workspace/Eikon Desktop Application which needs to be running locally. It requires the previously created text file eikon.cfg to be in the current working directory.

You should save a text file with filename eikon.cfg having the following contents:

[eikon]
app_id = YOUR_APP_ID_HERE

This file should be readily availble (e.g. in the current working directory) for the next steps.

#Imporint Eikon and Main Modules

import eikon as ek
import configparser as cp
import warnings
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib
import matplotlib.ticker as tick
from matplotlib.ticker import FuncFormattercfg = cp.ConfigParser()
cfg.read('eikon.cfg')  # adjust for different file location['eikon.cfg']

Please note that the Refintiv Workspace/Eikon application integrates a Data API proxy that acts as an interface between the Eikon Data API Python library and the Eikon Data Platform. For this reason, the Refinitiv Workspace/Eikon application must be running when you use the Eikon Data API Python library.

ek.set_app_key(cfg['eikon']['app_id'])

Get the Company Geographic Sale Data, Lets try with AMD

Firstly, we use request company fundamentals via Eikon Data API get_data function with following fields:

TR.BGS.GeoTotalRevenue.segmentName: Segment (Geographic) data
TR.BGS.GeoTotalRevenue: Each segment revenue value
TR.CompanyName: Get company name

Firstly, We will request AMD.O RIC data to explore Advanced Micro Devices, Inc geographic sale revenue as an example data.

df,err = ek.get_data('AMD.O', ['TR.BGS.GeoTotalRevenue.segmentName', 'TR.BGS.GeoTotalRevenue','TR.CompanyName'])
df

This get_data function returns data as Pandas Dataframe object by default.

The returned Dataframe contains the total sale values of all regions in Segment’s Segment Total and Consolidated Total rows. In the same time, the company name information is available at the first row of Dataframe object which is the only row that contains Company Name column data.

Lets try plotting a graph

Basically, we can use this Dataframe to plot a bar graph as it is. However, the result is hard to read and analysis sale revenue at all.

df_plot = df.copy()
df_plot.set_index('Segment Name',drop=True,inplace=True)
fig = plt.figure()
df_plot.plot(kind='barh', ax = fig.gca())
plt.show()

So we need to restructure the Dataframe and make data easier to read before plotting a graph.

Restructure Company Geographic Sale Data Dataframe object.

We start by renaming the Segment Name and Geographic Total Revenues (Calculated) columns to readable names like Geographic and Sales in £m.

df_amd = df.copy()

df_amd.rename(columns={
                   'Segment Name':'Geographic',
                   'Geographic Total Revenues (Calculated)':'Revenues'},
         inplace = True)

df_amd

Then we get the Company name and consolidate total revenue information from Dataframe object.

total_sale = df_amd.iloc[df_amd.shape[0] - 1]['Revenues']

company_name = df_amd.iloc[0]['Company Name']

And the last thing on this phase is to remove the Total Sale Revenue rows from the Dataframe, we will display the consolidated revenue information as a graph footer instead.

df_amd = df_amd[df_amd['Geographic'] != 'Segment Total']
df_amd = df_amd[df_amd['Geographic'] != 'Consolidated Total']

Plotting the graph

Finally, we are now ready for plotting a readable graph. We create a Python function format_revenues_number to reformat large revenue numbers into a readable numbers in trillions, billions or millions unit. This function source code is based on Dan Friedman's How to Format Large Tick Values tutorial source code via GitHub.

def format_revenues_number(tick_val, pos):
    """
    Turns large tick values (in the trillions, billions, millions and thousands) such as 4500 into 4.5K 
    and also appropriately turns 4000 into 4K (no zero after the decimal).
    """
    if tick_val >= 1000000000000: # Add support for trillions
        val = round(tick_val/1000000000000, 1)
        new_tick_format = '{:}T'.format(val)
    elif tick_val >= 1000000000:
        val = round(tick_val/1000000000, 1)
        new_tick_format = '{:}B'.format(val)
    elif tick_val >= 1000000:
        val = round(tick_val/1000000, 1)
        new_tick_format = '{:}M'.format(val)
    elif tick_val >= 1000:
        val = round(tick_val/1000, 1)
        new_tick_format = '{:}K'.format(val)
    elif tick_val < 1000:
        new_tick_format = round(tick_val, 1)
    else:
        new_tick_format = tick_val

     # make new_tick_format into a string value
    new_tick_format = str(new_tick_format)
    """
    code below will keep 4.5M as is but change values such as 4.0M to 4M since that 
    zero after the decimal isn't needed
    """
    index_of_decimal = new_tick_format.find(".")
    
    if index_of_decimal != -1:
        value_after_decimal = new_tick_format[index_of_decimal+1]
        if value_after_decimal == "0":
            # remove the 0 after the decimal point since it's not needed
            new_tick_format = new_tick_format[0:index_of_decimal] + new_tick_format[index_of_decimal+2:]
            
    return new_tick_format

We use Python matplotlib.pyplot library to plot a bar graph that represent each region revenue from restructured Dataframe object in Jupyter Notebook.

# Plotting a Graph

df_amd.set_index('Geographic',drop=True,inplace=True)
fig = plt.figure()

#Format Total Sale display unit as a graph footer.
fig.text(.5, -.05, 'Total Sale %s' %(f'{total_sale:,.2f}'), ha='center',fontsize='large')

# Create graph title from Company and RIC names dynamically.
plt.ticklabel_format(style = 'plain')
plt.title('%s (%s) Geographic Sale Breakdown' % (company_name, 'AMD.OQ'), color='black',fontsize='x-large')
ax = fig.gca()

#Apply Sale data into millions function.
formatter = FuncFormatter(format_revenues_number)
ax.xaxis.set_major_formatter(formatter)

df_amd.plot(kind='barh', ax = fig.gca())
plt.show()

Note: You can adjust a graph size to match your preference and data via a figsize parameter of Dataframe.plot() function.

Reference: Dataframe.plot() function

Lets try TSMC and Intel Global Sale Revenue

Next, lets try to get Global Sale Revenue data of 2330.TW TSMC (Taiwan Semiconductor Manufacturing Company) and INTC.O Intel Corporation.

df,err = ek.get_data(['2330.TW','INTC.O'], ['TR.BGS.GeoTotalRevenue.segmentName', 'TR.BGS.GeoTotalRevenue','TR.CompanyName'])
df

We repeat the same steps above to restructure our DataFrame object.

df.rename(columns={
                   'Segment Name':'Geographic',
                   'Geographic Total Revenues (Calculated)':'Revenues'},
         inplace = True)

df

We pass a list of RIC (['2330.TW','INTC.O']) to Eikon Data API get_data function, so the returned DataFrame object contains both TSMC and Intel data. We will separate them using DataFrame Groupby function to make it easier to process and plot graphs.

grouped = df.groupby(df.Instrument)
df_tsmc = grouped.get_group('2330.TW') #Get TSMC rows
df_tsmc

df_intel = grouped.get_group('INTC.O') #Get Intel rows
df_intel

Then we get the Company name and consolidate total revenue information from Dataframe object.

total_sale_tsmc = df_tsmc.iloc[df_tsmc.shape[0] - 1]['Revenues']

company_name_tsmc = df_tsmc.iloc[0]['Company Name']

total_sale_intel = df_intel.iloc[df_intel.shape[0] - 1]['Revenues']

company_name_intel = df_intel.iloc[0]['Company Name']

Next, we remove the Total Sale Revenue rows from the Dataframe. we will display the consolidated revenue information as a graph footer instead.

df_tsmc = df_tsmc[df_tsmc['Geographic'] != 'Segment Total']
df_tsmc = df_tsmc[df_tsmc['Geographic'] != 'Consolidated Total']

df_intel = df_intel[df_intel['Geographic'] != 'Segment Total']
df_intel = df_intel[df_intel['Geographic'] != 'Consolidated Total']

The last step is plotting a graph of Intel and TSMC.

# Plotting a Graph

df_tsmc.set_index('Geographic',drop=True,inplace=True)
fig = plt.figure()

#Format Total Sale display unit as a graph footer.
fig.text(.5, -.05, 'Total Sale %s' %(f'{total_sale_tsmc:,.2f}'), ha='center',fontsize='large')

# Create graph title from Company and RIC names dynamically.
plt.ticklabel_format(style = 'plain')
plt.title('%s (%s) Geographic Sale Breakdown' % (company_name_tsmc , '2330.TW'), color='black',fontsize='x-large')
ax = fig.gca()

#Apply Sale data into millions function.
formatter = FuncFormatter(format_revenues_number)
ax.xaxis.set_major_formatter(formatter)

df_tsmc.plot(kind='barh', ax = fig.gca())
plt.show()

# Plotting a Graph

df_intel.set_index('Geographic',drop=True,inplace=True)
fig = plt.figure()

#Format Total Sale display unit as a graph footer.
fig.text(.5, -.05, 'Total Sale %s' %(f'{total_sale_intel:,.2f}'), ha='center',fontsize='large')

# Create graph title from Company and RIC names dynamically.
plt.ticklabel_format(style = 'plain')
plt.title('%s (%s) Geographic Sale Breakdown' % (company_name_intel , 'INTC.O'), color='black',fontsize='x-large')
ax = fig.gca()

#Apply Sale data into millions function.
formatter = FuncFormatter(format_revenues_number)
ax.xaxis.set_major_formatter(formatter)

df_intel.plot(kind='barh', ax = fig.gca())
plt.show()

Conclusion

Refinitiv provides a wide range of Reuters Fundamentals data for your investment decisions including company geographic sale information. This information helps the marketing team and investor analyze the revenue from each geographic region of your interested company in both panel data and graph formats.

At the same time, the Eikon Data API lets developers rapidly access Refinitiv Eikon/Refinitiv Workspace data and our latest platform capabilities with a few lines of code that easy to understand and maintain.

References

You can find more detail regarding the Eikon Data API and related technologies for this notebook from the following resources:

For any question related to this example or Eikon Data API, please use the Developers Community Q&A Forum.