증권사 거래내역 합치기: 여러 증권사 거래내역을 하나로 모아 실제 수익을 볼 수 있나요? | 클로드 엑셀왕 8

이 장에서 다루는 것

엑셀왕 TIP 합치기 전에 원본 숫자 적어 두기

증권사마다 계좌에서 나가고 들어온 돈의 합계(산 돈은 빼고, 판 돈과 배당은 더한 값)와 행 수, 그리고 연말 잔고 화면의 종목별 보유수량을 메모해 둡니다. 가증권처럼 정산금액이 모두 양수로 적힌 파일은 매수 줄을 빼서 더합니다. 합친 뒤에 이 숫자와 맞는지 보면, 빠진 줄이나 부호를 잘못 바꾼 줄이 바로 드러납니다.

이 장의 예제에서는 가증권 -2,471,882원(12행), 나증권 -1,374,245원(8행), 다증권 -1,577.23달러(7행)입니다. 산 돈이 판 돈보다 많으면 음수가 됩니다.

클로드에서는 거래내역 세 개를 한 대화에

클로드 앱(웹·데스크톱·모바일)의 대화창에 거래내역 파일 세 개와 연말 잔고가 든 시트를 함께 첨부하고 시작합니다. 같은 종목이 두 증권사에 있으면 클로드가 한 대화 안에서 두 파일을 같이 봐야 합니다.

거래내역에는 계좌번호와 이름이 머리말에 찍혀 나오는 경우가 많습니다. 계좌번호·이름·주민등록번호가 든 줄이나 열은 지우고 첨부하세요.

이렇게 말하기

증권사 세 곳 거래내역을 합치기 전에, 열을 어떻게 맞출지와 손익을 계산하려면 정해야 할 기준을 먼저 보여 줘.

그러면 처리되는 일

· 세 파일의 머리글을 읽고 구분·수량·금액·수수료·세금·환율 열을 맞춘 대응표를 보여 줍니다.

· 한빛전자가 두 증권사에 모두 있다는 점, 달러 거래가 있다는 점, 나증권은 수수료와 세금을 한 열에 적는다는 점을 짚고, 평균단가·원화 환산·수수료 처리 기준을 묻습니다.

이렇게 답하기

평균단가는 증권사 계좌마다 따로, 살 때마다 다시 계산하는 이동평균으로 해 줘. 달러 거래는 거래내역의 적용환율로 거래마다 원화로 바꾸고, 수수료와 세금은 한 열로 합쳐 줘. 배당은 세전 금액으로 두고 원천세는 수수료·세금에 넣어 줘.

그러면 처리되는 일

· 통합 파일에 ‘기준과열대응’ 시트를 만들어 정한 기준을 적어 둡니다.

· 원가 계산은 증권사와 종목이 같은 줄끼리만 이어지게 만듭니다. 가증권 한빛전자와 나증권 한빛전자는 평균단가를 따로 계산합니다.

엑셀왕 노트 계좌별로 할까, 합쳐서 할까

가증권 한빛전자의 평균단가는 58,750원, 나증권은 59,400원입니다. 계좌별로 계산하면 한빛전자 실현손익은 982,000원이고, 두 계좌를 하나로 합쳐 평균단가를 내면 976,429원입니다. 5,571원 차이입니다.

각 증권사 앱이 보여 주는 평균단가는 그 계좌 안의 거래로만 계산한 값이라, 계좌별로 맞추면 앱 화면과 대조하기 쉽습니다. 세금 신고에 쓰는 계산 방법은 이 표와 따로, 증권사가 주는 신고용 자료와 세무 안내로 확인하세요.

이렇게 말하기

정한 기준대로 세 거래내역을 날짜순으로 통합내역 시트 하나에 합쳐서 엑셀 파일로 줘. 원본 파일 이름과 행 번호를 남기고, 매도원가와 실현손익은 수식으로 계산해 줘.

그러면 처리되는 일

· 세 파일의 27행을 같은 열 구조로 합칩니다. 수량은 모두 양수로 바꾸고 방향은 구분 열(매수·매도·배당)로 나타냅니다.

· 금액과 수수료·세금은 원래 통화 그대로 두고, 옆 열에서 환율을 곱해 원화로 바꿉니다. 원화 거래는 환율 1입니다.

· 줄마다 같은 증권사·같은 종목의 앞선 줄을 더해 직전보유수량과 직전잔여원가를 구하고, 매도 줄에서는 판 수량만큼의 원가(매도원가)와 실현손익을 계산합니다.

수식 한 줄 읽기 화면 8-3의 R21 셀

=IF(E21="매도", ROUND(H21*Q21/P21, 0), 0)

· E21=”매도” → 매도 줄일 때만 계산하고, 나머지 줄은 0입니다.

· Q21/P21 → 직전잔여원가 4,700,000원을 직전보유수량 80주로 나눈 값, 곧 가증권 한빛전자의 평균단가 58,750원입니다.

