구글스프레드를 활용한 암호화폐 포트폴리오 설계 #2

안녕하세요, Aiyren입니다.

암호화폐 포트폴리오 설계 시리즈를 시작한다고 1편을 끄적거린지 얼마되지 않은 것 같은데 벌써 1주일이 흘렀네요. 2편 잘 써보려 구상은 열심히 했는데 게으름 때문인지 손에 잘 안잡혔네요.

주말동안 스팀 가격이 많이 올라 룰루랄라 게임기(닌텐도 스위치)나 붙잡고 뒹굴거리다, 비트코인이 다시 주저앉는 걸 보고 부랴부랴 컴퓨터 앞에 앉아봅니다.

오늘은 예고했던 대로 추출한 데이터를 가공하고 보기쉽게 기초 통계를 만들어볼까 합니다.

자.. 구글 스프레드를 열어볼까요?

#2. 데이터 전처리

1) 분석대상 정의

저번 시간에 예제로 비트코인의 1년간 Historical Data 추출까지 작업했었죠?
다른 코인도 똑같은 작업을 반복해야 하는데, 노가다 작업을 조금이라도 줄이기 위해 머리를 좀 써야겠네요.

분석대상은 샘플로 다음과 같이 정의합니다.

시가총액 1위~25위까지의 코인

먼저 분석하고자 하는 대상 코인을 관리하기 위해 "List" 시트를 만들어보도록 하겠습니다.

새 시트를 만들고 이름을 List로 변경한 다음, https://coinmarketcap.com/ 의 첫 페이지를 저번 시간에 배운 방법으로 불러와 봅니다.

A1 셀에 주소를 입력하고 A3 셀에 "=importhtml(A1, "table", 1)"을 입력하면, 다음과 같이 시가총액 1~100위의 코인리스트가 생성됩니다.

동일한 방식으로 주소만 https://coinmarketcap.com/2 로 바꾸면 101~200위 리스트가 생성되고, 이후 목록도 뒤에 숫자만 3,4,5.. 로 바꾸면 불러올 수 있어요.

https://coinmarketcap.com/all/views/all/ 에서 한꺼번에 전체 리스트(1,469개)를 제공하긴 하는데, 속도 문제로 일단 1~100위 기준으로 작업하겠습니다.

불러온 List 자료를 보기 좋게 정리해봅시다.

1. 데이터 영역은 그대로 두기 위해 회색 처리
2. 분석시작일자, 종료일자 입력칸 만들기
3. 24시간 변동값에 조건부서식으로 색 표시

한 눈에도 보기 좋아졌죠?
현재 (18.1.21@20시) 상위 코인들 중에선 QTUM이 (왜인지는 모르지만) 눈에 띄게 오르고 있네요.

이제 해당코인의 Historical Data를 불러오기 위해 URL 주소를 설정해야 하는데, 사이트에서 제공해주는 주소를 자동으로 알 수 없어 그냥 노가다로 붙여넣어야 겠네요.. ㅠㅠ

랭킹이 실시간으로 바뀔 수 있어 URL 시트를 별도로 만들어 다음과 같이 입력합니다.

그 다음 vlookup 함수로 자동으로 불러오게 만들면, 그럴듯한 첫 페이지가 완성됩니다.

2) 데이터 추출 자동화

방금 만든 List를 활용해 상위 코인들의 데이터 추출을 자동화 해봅시다.

1편에서 작업한 데이터 추출 시트를 List 시트의 URL과 시작일자, 종료일자로 다음과 같이 고쳐볼까요?

이제 노란셀의 숫자만 바꾸면 해당 랭킹의 데이터를 가져오게 바뀌었네요.
이걸 옆으로 랭킹이 증가하게 25개를 복사하면 아래와 같이 TOP 1~25위의 Historical Data가 빼곡하게 입력 되겠지요?

3) 기초 통계 생성

생성한 데이터를 그냥 보면 뭐가 뭔지 알 수 없으므로, 간단한 통계를 분석해보는 예제를 만들어 봅시다.
(데이터 가공 수식은 복잡해서 간단하게 설명하고 넘어갈까 합니다. 관심있으신 분은 포스팅 끝에서 공유해드리는 예제 파일 참고하시기 바랍니다.)

이제 불러온 Historical Data로 다음과 같은 기초 통계를 만들어 봅시다.

일별 수익률 (최근 30일)
수익률 평균/표준편차 (최근 30일)

일별 수익률은 다음의 일별 종가(Close) 기준으로 계산합니다.

1월 20일 비트코인 수익률 = 당일 종가(12,899.2) / 전일 종가(11,607.4) - 1 = 1.2%

List 시트에서 offset 함수를 통해서 URL옆 J열부터 오른쪽 방향으로 다음과 같이 일별 수익률을 계산합니다.
(아래로 한칸씩 밀면서 위의 일별 수익률 공식을 적용한다고 보시면 됩니다)

코인별 30일간의 일별 수익률을 만들었으면, 왼쪽에 2칸을 더 만들어 평균/표준편차를 계산합니다.

보기 좋은 떡이 먹기도 좋다고, 계산한 수익률에 조건부 서식을 아래와 같이 만들어 꾸며 봅시다.

그럼.. 요렇게 깔끔하게 완성!
4일전 폭락장이 한눈에 들어오네요.. ㅎㅎ

아이고.. 오늘은 여기까지 하겠습니다.
엑셀파일 만들면서 포스팅하니 한나절 걸리는군요.

작업 파일 공유해드리니, 관심있으신 분은 고쳐쓰셔도 좋습니다 ^^
(https://docs.google.com/spreadsheets/d/1xlkVN6fdyfrMH-V5fJkMP_ZNIC52o4fTlBpsm-ciMOQ/edit?usp=sharing)

다음 시간부터는 제가 ETF 분석할 때 자주 보는 통계(변동성/모멘텀/상관관계)를 산출해 암호화폐에 적용해서 포트폴리오 설계하는 작업을 진행해 보도록 하겠습니다.

그럼.. 이번 주도 즐겁고 행복한 투자 하시길 바랍니다.