0

I use excel to build financial models. First, I download excel sheets from the SEC website and then go through a manual, hideous process of using specific columns, substituting them into formulas and then getting my results.

I was wondering if it is possible to code a program that would pull out specific data from my excel spreadsheet and compute using a set formula. For example, if I need to calculate: leverag = assets/equity .The assets can be found on excel in the cell next to “Total assets” and equity next to “total stockholders’ equity”. Is there a code that could pull out the data from these cells and then compute using a formula?

Example data that I use can be found here https://www.sec.gov/ix?doc=/Archives/edgar/data/789019/000156459020034944/msft-10k_20200630.htm#ITEM_6_SELECTED_FINANCIAL_DATA page 57

The leverage for this example is equal to 2.5469 for year 2020 (because total assets=301311 and total equity = 118304, dividing assets by equity equals 2.5469)

I would really appreciate your answers, as they will make my life much easier (best Christmas present ever) :)

user14891498
  • 1
  • 1
  • You can definitely use pandas for this. However you must provide some sample of your data, in order to get decent answers. Check here for more details: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples – IoaTzimas Dec 26 '20 at 11:11
  • @IoaTzimas I’m sorry, I’m new to the website, how do I add data? Can I just drop a link to the SEC files of any company ? Also, thanks for your response – user14891498 Dec 26 '20 at 11:26
  • Best way to provide data is described in the link i provided. If it's not easy for you, just provide a sample of your table in order to get some original idea how to proceed. Pictures and links are not recommended, however it is better than nothing, if there is no other way – IoaTzimas Dec 26 '20 at 11:30
  • Yes, the link does look complicated indeed. I’ll see what I can do, thanks – user14891498 Dec 26 '20 at 11:36
  • Just paste an image of your excel file (with columns included) if everything else is difficult for you. Also, please provide some example of your expected output, Will it be new columns based on calculations of other columns? There is definitely a way to automate such tasks with pandas – IoaTzimas Dec 26 '20 at 11:39
  • Is it better now? thanks for your advice – user14891498 Dec 26 '20 at 11:44

1 Answers1

0

openpyxl should get you started. Simple example:

import openpyxl
wb = openpyxl.load_workbook('sec_workbook.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
sheet['A1'].value

You can find more info for openpyxl in a great book named "Automate the Boring Stuff" by Al Sweigert or at https://openpyxl.readthedocs.io/en/stable/

jim
  • 401
  • 4
  • 10