[Practical Data Analysis] 2 - Calculating CGT on U.S. stocks

지난번에 환율정보를 기반으로 실제 거래 데이터를 이용하여 계속진행하겠습니다.

다시 말하지만 파운드화로 보고서를 만드는 과정이고, 세금을 계산하는 것은 아니고 세금의 기준이되는 수익까지만 계산합니다.

 

여기서 잠깐) 내가 사용하는 주피터 노트북의 현제 폴더위치를 알아보자.

 

 

그럼 본론으로 돌아가서 , 주식 거래내역을 엑셀 파일로 다운받았고,

데이터프레임으로 엑셀파일 로딩하는 방법

 

import pandas as pd

# 파일 경로
file_path1 = './source/2024 details.xls'  # 또는 example.xls
file_path2 = './source/2025 details.xls'  # 또는 example.xls

# Excel 파일 읽기
df1 = pd.read_excel(file_path1)  # 엔진은 자동으로 선택됨
df2 = pd.read_excel(file_path2)  # 엔진은 자동으로 선택됨

 

혹시 엑셀관련 패키지가 없다면 , 아래 패키지를 추가로 설치해야 한다.

! 포함하면 주피터에서 실행 가능, 터미널에서 실행하려면 ! 제외하고 명령어를 실행시키면 된다.

 

!conda install -c conda-forge xlrd
!conda install -c conda-forge openpyxl

 

1개의 거래당 2줄에 걸쳐서 데이터가 존재한다. 그래서 2줄을 1줄로 변경하는 작업이 필요하다.

 

 

처리 방법은 아래 방법으로 처리하면 된다.

 

# 짝수행을 홀수행 옆으로 붙이기, 인덱스를 리셋해야 보기 좋음.
df_odd = df1.iloc[1::2].reset_index(drop=True)
df_even = df1.iloc[::2].reset_index(drop=True)  # 참고: 짝수 행

# 이전에는 아래방향으로 데이터를 접합했지만, 이번엔 옆으로 접합하기 위해서
# axis=1 옵션을 추가함.
df_new1 = pd.concat([df_even, df_odd], axis=1)

# 짝수행을 홀수행 옆으로 붙이기
df_odd = df2.iloc[1::2].reset_index(drop=True)
df_even = df2.iloc[::2].reset_index(drop=True)  # 참고: 짝수 행

df_new2 = pd.concat([df_even, df_odd], axis=1)

 

 

첫 행에 있는 내용을 컬럼명으로 변경하는 방법

 

# 첫 행을 컬럼명으로 설정
df_new1.columns = df_new1.iloc[0]             # 첫 행 데이터를 컬럼명으로 지정
df_new1 = df_new1[1:].reset_index(drop=True)  # 첫 행 제거 후 인덱스 재설정

# 첫 행을 컬럼명으로 설정
df_new2.columns = df_new2.iloc[0]             # 첫 행 데이터를 컬럼명으로 지정
df_new2 = df_new2[1:].reset_index(drop=True)  # 첫 행 제거 후 인덱스 재설정

 

 

2개의 데이터프레임을 하나로 합치기, 이전까지 2번씩 해야했던 불필요한 반복을 줄이기 위함.

 

# 2개의 df 를 하나로 합치기, 인덱스 제거함
df_new = pd.concat([df_new1, df_new2], ignore_index=True)

 

불필요한 컴럼 NaN 등을 제거한다.

 

# 필요없는 컬럼/열 삭제
cols_to_delete = [10, 11, 13, 17, 18]
df_copy = df_new.drop(df_new.columns[cols_to_delete], axis=1)

 

 

날짜 형식을 환율정보와 동일한 형식으로 yyyymmdd 타입으로 변경

 

# 날짜 형식에 대한 형식을 변환 yyyymmdd
df_copy["매도일"] = pd.to_datetime(df_copy["매도일"], format='%Y.%m.%d').dt.strftime('%Y%m%d')
df_copy["매수일"] = pd.to_datetime(df_copy["매수일"], format='%Y.%m.%d').dt.strftime('%Y%m%d')

 

 

 

전체의 데이터가 object 타입이다.

 

