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

한국에서 미국주식을 매매하면 국세청에 양도세를 신고해야 합니다. 이건 개인이 할 필요는 없고 증권사에서 제공하는 대리 신고 서비스를 이용하면 됩니다. 아주 쉽습니다. 세금만 잘 내면 됩니다. 

다른 나라에 신고하는 경우를 상상해서, 거래 데이터를 변환하는 방법에 대해서 알아보겠습니다.

 

CGT - Capital Gains Tax

 

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

 

환율정보는 인터넷에서 구했습니다. 월평균 자료가 다운로드 받기 편해서 받았습니다. 일일 환율정보 파일을 받아도 별차이는 없습니다.

 

exrates-monthly-0122.csv
exrates-monthly-0123.csv
exrates-monthly-0124.csv
exrates-monthly-0125.csv
exrates-monthly-0222.csv
exrates-monthly-0223.csv
exrates-monthly-0224.csv
exrates-monthly-0225.csv
exrates-monthly-0322.csv
.....

 

먼저 파일을 모두 로딩하는 코드 입니다. 개발 환경은 주피터 노트북으로 해서 중간에 데이터 변환 과정을 확인하면서 진행했습니다.

참고로 개발환경은 맥북/주피터 노트북입니다.

 

csv 파일을 로딩해서 리스트 자료형에 넣었습니다.

 

import pandas as pd
import glob
import os

# CSV 파일들이 있는 폴더 경로 (현재 폴더이면 "." 사용)
# 현재 폴더 위치 : "/Users/mike/StudioProjects/python/Silicon_Dev"
# 확인 방법은 주피터에서 !pwd 이렇게 입력하면 주피터가 실행된 위치를 알수 있음.
folder_path = "./source"

# 파일 패턴: exrates-monthly-MMYY.csv
file_pattern = os.path.join(folder_path, "exrates-monthly-*.csv")

# 패턴에 맞는 모든 파일 찾기
# sorted로 이름순 정렬, 사실 정렬은 데이터 프레임에서 가능하므로 큰 의미는 없음.
all_files = sorted(glob.glob(file_pattern))  

# 모든 CSV를 읽어서 하나의 DataFrame으로 합치기
# 합치기 위해서 리스트에 모든 파일 정보를 로딩합니다.
df_list = []
for file in all_files:
    df = pd.read_csv(file)
    df['source_file'] = os.path.basename(file)  # 나중에 어떤 파일에서 왔는지 확인용
    df_list.append(df)

 

47개월 자료만 다운받아서 해당 개수만큼 리스트 길이가 맞는지 확인합니다.

 

# 2022 01 ~ 2025 11, so total months are 47
print(type(df_list))
print(len(df_list))
-------------------
<class 'list'>
47

 

하나의 리스트를 화면에 출력해보면 아래와 같습니다.

 

 

영국통화기준 전세계의 대부분 통화에 대한 환율정보를 볼수 있습니다. 이런 구조로 47개월의 환율 정보가 해당 리스트에 저장되어 있습니다.

 

데이터 프레임으로 변환을 합니다.

 

# reset the index and create new one
combined_df = pd.concat(df_list, ignore_index=True)

 

데이터가 얼마나 되는지 확인합니다. 확인하는 방법이 여러가지 있지만 일단 데이터 타입과 전체 개수만 확인합니다.

근데, 몇개의 통화는 중간에 빠진듯합니다.

47개월 * 166개 통화 종류 = 7,802 개의 rows 여야하는데, 좀 적네요.

 

환율정보만 float64 타입이고 나머지는 object 타입이네요

 

combined_df.info()
------------------------------------------
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 7741 entries, 0 to 7740
Data columns (total 7 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   Country/Territories    7741 non-null   object 
 1   Currency               7741 non-null   object 
 2   Currency Code          7741 non-null   object 
 3   Currency Units per £1  7741 non-null   float64
 4   Start date             7741 non-null   object 
 5   End date               7741 non-null   object 
 6   source_file            7741 non-null   object 
dtypes: float64(1), object(6)
memory usage: 423.5+ KB

 

날짜 형식을 보기 편하게 yyyymmdd 형식으로 변경해서 새로운 컬럼을 추가.

 

# re-format the date as yyyymmdd
combined_df["start_date"] = pd.to_datetime(combined_df["Start date"], format='%d/%m/%Y').dt.strftime('%Y%m%d')
combined_df["end_date"] = pd.to_datetime(combined_df["End date"], format='%d/%m/%Y').dt.strftime('%Y%m%d')

 

데이터 출처 정보를 추가. 컬럼 위치 바꾸려고 마지막에 복제함.

 

df_copy = combined_df
df_copy['source'] = df_copy['source_file']

 

 

필요 없는 컬럼 제거 - 편집은 삭제를 해도 되고, 필요한 컬럼만 선택해서 새로운 df 에 복사하는 방법 등이 있다.

 

# drop cols4,5,6
cols_to_delete = [4,5,6]
df_copy = df_copy.drop(df_copy.columns[cols_to_delete], axis=1)
df_copy.head()

 

 

주의) 데이터 중복이 있으면 데이터간 조인할때 데이터가 중복되므로 조심해야 한다.

처음엔 몰랐는데, 데이터 중복이 있어서 확인하는 부분 추가했습니다.

 

# USD 쓰는 곳이 2개라서 확인함.
print(df_copy[df_copy['Currency Code'] == 'USD'])

 

 

컬럼 이름을 간결하게 정리했습니다.  이런건 미리해주면 좋아요. 귀찮아서 버티다가 이제 변경했어요.

 

df_copy.columns = ['country', 'currency', 'cur_code', 'pound', 'start', 'end', 'source']

 

 

이제 필요한 달러와 한화 정보만 추출합니다.

 

df_filtered = df_copy[(df_copy['country'] == 'USA') | (df_copy['cur_code'] == 'KRW')].reset_index(drop=True)
df_filtered.sort_values(by='start', ascending=True)
# 47months * 2 currency = 94 rows

 

47개월 * 2개의 통화 = 94 rows 확인

 

 

환율정보가 일일 환율이 아니고 월별 환율이라서 yyyymm 으로 비교 가능한 컬럼을 추가.

 

df_filtered['start_month'] = df_filtered['start'].str[:6]
df_filtered['end_month'] = df_filtered['end'].str[:6]

 

 

데이터 정보 조회
------------------------------------------
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 94 entries, 0 to 93
Data columns (total 9 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   country      94 non-null     object 
 1   currency     94 non-null     object 
 2   cur_code     94 non-null     object 
 3   pound        94 non-null     float64
 4   start        94 non-null     object 
 5   end          94 non-null     object 
 6   source       94 non-null     object 
 7   start_month  94 non-null     object 
 8   end_month    94 non-null     object 
dtypes: float64(1), object(8)
memory usage: 6.7+ KB

 

다른 주피터 노트북에서 환율정보를 사용하기 위해서 환율정보를 csv 파일로 저장.

 

df_filtered.to_csv("exchanged_rate.csv", index=False, encoding='utf-8-sig')
# 인코딩은 utf8, 인덱스는 필요없어서 제거함.