스프레드시트 주식매수매도내역 그룹화 수식 만들고 싶습니다. (고난도),,

오빠두엑셀에서 자산관리 대시보드 무료로 다운받아서 제 필요에 맞게 챗지피티와 함께 수정해가면서 사용하고 있는데,,

 

챗지피티와 함께 해결하지 못하는 부분이 있어서 질문 남깁니다.

https://docs.google.com/spreadsheets/d/12chyVGuJnnQVQTSFx1GDFnykb1ZWk5lVhRNjnM60Fgo/edit?usp=sharing

제가 활용하고 있는 대시보드 중 일부 시트만 추출한 링크인데, 자산관리 대시보드 자체 수식에서도 그런 것 같고 해당 시트에서 실평단가 및 실현손익을 구할 시에  '이전 날짜'의 '같은 종목코드'의 '총 매수금액'을 '총 매수 수량'으로 나누어서 실평단가를 계산하고 이걸 바탕으로 실현손익을 계산하도록 되어있는 것 같습니다.

그런데 이렇게 될 경우, A종목을 10주 매수했다가 10주 전부 매도하고 다시 15주 매수했을 경우의 실 평단가는 새로 매수한 15주만 계산되어야하는데 이미 매도한 이전의 10주의 평단가까지 포함되어 계산되는 문제가 있습니다.

그래서 이 문제를 해결하고 싶어서 챗지피티와 열심히 머리를 굴려봤는데,,, 종목별로 누적 매수수량을 구하는 열을 만들었고 종목별 +  누적매수수량이 0이 되는 부분을 기점으로 전부 매도하고 다시 같은 종목을 매수할 경우에 그룹번호를 매겨서 그룹번호별로 실평단가를 계산하도록 하고 싶었습니다..

그래서 챗지피티와 함께 최대한 만들어낸 수식이 아래와 같은데 완벽하게 구동되는 것 같지는 않습니다. 근데 문제는 어떤 부분에서 어떤 게 잘못되어서 완벽한 구동이 안되는 지 이유를 알 수 가 없어서 수정을 할 수가 없네요..

=ARRAYFORMULA(
LET(
종목코드, E2:E,
누적수량, J2:J,
이전종목, IF(ROW(E2:E)=2, "", E1:E),
이전누적, IF(ROW(E2:E)=2, 0, J1:J),
변경지점,
(ROW(E2:E)=2) +
((종목코드 = 이전종목) * (이전누적 = 0) * (누적수량 > 0)),
키, 종목코드 & "|" & SCAN(0, 변경지점, LAMBDA(a, b, IF(b, a+1, a))),
MATCH(키, UNIQUE(키), 0)
)
)

이렇게 되는 데 조건에 맞지 않는 부분들에서 그룹 번호를 새로 매기고 해서 정확히 실평단가 계산이 안되는 상황입니다.

1. 중복되는 그룹번호가 없도록
2. 종목별로 다 다른 그룹번호를 부여하고
3. 같은 종목일때에는 누적수량이 0이 되지 않으면 같은 그룹번호를 받고, 누적수량이 0이 되었다가 다시 매수하는 경우에 새로운 그룹번호를 부여

위 수식을 적용한 상태에서 나오는 문제점은 정확히 파악되지는 않았지만, 해당 종목의 누적수량이 0이 된 적이 없는데도 새로 그룹번호를 부여받은 경우가 있고 누적수량이 0이 되었다가 다시 매수했는데 이전 그룹번호를 그대로 받은 경우도 있습니다...

어떻게 수식상으로 해결할 수 있는 방법이 있을까요?

그룹번호를 제대로 부여하여야 그룹번호 별로 실현손익을 정확히 계산할 수 있을 것 같은데 쉽지가 않네요...