데이터가 없으면 중간과정에서 문제가 생기므로 NaN(결측치) 있는지 확인이 필요하다.

경우에 따라서는 초기값이나 별도의 치환이 필요하다.

 

# 데이터가 없는 컬럼 찾기
df_copy.isna().sum() 
--------------------------------
0
계좌번호      0
국가명       0
종목명       0
매도수량      0
매도일       0
매도단가      0
매도금액      0
필요경비      0
양도소득금액    0
매도일환율     0
종목코드      0
매수일       0
매수단가      0
매수금액      0
매수일환율     0
dtype: int64

 

필요한 데이터/컬럼만 추출하여, 원하는 순서로 다시 배치하여 별도의 데이터프레임 만들기

 

cols_to_keep = ['종목명', '종목코드', '매도수량', '매도일', '매수일', '매도단가', '매도금액', 
                '매수단가', '매수금액', '필요경비', '양도소득금액', '매도일환율', '매수일환율']
trade = df_copy[cols_to_keep].copy()

 

 

환율이 월단위로 제공된 자료이므로 trade 데이터프레임에 조인시 기준이 될 새로운 컬럼 추가, 형식은 yyyymm 로 변환.

 

# 여기서 int 처리한 이유가 환율정보가 해당 컬럼이 int 타입이기 때문이다.
# csv 파일을 로딩하면 숫자는 자동으로 int 타입으로 변환됨.

trade['sell_month'] = trade['매도일'].str[:6].astype(int)
trade['buy_month'] = trade['매수일'].str[:6].astype(int)

 

전체 데이터중에 매도 기간이 "20250405" 까지 이므로 이외 기간 제외함. 보고 대상 기간이 20240406~20250405 까지임.

단 타입이 object 타입이므로 스트링으로 비교 처리함.

 

# 매도 기간이 25년 4월 5일까지만 보고 대상임, 26년 1월 말일까지 보고 가능.
trade = trade[trade['매도일'] <= '20250405']

 

 

컬럼명을 영어로 변경

 

trade.columns = ['name', 'code', 'cnt', 'sell_date', 'buy_date', 'sell_usd', 'sell_krw', 
                 'buy_usd', 'buy_krw', 'fee_krw', 'CG_amt_krw', 'sell_usd_krw', 'buy_usd_krw', 
                 'sell_month', 'buy_month' ]

 

 

 

 

환율정보 로딩.

 

# 파일 경로
file_path3 = 'exchanged_rate.csv'  # 또는 example.xls

# Excel 파일 읽기
ex_rate = pd.read_csv(file_path3)

 

 

하나의 데이터를 통화 종류에 따라 2개의 데이터로 분리함.

 

# 달라, 한화 데이터 프레임으로 분리함.
ex_usd = ex_rate[ex_rate['cur_code'] == 'USD'].sort_values(by='start').reset_index(drop=True)
ex_krw = ex_rate[ex_rate['cur_code'] == 'KRW'].sort_values(by='start').reset_index(drop=True)

 

 

매도 가격을 달러에서 파운드로 변환한 컬럼 추가

환율중 파운드/달러 환율 컬럼 추가(파운드에 이 값을 곱하면 달러가 되는 환율, 그래서 파운드를 계산할때 달러를 이 값으로 나눔)

 

# merge - 디비의 join 과 같은 명령어
# add sell_pound(매도가격), sell_pound_usd(매도일의 파운드/달러 환율)
df_merged = trade.merge(ex_usd[['pound','start_month']], left_on='sell_month', right_on='start_month', how='inner')
df_merged['sell_pound'] = (df_merged['sell_usd'] / df_merged['pound']).astype(float)
df_merged['sell_pound_usd'] = df_merged['pound']

# 필요없는 필드 제거
cols_to_delete = ['pound', 'start_month']
df_merged = df_merged.drop(columns=cols_to_delete, axis=1)

 

여기서 잠깐) 'sell_usd' 타입은 object 이고, pound 타입은 float 타입입니다. 이런 경우, Pandas는 연산 시 내부적으로 **pd.to_numeric()**을 시도하는 동작을 하기 때문에 가능함. 

