Skip to content

Instantly share code, notes, and snippets.

@johnukfr

johnukfr/DevRefinitiv Q&A Python RD equityBySubSectorScreen

Last active February 2, 2023 07:25
Show Gist options
  • Save johnukfr/834233fdd50e1c5c15d635ef034d7bc7 to your computer and use it in GitHub Desktop.
Save johnukfr/834233fdd50e1c5c15d635ef034d7bc7 to your computer and use it in GitHub Desktop.
Download ZIP
Can I use LSEG-Refinitiv's Screener to get a data-frame of Revenue values for companies broken down by sector in Codebook? Yes you can!
Raw
DevRefinitiv Q&A Python RD equityBySubSectorScreen
import refinitiv.data as rd
from refinitiv.data.content import search
import pandas as pd
import re
from typing import List
try: # The following libraries are not available in Codebook, thus this try loop. You can download `Example.DataLibrary.Python-main` at https://github.com/Refinitiv-API-Samples/Example.DataLibrary.Python/tree/main/Configuration
rd.open_session(config_name="C:\\Example.DataLibrary.Python-main\\Example.DataLibrary.Python-main\\Configuration\\refinitiv-data.config.json")
rd.open_session("desktop.workspace")
except:
rd.open_session()
def EquityBySubSectorScreen(
dataField: List[str] = ["TR.TotalRevenue"],
screenOnName: str = "TR.TRBCActivityCode",
screenOn: List[str] = [i.strip() for i in [' 5010101010', ' 5010101011', ' 5010101012', ' 5010201010', ' 5010202010', ' 5010202011', ' 5010202012', ' 5010202013', ' 5010202014', ' 5010202015', ' 5010203010', ' 5010203011', ' 5010203012', ' 5010203013', ' 5010301010', ' 5010301011', ' 5010301012', ' 5010301013', ' 5010301014', ' 5010301015', ' 5010302010', ' 5010302011', ' 5010302012', ' 5010302013', ' 5010303010', ' 5010303011', ' 5010303012', ' 5010303013', ' 5010303014', ' 5010303015', ' 5020101010', ' 5020101011', ' 5020101012', ' 5020101013', ' 5020101014', ' 5020101015', ' 5020101016', ' 5020101017', ' 5020101018', ' 5020101019', ' 5020101020', ' 5020102010', ' 5020102011', ' 5020102012', ' 5020102013', ' 5020102014', ' 5020102015', ' 5030101010', ' 5030101011', ' 5030101012', ' 5110101010', ' 5110101011', ' 5110101012', ' 5110101013', ' 5110101014', ' 5110101015', ' 5110101016', ' 5110101017', ' 5110102010', ' 5110102011', ' 5110102012', ' 5110102013', ' 5110102014', ' 5110103010', ' 5110103011', ' 5110103012', ' 5110103014', ' 5110103016', ' 5110103017', ' 5110103018', ' 5110103019', ' 5110109010', ' 5120101010', ' 5120101011', ' 5120101012', ' 5120101013', ' 5120101014', ' 5120101015', ' 5120101016', ' 5120102010', ' 5120102011', ' 5120102012', ' 5120102013', ' 5120102014', ' 5120102015', ' 5120102016', ' 5120103010', ' 5120103011', ' 5120103012', ' 5120103013', ' 5120103014', ' 5120103015', ' 5120103016', ' 5120105010', ' 5120105011', ' 5120105012', ' 5120105013', ' 5120105014', ' 5120105015', ' 5120105016', ' 5120105017', ' 5120106010', ' 5120106011', ' 5120106012', ' 5120107010', ' 5120107011', ' 5120107012', ' 5120107013', ' 5120108010', ' 5120201010', ' 5120201011', ' 5120201012', ' 5120201013', ' 5120201014', ' 5120201015', ' 5130101010', ' 5130101011', ' 5130101012', ' 5130101013', ' 5130101014', ' 5130101015', ' 5130101016', ' 5130102010', ' 5130102011', ' 5130102012', ' 5130102013', ' 5130102014', ' 5130201010', ' 5130201011', ' 5130201012', ' 5130201013', ' 5130201014', ' 5130201015', ' 5130201016', ' 5130202010', ' 5130202011', ' 5210101010', ' 5210101011', ' 5210101012', ' 5210101013', ' 5210101014', ' 5210101015', ' 5210101016', ' 5210101017', ' 5210101018', ' 5210101019', ' 5210101021', ' 5210201010', ' 5210201011', ' 5210201012', ' 5210201013', ' 5210201014', ' 5210201015', ' 5210201016', ' 5210201017', ' 5210201018', ' 5210201019', ' 5210201020', ' 5210201021', ' 5210201022', ' 5210201023', ' 5210201024', ' 5210201025', ' 5210202010', ' 5210202011', ' 5210202012', ' 5210202013', ' 5210202014', ' 5210202015', ' 5210202016', ' 5210202017', ' 5210203010', ' 5210203011', ' 5210203012', ' 5210203013', ' 5210203014', ' 5210203015', ' 5210203016', ' 5210203017', ' 5210203018', ' 5210203019', ' 5210203020', ' 5210204010', ' 5210204011', ' 5210204012', ' 5210204013', ' 5210204014', ' 5210204015', ' 5210204016', ' 5210205010', ' 5210205011', ' 5210205012', ' 5220102010', ' 5220102011', ' 5220102012', ' 5220102013', ' 5220102014', ' 5220102015', ' 5220102016', ' 5220102017', ' 5220102018', ' 5220102019', ' 5220102020', ' 5220102021', ' 5220102022', ' 5220102023', ' 5220201010', ' 5220301010', ' 5220301011', ' 5220301012', ' 5220301013', ' 5220301014', ' 5220301015', ' 5220302010', ' 5220302011', ' 5220302012', ' 5220302013', ' 5220303010', ' 5220303011', ' 5220303012', ' 5220303013', ' 5220303014', ' 5220304010', ' 5220304011', ' 5220304012', ' 5220304013', ' 5220304014', ' 6310301010', ' 5220304016', ' 5220304017', ' 5220304018', ' 5220304019', ' 5220304020', ' 5220304021', ' 5220304022', ' 5220304023', ' 5220304024', ' 5220304025', ' 5220304026', ' 5220304027', ' 5220306010', ' 5220306011', ' 5220306012', ' 5220306013', ' 5220306014', ' 5220307010', ' 5220307011', ' 5220307012', ' 5220307013', ' 5220307014', ' 5220307015', ' 5220307016', ' 5220307017', ' 5220307018', ' 5240501010', ' 5240501011', ' 5240501012', ' 5240501013', ' 5240501014', ' 5240502010', ' 5240502011', ' 5240502012', ' 5240502013', ' 5240503010', ' 5240503011', ' 5240503012', ' 5240503013', ' 5240503014', ' 5240601010', ' 5240601011', ' 5240601012', ' 5240601013', ' 5240601014', ' 5240602010', ' 5240602011', ' 5240602012', ' 5240602013', ' 5240602014', ' 5240602015', ' 5240602016', ' 5240602017', ' 5240701010', ' 5240701011', ' 5240701012', ' 5240701013', ' 5240701014', ' 5240702010', ' 5240702011', ' 5240702012', ' 5240702013', ' 5240703010', ' 5240703011', ' 5240703012', ' 5240703013', ' 5310101010', ' 5310101011', ' 5310101012', ' 5310101013', ' 5310101014', ' 5310101015', ' 5310101016', ' 5310102010', ' 5310102011', ' 5310102012', ' 5310102013', ' 5310102014', ' 5310102015', ' 5310102016', ' 5310102017', ' 5310103010', ' 5310103011', ' 5310103012', ' 5310103013', ' 5310103014', ' 5310103015', ' 5320201010', ' 5320201011', ' 5320201012', ' 5320201013', ' 5320201014', ' 5320201015', ' 5320201016', ' 5320201017', ' 5320201018', ' 5320201019', ' 5320202010', ' 5320202011', ' 5320202012', ' 5320202013', ' 5320202014', ' 5320202015', ' 5320202016', ' 5320202017', ' 5320202018', ' 5320202019', ' 5320202020', ' 5320202021', ' 5320202022', ' 5320202023', ' 5320202024', ' 5320202025', ' 5320202026', ' 5320202027', ' 5320202028', ' 5320202029', ' 5320202030', ' 5320203010', ' 5320203011', ' 5320203012', ' 5320203013', ' 5320203014', ' 5320203015', ' 5320203016', ' 5320203017', ' 5320301010', ' 5320301011', ' 5320301012', ' 5320301013', ' 5320301014', ' 5320301015', ' 5320301016', ' 5320302010', ' 5320302011', ' 5320302012', ' 5320302013', ' 5320302014', ' 5320302015', ' 5320302016', ' 5320302017', ' 5320302018', ' 5320302019', ' 5320302020', ' 5320403010', ' 5320403011', ' 5320403012', ' 5320403013', ' 5320403014', ' 5320403015', ' 5320403016', ' 5320404010', ' 5320404011', ' 5320404012', ' 5320404014', ' 5320404015', ' 5320404016', ' 5320404017', ' 5320404018', ' 5320501010', ' 5320501011', ' 5320501012', ' 5320501013', ' 5320501014', ' 5320501015', ' 5320501016', ' 5320502010', ' 5320502011', ' 5320502012', ' 5320502013', ' 5320502014', ' 5320502015', ' 5320502016', ' 5320502017', ' 5330101010', ' 5330101011', ' 5330101012', ' 5330101013', ' 5330101014', ' 5330101015', ' 5330101016', ' 5330101017', ' 5330102010', ' 5330102011', ' 5330102012', ' 5330102013', ' 5330102014', ' 5330102015', ' 5330102016', ' 5330103010', ' 5330103011', ' 5330103012', ' 5330103013', ' 5330103014', ' 5330103015', ' 5330104010', ' 5330104011', ' 5330104012', ' 5330104013', ' 5330104014', ' 5330104015', ' 5330104016', ' 5330104017', ' 5330104018', ' 5330104019', ' 5330104020', ' 5330104021', ' 5330104022', ' 5330104023', ' 5330201010', ' 5330201011', ' 5330201012', ' 5330201013', ' 5330201014', ' 5330201015', ' 5330201016', ' 5330201017', ' 5330201018', ' 5330201019', ' 5330201020', ' 5330201021', ' 5330201022', ' 5330202010', ' 5330202011', ' 5330202012', ' 5330202013', ' 5330203010', ' 5330203011', ' 5330203012', ' 5330203013', ' 5330203014', ' 5330203015', ' 5330203016', ' 5330204010', ' 5330204011', ' 5330204012', ' 5330204013', ' 5330204014', ' 5330204015', ' 5330204016', ' 5330204017', ' 5340201010', ' 5340201011', ' 5340201012', ' 5340201013', ' 5340202010', ' 5340202011', ' 5340202012', ' 5340202013', ' 5340202014', ' 5340202015', ' 5340301010', ' 5340301011', ' 5340301012', ' 5340301013', ' 5340301014', ' 5340301015', ' 5340301016', ' 5340302010', ' 5340302011', ' 5340302012', ' 5340302013', ' 5340302014', ' 5340302015', ' 5340302016', ' 5340302017', ' 5340303010', ' 5340303011', ' 5340303012', ' 5340303013', ' 5340303014', ' 5340303015', ' 5340303016', ' 5340304010', ' 5340304011', ' 5340304012', ' 5340304013', ' 5340304014', ' 5340304015', ' 5340304016', ' 5340304017', ' 5340304018', ' 5340304019', ' 5340305010', ' 5340305011', ' 5340305012', ' 5340305013', ' 5340309010', ' 5340309011', ' 5340309012', ' 5340309013', ' 5340309014', ' 5340309015', ' 5340309016', ' 5340309017', ' 5340309018', ' 5340309019', ' 5340309020', ' 5340309021', ' 5340309022', ' 5340309023', ' 5340309024', ' 5340309025', ' 5410101010', ' 5410101011', ' 5410102010', ' 5410102011', ' 5410102012', ' 5410102013', ' 5410103010', ' 5410103011', ' 5410103012', ' 5410103013', ' 5410103014', ' 5410201010', ' 5410201011', ' 5410201012', ' 5410201013', ' 5410201014', ' 5410201015', ' 5410201016', ' 5410201017', ' 5410201018', ' 5410201019', ' 5410201020', ' 5410201021', ' 5410201022', ' 5410201023', ' 5410201024', ' 5410201025', ' 5410201026', ' 5410201027', ' 5410201028', ' 5410201029', ' 5410201030', ' 5410202010', ' 5410202011', ' 5410202012', ' 5410202013', ' 5410202014', ' 5410202015', ' 5410202016', ' 5410202017', ' 5410202018', ' 5410202019', ' 5410202020', ' 5410202021', ' 5410202022', ' 5410202023', ' 5410202024', ' 5410202025', ' 5410202026', ' 5410202027', ' 5410202028', ' 5410202030', ' 5410202031', ' 5410202032', ' 5410203010', ' 5410203011', ' 5410203012', ' 5410203013', ' 5410203014', ' 5420101010', ' 5420101011', ' 5420101012', ' 5420101013', ' 5420101015', ' 5420101016', ' 5420102010', ' 5420102011', ' 5420102012', ' 5420102013', ' 5420102014', ' 5420102015', ' 5420102016', ' 5420103010', ' 5420103011', ' 5420103012', ' 5420103013', ' 6310101010', ' 5420103015', ' 5420103016', ' 5420103017', ' 5420103018', ' 5430101010', ' 5430101011', ' 5430101012', ' 5430101013', ' 5430101014', ' 5430102010', ' 5430102011', ' 5430102012', ' 5430102013', ' 5430102014', ' 5430102015', ' 5430102016', ' 5440101010', ' 5510101010', ' 5510101011', ' 5510101012', ' 5510101013', ' 5510101014', ' 5510101015', ' 5510103010', ' 5510103011', ' 5510103012', ' 5510103013', ' 5510103014', ' 5510103015', ' 5510105010', ' 5510105011', ' 5510105012', ' 5510105013', ' 5510105014', ' 5510105015', ' 5510201010', ' 5510201011', ' 5510201012', ' 5510201013', ' 5510201014', ' 5510201015', ' 5510202010', ' 5510202011', ' 5510202012', ' 5510202013', ' 5510202014', ' 5510202015', ' 5510202016', ' 5510202017', ' 5510203010', ' 5510205010', ' 5510205011', ' 5510205012', ' 5730101010', ' 5730101020', ' 5730101030', ' 5730101040', ' 5730102010', ' 5730102020', ' 5730102030', ' 5730103010', ' 5730103020', ' 5730103030', ' 5730103040', ' 5730103050', ' 5730109010', ' 5530101010', ' 5530101011', ' 5530101012', ' 5530102010', ' 5530102011', ' 5530102012', ' 5530102013', ' 5530102014', ' 5530103010', ' 5530103011', ' 5530103012', ' 5530105010', ' 5530105011', ' 5530105012', ' 6010101010', ' 6010101020', ' 6010101030', ' 6010101040', ' 6010101050', ' 6010102010', ' 6010102020', ' 6010102030', ' 6010102040', ' 6010102050', ' 6010201010', ' 6010202010', ' 6010202020', ' 6010202030', ' 6010202040', ' 6010203010', ' 6010204010', ' 6010204020', ' 6010204030', ' 6010204040', ' 6010204050', ' 6010204060', ' 6010204070', ' 5550101010', ' 5550102010', ' 5550102011', ' 5550103010', ' 5550104010', ' 5550104011', ' 5550104012', ' 5550105010', ' 5550106010', ' 5560101010', ' 5560101011', ' 5610101010', ' 5610101011', ' 5610101012', ' 5610101013', ' 5610101014', ' 5610101015', ' 5610101016', ' 5610102010', ' 5610102011', ' 5610102012', ' 5610102013', ' 5610102014', ' 5610102015', ' 5610102016', ' 5610102017', ' 5610102018', ' 5610102019', ' 5610201010', ' 5610201011', ' 5610201012', ' 5610201013', ' 5610201014', ' 5610201015', ' 5610201016', ' 5610201017', ' 5610201018', ' 5610201019', ' 5610201020', ' 5610202010', ' 5610202011', ' 5620104010', ' 5620104011', ' 5620104012', ' 5620104013', ' 5620104014', ' 5620104015', ' 5620104016', ' 5620104017', ' 5620104018', ' 5620201010', ' 5620201011', ' 5620201012', ' 5620201013', ' 5710101010', ' 5710101011', ' 5710101012', ' 5710101013', ' 5710101014', ' 5710101015', ' 5710102010', ' 5710102011', ' 5710102012', ' 5710102013', ' 5710201010', ' 5710201011', ' 5710201012', ' 5710201013', ' 5710201014', ' 5710201015', ' 5710201016', ' 5710201017', ' 5710401010', ' 5710401011', ' 5710401012', ' 5710401013', ' 5710401014', ' 5710401015', ' 5710501010', ' 5710501011', ' 5710501012', ' 5710501013', ' 5710501014', ' 5710501015', ' 5710601010', ' 5710601011', ' 5710601012', ' 5710601013', ' 5710601014', ' 5710601015', ' 5710601016', ' 5710601017', ' 5710601018', ' 5710601019', ' 5710602010', ' 5710602011', ' 5710602012', ' 5710602013', ' 5710602014', ' 5710602015', ' 5710603010', ' 5710603011', ' 5710603012', ' 5710603013', ' 5710603014', ' 5710701010', ' 5720101010', ' 5720101011', ' 5720101012', ' 5720101013', ' 5720101014', ' 5720101015', ' 5720101016', ' 5720102010', ' 5720102011', ' 5720102012', ' 5720102013', ' 5720102014', ' 5720102015', ' 5720102016', ' 5720102017', ' 5720102018', ' 5720103010', ' 5720103011', ' 5720103012', ' 5720103013', ' 5720103014', ' 5720103015', ' 5720103016', ' 5740101010', ' 5740101020', ' 5740101030', ' 5740101040', ' 5740101050', ' 5740101060', ' 5740102010', ' 5740102020', ' 5740102030', ' 5740102040', ' 5740102050', ' 5910101010', ' 5910101012', ' 5910101013', ' 5910101014', ' 5910101020', ' 5910101021', ' 5910101022', ' 5910101023', ' 5910101024', ' 5910101025', ' 5910102010', ' 5910102011', ' 5910102012', ' 5910102013', ' 5910201010', ' 5910201011', ' 5910301010', ' 5910301011', ' 5910301012', ' 5910301013', ' 5910401010', ' 6110101010', ' 6110102010', ' 6110103010', ' 6110104010', ' 6110105010', ' 6110105020', ' 6110105030', ' 6110105040', ' 6210101010', ' 6210101020', ' 6210102010', ' 6210102020', ' 6210102030', ' 6210103010', ' 6210104010', ' 6310101010', ' 6310201010', ' 6310201020', ' 6310201030', ' 6310201040', ' 6310201050', ' 6310201060', ' 6310301010']],
batchesOf: int = 10,
ranges: range = range(2011, 2023),
sectorFieldNames: List[str] = [
"TR.TRBCEconomicSector", "TR.TRBCBusinessSector",
"TR.TRBCIndustryGroup", "TR.TRBCIndustry",
"TR.GICSIndustry", "TR.GICSIndustryGroup",
"TR.TRBCActivity", "TR.TRBCActivityCode",
"TR.NetDebt", "TR.EBITDA", "TR.CapitalExpenditures"],
renameCols: bool = True,
debug: bool = False
) -> pd.DataFrame:
"""EquityBySubSectorScreen V2.0:
Changes
----------------------------------------------
Changed from Version 1.0 to 2.0: Jonathan Legrand:
- allowed `dataField` argument to be a List[str] for multiple fields over time periods.
- changed the name of the function from `equityBySubSectorScreen` to `EquityBySubSectorScreen`
"""
i: int
j: str
m: str
n: int
def chunks(lst: List, n: int) -> List:
"""Yield successive n-sized chunks from lst."""
for i in range(0, len(lst), n):
yield lst[i:i + n]
reqs: List[str] = [
re.sub(r"[\[\]]", "", j)
for j in [
f'SCREEN(U(IN(EQUITY(Public,Active,Primary))),IN({screenOnName},{m}))'
for m in chunks(screenOn, batchesOf)]]
# Fields
flds: List[str] = sectorFieldNames.copy()
for m in dataField:
for i in [f'{m}(Period=FY{j})' for j in [str(n) for n in ranges]]:
flds.append(i)
# Call
responceDf: pd.DataFrame = rd.get_data(universe=reqs[0], fields=flds)
# Column Name Replacements:
if renameCols:
col: List[str] = [str(i) for i in list(responceDf.columns)[:len(sectorFieldNames)+1]]
if debug: print(f"col before 1st loop: {col}")
for m in dataField:
for i in [f"{m}{j}" for j in [str(n) for n in ranges]]:
col.append(i)
if debug: print(f"col after 1st loop: {col}")
responceDf.columns = col
# Loop
k: List[str]
for k in reqs[1:]:
responceDf2: pd.DataFrame = rd.get_data(universe=k, fields=flds)
if renameCols:
responceDf2.columns = col
responceDf: pd.DataFrame = responceDf.append(responceDf2, ignore_index=True)
return responceDf
test1 = EquityBySubSectorScreen(
dataField=["TR.TotalRevenue", "TR.NetDebt", "TR.EBITDA", "TR.CapitalExpenditures"],
sectorFieldNames=[
"TR.TRBCEconomicSector", "TR.TRBCBusinessSector",
"TR.TRBCIndustryGroup", "TR.TRBCIndustry",
"TR.GICSIndustry", "TR.GICSIndustryGroup",
"TR.TRBCActivity", "TR.TRBCActivityCode"])
test1.to_excel("EquityBySubSectorScreenALLv1.xlsx")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment