project_finance (3)

3 minute read

다중회사 5개년치 재무제표 주요계정 테이블

OpenDartReader 라이브러리를 활용한 재무제표 분석

project_finance

code

테스트 회사 리스트

stock_names = ["삼성전자", "SK하이닉스", "현대자동차", "현대모비스", "엔씨소프트", "원익IPS", "휴젤"]

columns

columns_infos = ["stock_code", "stock_name", "corp_code"]
columns_quarters = [
 'thstrm_amount_2021_1',
 'thstrm_amount_2021_2',
 'thstrm_amount_2020_1',
 'thstrm_amount_2020_2',
 'thstrm_amount_2020_3',
 'thstrm_amount_2020_4',
 'thstrm_amount_2019_1',
 'thstrm_amount_2019_2',
 'thstrm_amount_2019_3',
 'thstrm_amount_2019_4',
 'thstrm_amount_2018_1',
 'thstrm_amount_2018_2',
 'thstrm_amount_2018_3',
 'thstrm_amount_2018_4',
 'thstrm_amount_2017_1',
 'thstrm_amount_2017_2',
 'thstrm_amount_2017_3',
 'thstrm_amount_2017_4',
]

accounts 딕셔너리

key: 계정과목 이름 (string)

values: 계정과목 id (list)

accounts = {}
accounts["equity"] = ["ifrs_EquityAttributableToOwnersOfParent", "ifrs-full_EquityAttributableToOwnersOfParent"]
accounts["profit"] = ["ifrs_ProfitLossAttributableToOwnersOfParent", "ifrs-full_ProfitLossAttributableToOwnersOfParent"]

핵심함수

재무제표 테이블에서 원하는 계정과목의 금액을 추출해서 리스트로 반환해주는 함수

# 재무제표 테이블에서 원하는 계정과목의 금액을 추출해서 리스트로 반환해주는 함수
def find_amounts(finstate, account, accounts):
  columns = list(finstate.columns)
  df = pd.DataFrame(columns=columns)
  i = 0
  for s in finstate["account_id"]:
      # 찾고자 하는 계정과목id 이면 df에 추가 
      if s in accounts[account]:
          df = df.append(finstate.loc[i], ignore_index=True)
      i += 1
  if len(df) == 0:
      return [None] * len(finstate.columns)
  else:
      # df의 각 row값을 벡터 합 시키기
      for i in range(len(df)):
          if i == 0:
              df_tot = df.loc[i]
          else:
              df_tot += df.loc[i]
      return list(df_tot)

재무제표 테이블에서 특정 계정과목의 금액 추출하여 다중회사의 특정 계정과목 테이블에 데이터를 추가하는 함수

# 재무제표 테이블에서 특정 계정과목의 금액 추출하여 다중회사의 특정 계정과목 테이블에 데이터를 추가하는 함수
def append_amounts(account_df, stock_infos, stock_name, account, accounts): 
  stock_code = find_stock_code(stock_infos, stock_name)
  fstate_all_account = read_xlsx(stock_code)
  # columns
  columns = list(account_df.columns)
  columns_infos = columns[:3]
  columns_quarters = columns[3:]
  infos = list(fstate_all_account.loc[0, columns_infos])
  # 계정과목 금액 찾기
  amounts = find_amounts(fstate_all_account, account, accounts)[6:]
  # infos list와 amounts list 합친 후 account_df에 추가하기 
  data = tuple(infos + amounts)
  data_df = pd.DataFrame([data], columns=columns)
  account_df = account_df.append(data_df, ignore_index=True)
    
  return account_df

계정과목 금액 테이블에서 결측 데이터 찾는 함수

# account table에서 결측 데이터 찾기 
def find_zero_null(df, columns_quarters):
  zero_row_columns = []
  null_row_columns = []
  for quarter in columns_quarters:
      i = 0
      for amount in df[quarter]:
          if amount == None:
              null_row_columns.append([i, quarter])
          elif abs(amount) == 0:
              zero_row_columns.append([i, quarter])
          i += 1
  return zero_row_columns, null_row_columns

결과

profit_df 테이블
```
read_xlsx("profit_df_test")
```

	stock_code	stock_name	corp_code	thstrm_amount_2021_1	thstrm_amount_2021_2	thstrm_amount_2020_1	thstrm_amount_2020_2	thstrm_amount_2020_3	thstrm_amount_2020_4	thstrm_amount_2019_1	...	thstrm_amount_2019_3	thstrm_amount_2019_4	thstrm_amount_2018_1	thstrm_amount_2018_2	thstrm_amount_2018_3	thstrm_amount_2018_4	thstrm_amount_2017_1	thstrm_amount_2017_2	thstrm_amount_2017_3	thstrm_amount_2017_4
0	005930.KS	삼성전자	126380	7.092786e+12	9.450676e+12	4.889599e+12	5.488964e+12	9.266815e+12	1.682403e+13	5.107490e+12	...	6.105039e+12	1.540002e+13	1.161183e+13	1.098155e+13	1.296743e+13	3.092345e+13	7.488532e+12	1.079994e+13	1.103977e+13	3.030480e+13
1	000660.KS	SK하이닉스	164779	9.904460e+11	1.984515e+12	6.481540e+11	1.262890e+12	1.077262e+12	3.677840e+12	1.102753e+12	...	4.932010e+11	1.520087e+12	3.120254e+12	4.329947e+12	4.693620e+12	1.084649e+13	1.897969e+12	2.468544e+12	3.054248e+12	7.587264e+12
2	005380.KS	현대자동차	164742	1.327250e+12	1.761887e+12	4.633140e+11	2.273860e+11	-3.360610e+11	1.760497e+12	8.294770e+11	...	4.269110e+11	2.553138e+12	6.680140e+11	7.005990e+11	2.692450e+11	1.238839e+12	0.000000e+00	0.000000e+00	8.523710e+11	3.180453e+12
3	012330.KS	현대모비스	164788	5.997990e+11	6.665550e+11	3.484440e+11	2.342050e+11	3.897160e+11	1.139430e+12	4.829500e+11	...	5.771670e+11	1.713503e+12	4.665010e+11	5.530600e+11	4.488280e+11	1.439976e+12	7.611780e+11	4.812400e+11	4.821620e+11	1.085990e+12
4	036570.KS	엔씨소프트	261443	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
5	240810.KQ	원익IPS	1135941	2.564883e+10	7.599476e+10	1.376603e+10	3.542319e+10	8.727212e+10	1.054670e+10	4.977547e+09	...	1.501040e+09	4.136163e+10	2.459296e+10	3.156078e+10	3.701010e+10	4.985029e+10	2.488083e+10	3.673446e+10	2.166335e+10	7.370123e+10
6	145020.KQ	휴젤	888347	1.862436e+10	1.469514e+10	6.001495e+09	1.437236e+10	9.823475e+09	3.216862e+10	1.379860e+10	...	1.000559e+10	3.460651e+10	1.943144e+10	1.461857e+10	2.277522e+10	4.696118e+10	0.000000e+00	0.000000e+00	1.720762e+10	5.561952e+10

7 rows × 21 columns

equity_df 테이블

read_xlsx("equity_df_test")

	stock_code	stock_name	corp_code	thstrm_amount_2021_1	thstrm_amount_2021_2	thstrm_amount_2020_1	thstrm_amount_2020_2	thstrm_amount_2020_3	thstrm_amount_2020_4	thstrm_amount_2019_1	...	thstrm_amount_2019_3	thstrm_amount_2019_4	thstrm_amount_2018_1	thstrm_amount_2018_2	thstrm_amount_2018_3	thstrm_amount_2018_4	thstrm_amount_2017_1	thstrm_amount_2017_2	thstrm_amount_2017_3	thstrm_amount_2017_4
0	005930.KS	삼성전자	126380	265767257899008	274160529965056	258481768628224	261745373347840	267942138740736	267670331064320	245499810545664	...	255403451482112	254915469377536	215884501090304	225671422935040	234476374327296	240068992172032	183119604875264	193654455009280	203504408854528	207213415104512
1	000660.KS	SK하이닉스	164779	52354921529344	54850368831488	48231010533376	49382862880768	50479073591296	51888540090368	47159835623424	...	48253965959168	47928416665600	36371829882880	40771700916224	43484018900992	46845720002560	25162328047616	27757134217216	30896684007424	33815279960064
2	005380.KS	현대자동차	164742	70601578381312	72723250282496	69438925701120	69654412263424	69053146202112	69480633860096	68185340510208	...	69840668721152	70065802182656	68905540255744	69261296926720	68918907502592	67973968560128	66602066247680	68356673634304	69140429668352	69103482044416
3	012330.KS	현대모비스	164788	33496269586432	34134032384000	32547620782080	32781597933568	33127330217984	33252668604416	30983988445184	...	32363413241856	32330018193408	29412735057920	30095848767488	30340605280256	30630494601216	28456159019008	29261131939840	29868234375168	29295405694976
4	036570.KS	엔씨소프트	261443	3038004903936	3134575869952	2593551286272	2775932993536	3210856628224	3141584289792	2376113848320	...	2462692933632	2499168174080	2532326506496	2664293466112	2482215845888	2367717113856	1814119186432	2188696354816	2447746269184	2721242677248
5	240810.KQ	원익IPS	1135941	678936313856	755790053376	579267723264	614530088960	702146543616	663027843072	526018707456	...	552337408000	566554132480	289167081472	338846351360	375512694784	369216487424	243022430208	279915921408	301857701888	313468387328
6	145020.KQ	휴젤	888347	760155209728	776661172224	708633755648	726264578048	737391935488	746673537024	735421661184	...	709576491008	699495546880	721645338624	735678627840	744941879296	727719936000	272907010048	296767848448	689262559232	695710384128

7 rows × 21 columns

문제점

데이터 누락

accounts(dict)에 존재하지 않는 account_id를 사용함
찾고자 하는 계정과목에 대응되는 account_id가 재무제표 자체에서 누락됨

예시

nc_당기순이익

엔씨소프트 재무제표에서 지배기업의 소유주에게 귀속되는 당기순이익의
account_id가 재무제표 자체에서 누락되어 있고
account_nm 또한 IFRS 기반 XBRL 재무제표 공시용 표준계정과목체계의 이름과 다르다.

Twitter Facebook LinkedIn

정연서(yeonseo-Jung)