· H21*… → 판 수량 40주를 곱하고 원 단위로 반올림하면 매도원가 2,350,000원입니다.

한 줄로 읽으면 “판 수량만큼 이 계좌의 평균단가를 곱한다”입니다. Q열과 P열은 SUMIFS로 위쪽 줄 가운데 증권사와 종목이 같은 줄만 더해서, 나증권 한빛전자의 거래는 섞이지 않습니다.

이렇게 말하기

증권사마다 통합내역의 정산금액 합계와 행 수가 원본과 같은지, 종목별 보유수량이 연말 잔고와 같은지 보는 검산 시트를 만들어 줘.

그러면 처리되는 일

· 증권사별 원본 정산금액 합계와 통합내역 합계, 차이를 나란히 놓습니다. 다증권은 달러끼리 비교합니다.

· 증권사·종목마다 통합내역의 수량 변동을 더한 값과 원본 잔고를 비교합니다.

· 가진 수량보다 많이 판 줄이 없는지도 셉니다. 모두 맞으면 ‘일치’가 나오게 합니다.

이렇게 말하기

종목별로 매도금액, 매도원가, 실현손익, 배당, 수수료·세금, 순손익, 연말 보유수량을 요약하고, 증권사별 합계도 아래에 붙여 줘.

그러면 처리되는 일

· 종목별손익 시트에 종목마다 SUMIFS로 통합내역을 더한 표를 만들고, 순손익은 실현손익 + 배당 − 수수료·세금으로 계산합니다.

· 연말에 남은 주식은 보유수량과 원가만 보여 주고, 평가손익은 넣지 않습니다.

클로드에서는 기준을 프로젝트에 두기

내년에도 같은 계좌들을 정리한다면 클로드 프로젝트를 하나 만들고, 완성 파일의 기준과열대응 시트를 프로젝트 지식에 올려 두세요. 프로젝트 지침에는 “증권사 거래내역은 기준과열대응 시트대로 합친다. 평균단가는 계좌별 이동평균, 달러는 거래내역 환율. 원본 정산금액과 잔고로 검산한다. 처음 보는 열이 나오면 합치기 전에 묻는다”라고 적습니다.

그러면 다음에는 그 프로젝트에서 새 거래내역을 첨부하고 “올해 것도 같은 방식으로”라고만 해도 됩니다.

엑셀왕 노트 사고팔 판단은 이 표 밖에서

종목별손익 시트는 이미 끝난 거래를 정리하는 장부입니다. 클로드에게 종목 전망이나 사고팔 때를 물어 그대로 따르지 마세요. 양도소득세·금융소득 신고처럼 세금이 걸린 계산은 증권사가 주는 신고용 자료와 세무 안내를 따르고, 종목별손익 시트는 내 거래를 확인하는 데 씁니다.

한 번에 말하는 요청

증권사 세 곳 거래내역을 합치기 전에 열 대응표와 손익 계산에 필요한 기준을 먼저 보여 줘.
평균단가는 계좌별 이동평균, 달러는 거래내역의 적용환율로 거래마다 원화 환산, 수수료와 세금은 한 열로 합쳐서 날짜순 통합내역 시트를 만들어 줘.
증권사별 정산금액 합계와 연말 보유수량을 원본과 비교하는 검산 시트, 종목별 실현손익·배당·순손익 시트, 정한 기준을 적은 시트도 넣어서 엑셀 파일로 줘.

이 장의 핵심

합치기 전에 열 대응표와 손익 기준(평균단가, 환율, 수수료·세금)을 먼저 받는다.

기준은 사람이 정하고, 통합 파일 안에 시트로 적어 둔다. 기준이 바뀌면 손익 숫자도 바뀐다.

증권사별 정산금액 합계와 연말 보유수량을 원본과 맞춘 뒤에 종목별 손익을 본다. 원본 값은 사람이 직접 옮겨 적는다.

답 예시

환전수수료는 원화로 적힌 금액이니 환율을 곱하지 말고, 그 거래의 수수료·세금(원)에 그대로 더해 줘. 기준과열대응 시트의 수수료·세금 칸과 ‘넣지 않은 것’도 고쳐 줘.

이렇게 답하면 1월 블루핀테크 15주 매도의 원화 금액은 3,647,840원, 남아 있던 원가 2,963,287원을 빼면 실현손익은 684,553원입니다. 달러 수수료를 환산한 9,121원과 환전수수료 1,800원을 빼면 이 거래의 순손익은 673,632원입니다. 환전수수료에도 환율을 곱하면 1,800원이 2,648,160원으로 바뀌어 수수료가 터무니없이 커집니다. 처음 보는 열을 어느 통화로 볼지는 거래내역을 받은 사람이 정하고, 기준 시트에 남겨 둡니다.