주식 수익률 구하기
개발 | 2023.08.15 18:08
나는 나무증권을 사용하는데 주식 수수료는 다음과 같다(유관기관 제비용 포함)
국내주식 : 0.0043319%
ETF등 : 0.0049010%
그리고 2023년 현재 국내주식의 증권거래세율은 0.2%다.
(증권거래세는 연도별 다르기 때문에 기간별로 세율정보를 관리해야 한다)
수익률을 위한 평단가는 보통 선입선출법, 이동평균법으로 계산하는데 여기선 이동평균법만 다룬다.
실현손익 = (매도건수*매도가격) - (매수건수*평단가) - 매매수수료 - 매도수수료 - 세금
수익률 = 실현손익 / (매수건수*평단가) * 100
참고로 수수료는 10원단위 절사처리, 세금은 소숫점버림으로 계산한다.
DB에서는 trunc함수를 사용하면 된다.
trunc(47.93, -1)? // 수수료(10원 절사) 40
trunc(47.93, 0)? // 세금(소숫점 절사) 47
주식거래 테이블 구조가 아래와 같다고 해보자(MySQL 기준)
CREATE TABLE Trade (
idx int(10) unsigned NOT NULL AUTO_INCREMENT,
dt char(10) NOT NULL DEFAULT '' COMMENT '날짜',
cd char(6) NOT NULL DEFAULT '' COMMENT '종목코드',
gubun char(3) NOT NULL DEFAULT '' COMMENT '매수:BUY, 매도:SEL, 배당:DIV',
cnt mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '갯수',
price mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '가격',
PRIMARY KEY (idx),
KEY dt (dt),
KEY cd (cd)
);
매수,매도,배당이 발생할때마다 레코드가 생성된다.
이 자료로 수익률을 계산할 수 있을까?
결론부터 얘기하면 불가능하다(나에게 국한된 얘기다)
수익률 계산을 위해선 이동평균을 알아야 하는데 내가 가진 쿼리작성 능력으론 이동평균을 하나의 쿼리로 작성할 수 없었다(Oracle, MySQL에서 테스트)
그러면 어떻게 수익률을 관리할 수 있을까?
잔여수량과 평단가를 관리하는 칼럼을 추가해 매 레코드 생성시 잔여수량과 평단가를 재계산 하는 것이다.
이 방법은 방대한 데이터가 이미 구축되었을 경우 여러가지를 고려해야한다. 다행히 나의 서버는 이런점에서 유연하게 대처할 수 있었다.
alter table Trade add av int unsigned not null default 0 after result;
alter table Trade add rest_cnt mediumint unsigned not null default 0 after result;
rest_cnt, av 칼럼을 추가했다.
이제 Trade테이블 등록,수정,삭제시 잔여수량과 평단가 값을 재계산한다.
@Override
public StTradeDto getLastAvForUpdateDao(StTradeDto stTradeDto) {
// TODO Auto-generated method stub
HashMap<String, Object> map = this.sqlSessionTemplate.selectOne("mapper.getLastAvForUpdate", stTradeDto);
int rest_cnt = 0;
int av = 0;
int new_rest_cnt = 0;
int new_av = 0;
logger.info("map : \n" + map + " \n");
if ( map != null ) {
// Object 에서 integer 로 못바꾸므로 우선 String로 변환 후 Integer로 받는다.
rest_cnt = Integer.parseInt( String.valueOf(map.get("rest_cnt")) );
av = Integer.parseInt( String.valueOf(map.get("av")) );
}
if ( "BUY".equals( stTradeDto.getGubun() ) ) {
if ( rest_cnt == 0 ) {
new_rest_cnt = stTradeDto.getCnt();
new_av = stTradeDto.getPrice();
} else {
new_rest_cnt = rest_cnt + stTradeDto.getCnt();
new_av = ((rest_cnt * av) + (stTradeDto.getCnt() * stTradeDto.getPrice())) / new_rest_cnt;
}
} else if ( "SEL".equals( stTradeDto.getGubun() ) ) {
new_rest_cnt = rest_cnt - stTradeDto.getCnt();
new_av = av;
} else {
// 배당일 경우
new_rest_cnt = 0;
new_av = 0;
}
stTradeDto.setRest_cnt( new_rest_cnt );
stTradeDto.setAv( new_av );
return stTradeDto;
}
마지막으로 기존 구축된 레코드들의 잔여수량, 평단가를 계산한다.
나는 이런 프로시저로 처리했다.
DELIMITER $$
CREATE PROCEDURE sp_allAv()
BLOCK_1:
BEGIN
DECLARE done_1 boolean DEFAULT false;
DECLARE v_dt varchar(100);
DECLARE cur1 cursor for
select dt from Trade where gubun <> 'DIV' ORDER BY CD, DT, IDX, GUBUN;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_1 := true;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK ;
END;
START TRANSACTION;
OPEN cur1;
LOOP1:
LOOP
FETCH cur1 INTO v_dt;
IF done_1 THEN
CLOSE cur1;
LEAVE LOOP1;
END IF;
BLOCK_2:
BEGIN
DECLARE done_2 boolean DEFAULT false;
DECLARE v_idx varchar(100);
DECLARE v_rest_cnt varchar(100);
DECLARE v_av varchar(100);
DECLARE cur2 CURSOR FOR
select idx, sum( if(gubun='BUY', CNT, CAST(CNT AS SIGNED)*-1) ) OVER(PARTITION BY CD ORDER BY CD, DT, IDX, GUBUN) REST_CNT,
CASE WHEN GUBUN='BUY' AND CNT=sum( if(gubun='BUY', CNT, CAST(CNT AS SIGNED)*-1) ) OVER(PARTITION BY CD ORDER BY CD, DT, IDX, GUBUN) THEN PRICE
WHEN GUBUN='BUY' THEN
( (CNT*PRICE) + (sum( if(gubun='BUY', CNT, CAST(CNT AS SIGNED)*-1) ) OVER(PARTITION BY CD ORDER BY CD, DT, IDX, GUBUN)-CNT)*LAG(AV) OVER(PARTITION BY CD ORDER BY CD, DT, IDX, GUBUN) )
/ sum( if(gubun='BUY', CNT, CAST(CNT AS SIGNED)*-1) ) OVER(PARTITION BY CD ORDER BY CD, DT, IDX, GUBUN)
WHEN GUBUN='SEL' THEN LAG(AV) OVER(PARTITION BY CD ORDER BY CD, DT, IDX, GUBUN)
END AV
FROM Trade
WHERE DT <= v_dt
AND GUBUN <> 'DIV'
ORDER BY CD, DT, IDX, GUBUN;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_2 := true;
OPEN cur2;
LOOP2:
LOOP
FETCH cur2 INTO v_idx, v_rest_cnt, v_av;
IF done_2 THEN
CLOSE cur2;
LEAVE LOOP2;
END IF;
update Trade set av = v_av, rest_cnt = v_rest_cnt
where idx = v_idx;
END LOOP LOOP2;
COMMIT;
END BLOCK_2;
END LOOP LOOP1;
COMMIT;
END BLOCK_1;
$$
DELIMITER ;
정리해보자.
수익률을 계산하기 위해선 각 레코드마다 평단가와 잔여수량 정보를 구해야하는데 하나의 쿼리로 이를 처리하긴 불가능하다.
차선으로 택한 방법이 칼럼을 추가해 매 레코드마다 잔여수량과 평단가를 최신으로 관리하는 것이다.
"개발" 카테고리의 다른 글
| 주식 수익률 구하기 (0) | 2023.08.15 |
