Access Executive Compensation Data With Python
In this guide, we'll explore how to find compensation data of executives working at publicly listed companies using Python and the Compensation API.
Quick Start
pip install sec-api
from sec_api import ExecCompApi
execCompApi = ExecCompApi("YOUR_API_KEY")
# Get compensation data of executives working at Microsoft
msft_compensation_data = execCompApi.get_data("MSFT")
# Get executice compensation data by CIK
compensation_data_by_cik = execCompApi.get_data("1326801")
# List all exec compensations of Tesla, Amazon, Microsoft and Google
# for the years 2021 and 2020
# sort result by total compensation per annum first, by name second
query = {
"query": "ticker:(TSLA, MSFT, AMZM, GOOG) AND (year:2021 OR year:2020)",
"from": "0",
"size": "200",
"sort": [{"total": {"order": "desc"}}, {"name.keyword": {"order": "asc"}}]
}
compensation_companies = execCompApi.get_data(query)
How to get executive compensation data by ticker?
The Python code below returns all compensation data of Microsoft, starting from the most recently reported year 2021 and going back to 2006. The response is stored in result_ticker as a list of dictionaries. The resulting list of executice compensation data can be converted into a Pandas dataframe with a single line of code. Each dict entry in the list represents an executive's compensation data for a specific year.
For example, the first element in the list result_ticker[0] represents Jean Philippe Courtois' compensation in 2021. He earned a salary of $932,167 and received stock awards of $12,141,344. His total compensation was $16,809,865.
pip install sec-api
from sec_api import ExecCompApi
execCompApi = ExecCompApi("YOUR_API_KEY")
# Get all compensation data by ticker
result_ticker = execCompApi.get_data("MSFT")
result_ticker[0]{'bonus': 0,
'changeInPensionValueAndDeferredEarnings': 0,
'cik': '789019',
'id': 'ce5ce03d1e83d40b77dc1d78c4430a11',
'name': 'Jean Philippe Courtois',
'nonEquityIncentiveCompensation': 3670406,
'optionAwards': 0,
'otherCompensation': 65948,
'position': 'Executive Vice President, President, Global Sales, Marketing and Operations',
'salary': 932167,
'stockAwards': 12141344,
'ticker': 'MSFT',
'total': 16809865,
'year': 2021}import pandas as pd
df = pd.DataFrame(result_ticker)
df.head()
| id | cik | ticker | name | position | year | salary | bonus | stockAwards | optionAwards | nonEquityIncentiveCompensation | changeInPensionValueAndDeferredEarnings | otherCompensation | total | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | ce5ce03d1e83d40b77dc1d78c4430a11 | 789019 | MSFT | Jean Philippe Courtois | Executive Vice President, President, Global Sa... | 2021 | 932167 | 0 | 12141344 | 0 | 3670406 | 0.0 | 65948 | 16809865 |
| 1 | fec80b29259087fb57609bfa798f73bc | 789019 | MSFT | Christopher D. Young | Executive Vice President, Business Development... | 2021 | 541875 | 3500000 | 22594634 | 0 | 1788188 | 0.0 | 70386 | 28495083 |
| 2 | 0f95acf65abb775c57c1f9b0e93d527e | 789019 | MSFT | Amy E. Hood | Executive Vice President and Chief Financial O... | 2021 | 995833 | 0 | 17864404 | 0 | 4543490 | 0.0 | 62414 | 23466141 |
| 3 | 3b07e7788a49cdb08cf83cf0edd394e1 | 789019 | MSFT | Satya Nadella | Chairman and Chief Executive Officer | 2021 | 2500000 | 0 | 33036030 | 0 | 14212500 | 0.0 | 109750 | 49858280 |
| 4 | 11570117c22840c4143c6c9ceeaf51b7 | 789019 | MSFT | Bradford L. Smith | President and Chief Legal Officer | 2021 | 943333 | 0 | 15098855 | 0 | 4303958 | 0.0 | 109750 | 20455896 |
How to look up executive compensation data by CIK?
In the first example we list all compensation data of Facebook by its CIK 1326801. The first item result_cik[0] shows Mark Zuckerberg with a total annual compensation of $26,823,061 in 2021.
The second example uses the query functionality of the compensation API. Here we list all compensation items of executives working at Tesla, Amazon, Microsoft and Google in 2021 and 2020. The result is sorted by total annual compensation, starting with the highest value. The top four places go to Google with a total compensation between $66 million and $50 million in 2020. In comparison, Elon Musk didn't draw a salary in 2020 at all.
pip install sec-api
from sec_api import ExecCompApi
execCompApi = ExecCompApi("YOUR_API_KEY")
# Get executice compensation data by CIK
result_cik = execCompApi.get_data("1326801")
result_cik[0]{'bonus': 0,
'changeInPensionValueAndDeferredEarnings': 0,
'cik': '1326801',
'id': 'cc3958a33b14fad760ae4a09d98283d1',
'name': 'Mark Zuckerberg',
'nonEquityIncentiveCompensation': 0,
'optionAwards': 0,
'otherCompensation': 26823060,
'position': 'Chief Executive Officer',
'salary': 1,
'stockAwards': 0,
'ticker': 'FB',
'total': 26823061,
'year': 2021}# List all exec compensations of Tesla, Amazon, Microsoft and Google
# for the years 2021 and 2020
# sort result by total compensation per annum first, by name second
query = {
"query": "ticker:(TSLA, MSFT, AMZM, GOOG) AND (year:2021 OR year:2020)",
"from": "0",
"size": "200",
"sort": [{"total": {"order": "desc"}}, {"name.keyword": {"order": "asc"}}]
}
# Important: you need an active subscription in order to access the query endpoint
result_query = execCompApi.get_data(query)
import pandas as pd
# Create a simplified list of dicts with keys:
# ticker, name, position, total compensation, year
simplified = list(map(lambda x: {"Ticker": x["ticker"],
"Name": x["name"],
"Position": x["position"],
"Total": "${:,.0f}".format(x["total"]),
"Year": x["year"],
}, result_query))
pd.DataFrame(simplified)
| Ticker | Name | Position | Total | Year | |
|---|---|---|---|---|---|
| 0 | GOOG | Philipp Schindler | Senior Vice President, Chief Business Officer,... | $66,378,500 | 2020 |
| 1 | GOOG | Prabhakar Raghavan | Senior Vice President, Knowledge and Informati... | $55,245,610 | 2020 |
| 2 | GOOG | Ruth M. Porat | Senior Vice President, Chief Financial Officer... | $50,885,683 | 2020 |
| 3 | GOOG | Kent Walker | President, Global Affairs and Chief Legal Offi... | $50,877,663 | 2020 |
| 4 | MSFT | Satya Nadella | Chairman and Chief Executive Officer | $49,858,280 | 2021 |
| 5 | TSLA | Zachary Kirkhorn | Master of Coin and Chief Financial Officer | $46,562,116 | 2020 |
| 6 | TSLA | Andrew Baglino | SVP, Powertrain and Energy Engineering | $46,544,623 | 2020 |
| 7 | TSLA | Jerome Guillen | Former President, Tesla Heavy Trucking | $46,544,623 | 2020 |
| 8 | MSFT | Satya Nadella | Chief Executive Officer and Director | $44,321,788 | 2020 |
| 9 | GOOG | Philipp Schindler | Senior Vice President, Chief Business Officer,... | $28,661,983 | 2021 |
| 10 | GOOG | Prabhakar Raghavan | Senior Vice President, Knowledge and Informati... | $28,648,009 | 2021 |
| 11 | MSFT | Christopher D. Young | Executive Vice President, Business Development... | $28,495,083 | 2021 |
| 12 | MSFT | Amy E. Hood | Executive Vice President and Chief Financial O... | $23,466,141 | 2021 |
| 13 | MSFT | Bradford L. Smith | President and Chief Legal Officer | $20,455,896 | 2021 |
| 14 | MSFT | Amy E. Hood | Executive Vice President and Chief Financial O... | $19,626,234 | 2020 |
| 15 | MSFT | Jean Philippe Courtois | Executive Vice President, President, Global Sa... | $16,809,865 | 2021 |
| 16 | MSFT | Bradford L. Smith | President and Chief Legal Officer | $16,667,713 | 2020 |
| 17 | GOOG | Ruth M. Porat | Senior Vice President, Chief Financial Officer... | $14,662,476 | 2021 |
| 18 | GOOG | Kent Walker | President, Global Affairs and Chief Legal Offi... | $14,657,762 | 2021 |
| 19 | MSFT | Jean Philippe Courtois | Executive Vice President, President, Global Sa... | $13,949,304 | 2020 |
| 20 | MSFT | Margaret L. Johnson | Executive Vice President, Business Development | $9,575,482 | 2020 |
| 21 | GOOG | Sundar Pichai | Chief Executive Officer, Alphabet and Google, ... | $7,410,162 | 2020 |
| 22 | GOOG | Sundar Pichai | Chief Executive Officer, Alphabet and Google, ... | $6,322,599 | 2021 |
| 23 | TSLA | Elon Musk | Technoking of Tesla and Chief Executive Officer | $0 | 2020 |
How to export executive compensation data into Excel?
The example code downloads all compensation data of Microsoft's executives and saves the data in the Excel file exec-compensation-MSFT.xlsx. Each row represents an executive's compensation in a single year. Salary, bonus, stock awards, option awards and more are included. The data includes over 15 years of compensation history.
We can also export compensation data from multiple companies into a single Excel sheet. Instead of quering the API by ticker, we can send a more complex search query and export the result.
pip install sec-api
from sec_api import ExecCompApi
import pandas as pd
execCompApi = ExecCompApi("YOUR_API_KEY")
# request all compensation data for Microsoft
compensation_data = execCompApi.get_data("MSFT")
# lets make the headers a bit more human readable so that
# Excel can format the table header properly
def make_row_pretty(data_row):
pretty_data_row = {
"CIK": data_row["cik"],
"Ticker": data_row["ticker"],
"Name": data_row["name"],
"Position": data_row["position"],
"Year": data_row["year"],
"Salary": data_row["salary"],
"Stock Awards": data_row["stockAwards"],
"Option Awards": data_row["optionAwards"],
"Non-Equity Incentive Compensation": data_row["nonEquityIncentiveCompensation"],
"Other Compensation": data_row["otherCompensation"],
"Total": data_row["total"]
}
return pretty_data_row
data_formatted = list(map(make_row_pretty, compensation_data))
df_formatted = pd.DataFrame(data_formatted)
df_formatted.head()
| CIK | Ticker | Name | Position | Year | Salary | Stock Awards | Option Awards | Non-Equity Incentive Compensation | Other Compensation | Total | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 789019 | MSFT | Jean Philippe Courtois | Executive Vice President, President, Global Sa... | 2021 | 932167 | 12141344 | 0 | 3670406 | 65948 | 16809865 |
| 1 | 789019 | MSFT | Christopher D. Young | Executive Vice President, Business Development... | 2021 | 541875 | 22594634 | 0 | 1788188 | 70386 | 28495083 |
| 2 | 789019 | MSFT | Amy E. Hood | Executive Vice President and Chief Financial O... | 2021 | 995833 | 17864404 | 0 | 4543490 | 62414 | 23466141 |
| 3 | 789019 | MSFT | Satya Nadella | Chairman and Chief Executive Officer | 2021 | 2500000 | 33036030 | 0 | 14212500 | 109750 | 49858280 |
| 4 | 789019 | MSFT | Bradford L. Smith | President and Chief Legal Officer | 2021 | 943333 | 15098855 | 0 | 4303958 | 109750 | 20455896 |
# save data as Excel file
df_formatted.to_excel (r'exec-compensation-MSFT.xlsx', index = None, header=True)