다만, sell_usd 데이터가 숫자만 포함하고 있으면 문제가 없지만 문자를 포함하고 있으면 오류가 발생한다.

 

또는 사전에 아래 처럼 변환을 해도 좋음

df_merged['sell_usd'] = pd.to_numeric(df_merged['sell_usd'], errors='coerce')
errors='coerce' → 숫자로 변환 안 되는 값은 NaN(결측치) 처리됨.

 

 

 

주의) 환율정보를 매수가에도 적용해야하는데, 처음 작업할때 환율정보를 24년부터 가져와서 오류가 발생함, 매도가 merge 때는 문제가 없었지만, 매수는 24년 이전에 발생한 주식이 있어서 매수가 merge 때는 문제가 발생함.

===> 환율 작업을 다시 시작했음.

 

 

매수 가격을 달러에서 파운드로 변환한 컬럼 추가

 

# 예: df1에서는 code, df2에서는 cur_code
# add buy_pound(매수가격), buy_pound_usd(매수일의 파운드/달러 환율)
df_merged = df_merged.merge(ex_usd[['pound','start_month']], left_on='buy_month', right_on='start_month', how='inner')
df_merged['buy_pound'] = (df_merged['buy_usd'] / df_merged['pound']).astype(float)
df_merged['buy_pound_usd'] = df_merged['pound']

# 필요없는 필드 제거
# df_merged.drop(columns=['col1', 'col2'], inplace=True)
cols_to_delete = ['pound', 'start_month']
df_merged = df_merged.drop(columns=cols_to_delete, axis=1)

 

파운드 매도/매도환율/매수/매수환율 추가됨

 

 

거래 수수료를 파운드로 환산해야하는데, 거래 수수료는 한화 정보만 있어서 krw 환율정보를 가져오고 파운드 환율정보는 주식 매도일 기준으로 처리함(정확한 수수료 산정 방식을 몰라서 이렇게 처리함)

 

# 예: df1에서는 code, df2에서는 cur_code
# add fee_pound(수수료 krw->pound 환산), sell_pound_krw(매도일의 파운드/원화 환율), 
# fee_krw(필요경비는 한화정보만 있음)를 매도일 기준으로 환전하여 fee_pound 만듦.
df_merged = df_merged.merge(ex_krw[['pound','start_month']], left_on='sell_month', right_on='start_month', how='inner')
df_merged['fee_pound'] = (df_merged['fee_krw'] / df_merged['pound']).astype(float)
df_merged['sell_pound_krw'] = df_merged['pound']

# 필요없는 필드 제거
# df_merged.drop(columns=['col1', 'col2'], inplace=True)
cols_to_delete = ['pound', 'start_month']
df_merged = df_merged.drop(columns=cols_to_delete, axis=1)

 

CG_amt_pound 은 수수료 까지 계산한 금액임

sell-buy_pound 은 수수료 제외한 금액임, 그래서 CG_amt_pound 보다 큼.

 

# Capital Gain amt

df_merged['CG_amt_pound'] = (
                             (df_merged['sell_pound'] - df_merged['buy_pound']) 
                             * df_merged['cnt'] 
                             - df_merged['fee_pound']
                            ).astype(float)
df_merged['sell-buy_pound'] = (
                               (df_merged['sell_pound'] - df_merged['buy_pound']) 
                               * df_merged['cnt']
                              ).astype(float)

 

 

매도일 기준으로 정렬해서 필요한 컬럼만 확인

 

df_sorted = df_merged.sort_values(by='sell_date').reset_index(drop=True)
df_sorted[['name', 'cnt', 'sell_date', 'buy_date', 'sell_usd', 'buy_usd',
           'fee_krw', 'CG_amt_krw', 'sell_pound', 'buy_pound', 'fee_pound', 
           'CG_amt_pound', 'sell-buy_pound']]

 

거래 내역은 73건으로 문제 없이 잘 처리된것을 확인할수 있습니다.

 

 

그럼 핵심으로 돌아와서. 전체 Capital Gain 은 어느 정도이고 수수료 환율에 따라 오차는 어느정도 발생 가능한지 알아 보겠습니다.

 

total_amount = df_merged['CG_amt_pound'].sum()
print("Total CG_amt_pound: ", f"{total_amount:,.2f} Pounds")
print('----------------------------------------------------')

sell_buy_pound = df_merged['sell-buy_pound'].sum()
total_fee = df_merged['fee_krw'].sum()

max_val = ex_krw['pound'].max()
min_val = ex_krw['pound'].min()

print("total sell-buy_pound(without fee): ", f"{sell_buy_pound:,.2f} Pounds")
print("fee_krw:", f"{total_fee:,} KRW")
print()

print("Max POUND/KRW:", f"{max_val:,.2f}")
print("Min POUND/KRW:", f"{min_val:,.2f}")

max_fee_pound = total_fee/max_val
min_fee_pound = total_fee/min_val
print("Fee Max(ex_rate) Pounds:", f"{max_fee_pound:,.2f} Pounds")
print("Fee Min(ex_rate) Pounds:", f"{min_fee_pound:,.2f} Pounds")

print(f"Min-Max CG_amt is {sell_buy_pound-min_fee_pound:,.2f} ~ {sell_buy_pound-max_fee_pound:,.2f} Pounds")

 

계산 결과 및 숫자의 의미.

 

Total CG_amt_pound:  10,816.78 Pounds ==> 매도일 기준으로 수수료 적용한 결과
----------------------------------------------------
total sell-buy_pound(without fee):  11,529.43 Pounds ==> 수수료를 차감하지 않는 결과
fee_krw: 1,280,022 KRW

Max POUND/KRW: 1,909.48 ==> 기간중 환율이 높을때, 한화가 제일 약세인 환율
Min POUND/KRW: 1,533.51 ==> 기간중 환율이 낮을때, 한화가 제일 강세인 환율
Fee Max(ex_rate) Pounds: 670.35 Pounds ==> 한화가 약세라서 파운드 금액이 적음
Fee Min(ex_rate) Pounds: 834.70 Pounds ==> 한화가 강세라서 파운드 금액이 많음

Min-Max CG_amt is 10,694.73 ~ 10,859.08 Pounds
==> 매도일 기준으로 수수료를 계산한 총합이 구간의 뒷부분에 위치한 이유는 한화가 약세라서 수수료가 적어져서 임.
==> 만약에 수수료를 매수일 기준으로 총합을 구했다면 한화가 강세라서 파운드로 변환한 총합이 더 커져서 
    구간의 앞부분으로 이동했을 가능성이 있음.

 

 

다시 계산해서 수수료를 매수일 기준으로 다시 만들어 봤습니다.

 

df_CG_buy = df_merged.merge(ex_krw[['pound','start_month']], left_on='buy_month', right_on='start_month', how='inner')
df_CG_buy['fee_buy_pound'] = (df_CG_buy['fee_krw'] / df_CG_buy['pound']).astype(float)
df_CG_buy['buy_pound_krw'] = df_CG_buy['pound']

# 필요없는 필드 제거
cols_to_delete = ['pound', 'start_month']
df_CG_buy = df_CG_buy.drop(columns=cols_to_delete, axis=1)


# Capital Gain amt
df_CG_buy['CG_amt_buy_pound'] = (
                                 (df_CG_buy['sell_pound'] - df_CG_buy['buy_pound']) 
                                 * df_CG_buy['cnt'] 
                                 - df_CG_buy['fee_buy_pound']
                                ).astype(float)
                                
total_amount = df_CG_buy['CG_amt_pound'].sum()
print("Total CG_amt_pound(fee with sell date): ", f"{total_amount:,.2f} Pounds")
print('----------------------------------------------------')

total_amount = df_CG_buy['CG_amt_buy_pound'].sum()
print("Total CG_amt_buy_pound(fee with buy date): ", f"{total_amount:,.2f} Pounds")

 

2개의 차이가 생각보단 크지 않네요.

 

Total CG_amt_pound(fee with sell date):  10,816.78 Pounds
----------------------------------------------------
Total CG_amt_buy_pound(fee with buy date):  10,804.65 Pounds