백테스팅 구축 (14) - 결과값 엑셀에 입력하기

지난 게시글에서는 결과 데이터를 분석하기 위해 필요한 몇 가지 데이터들을 특정 변수에 입력한 후 그 값을 데이터프레임화 시킴으로써 우리가 전략을 수정하는 데에 있어서 필요한 데이터들을 수집했다. 그렇다면 이제 이를 바탕으로 데이터를 분석하기 위해서는 어떻게 해야 하는지 등에 대해 살펴보도록 하겠다.

 

데이터 분석 방법

    개인적으로 가장 추천하고 싶은 방법 중 하나는 바로 엑셀을 다룰 줄만 안다면 엑셀을 이용하는 것이다. 엑셀에서는 단순한 값 비교를 통해 결과값을 보여주거나 하는 등의 동작을 간편하게 이용할 수 있도록 구축되어 있는 등 결과 데이터를 분석하는 데에도 상당히 용이한 기능들이 많이 탑재되어 있다. 
    물론 우리가 제작했던  self.df_tracking_data (결과값 데이터 변수)를 MySQL에 입력하는 to_sql() 메서드(함수)를 통해 저장한 후 Workbench를 이용해서 데이터를 분석할 수도 있긴 하지만 엑셀과 비교하면 데이터 값의 수정이 복잡하다는 차이점이 있다. 즉, 이동평균선 간의 대소 비교 등과 같이 기존에 제작하지 않았던 데이터들을 계산하기 위해서는 또 새롭게 코드를 구축해야 한다는 것이다. 하지만 엑셀에서는 비교적 간단하게 분석할 수 있고, 그 결과값을 차트 등과 같은 시각적 데이터로 표현하는 것도 빠른 편에 속한다.

 

엑셀로 어떻게 보내는데?

    데이터프레임은 정말 감사하게도, (우리가 MySQL에 데이터를 저장할 때 to_sql을 사용했던 것처럼) 엑셀로 데이터를 보낼 수 있는 기능을 가진 to_excel() 메서드(함수)가 탑재되어 있다. 우리는 이를 통해 엑셀로 데이터를 입력해준 후에 엑셀에서 결과 데이터를 확인해보도록 할 예정이다.
    그 전에 앞서 우리가 제작했던 백테스팅 코드의 로직을 다시 한 번 살펴보자. 이전의 게시글에서도 살펴보긴 했지만 거기서 살펴봤던 내용은 단순히  def __init__()  안에 존재하는  while: 문 만을 대상으로 한 것이고, 이번에는 전체적인 코드를 대상으로 로직을 간단하게 살펴보도록 하곘다. 왜냐하면 to_excel()을 어느 지점에 입력할 것인지를 결정하기 위해 필요하기 때문이다.
    일단 아래와 같이  import os 를 입력한 후에,  os.getcwd() 를  directory_path 변수에 입력한 후 그 값을 출력해보자. 이는 현재 이용하고 있는 파이썬 파일(py 파일)이 존재하는 파일 경로를 얻어오는 것인데, 이를 통해 우리가 데이터를 저장하고자 하는 엑셀 파일을 우리가 원하는 위치에 생성하고 데이터를 입력하도록 할 수 있다. 물론 바탕화면에서 데이터를 관리하고 싶다면, 바탕화면 경로를 사용하면 된다. 해당 파일 경로를 얻어와서 그 안에 엑셀 파일을 제작하도록 하면 같은 파일 안에서 코드 수정과 결과값 확인이 가능하다는 장점이 있다.

import os

directory_path = os.getcwd()
print(directory_path)

## 출력 결과 ##
C:\Users\~~\PycharmProjects\trading\post_algorithm

 

그 다음에는 우리가 데이터를 입력하고자 하는 엑셀 파일의 파일 이름을 입력해준 후에, 앞서 얻어왔던 directory_path와 함께 사용해서 우리가 사용하고자 하는 엑셀 파일의 경로와 파일 이름을 함께  os.path.join 을 통해 전달해주자. 이제 xlxs_dir 변수 안에는 우리가 사용하고자 하는 파일의 경로와 파일 명이 모두 입력되었다. 그렇다면 이제, to_excel()을 사용하면 되는데 어디에 사용할지 한 번 로직을 통해 살펴보도록 하자.

import os

directory_path = os.getcwd()
file_name = "algoritm_1_result.xlsx"
xlsx_dir = os.path.join(directory_path, file_name)

 

현재 코드의 로직

   위의 사진을 보면 ①번 구간과 ②번 구간이 입력되어 있다. ①의 경우에는 하루 하루를 돌면서 self.df_tracking_data 변수에 입력된 시점의 상황이며 ②의 경우에는 if문 하단에 위치해 있기 때문에 class algorithm_1()이 모두 실행된 이후, 즉 시작일자(start_date)부터 종료일자(end_date)까지의 거래 데이터가 모두 입력된 시점의 상황이다. 
   다시 말해 ①번 지점에서 to_excel()을 사용하면 하루 하루의 거래 데이터를 엑셀 파일에 입력할 것이고, ②번 지점에서 to_excel()을 사용하면 입력 일자의 전체 범위를 대상으로 백테스팅을 진행한 후에 최종적으로 얻은 거래 데이터를 엑셀 파일에 입력하게 된다. 따라서 실시간으로 게속해서 분석하고 싶다면 ①의 위치에, 밤새 돌려놓고 자고 일어나서 다음 날 전체적으로 주욱 확인하고 싶다면 ②의 위치에 입력하면 된다.
     os.path.join(directory_path, file_name) 는 직접 print()해보면 결과값을 확인할 수 있는데, 바로 directory_path와 file_name을 합한 결과물이 출력된다.

 

openpyxl 설치

to_excel을 사용하기 위해서는 openpyxl 모듈이 필요하기 때문에, 해당 모듈을 설치하도록 하자. 파이참을 이용하는 경우에는 설정 화면 내 interpreter 창에서 [+] 버튼을 누르고 검색해서 설치할 수 있다.

설치 중의 화면(installing package 'openpyxl')

 

---

728x90

---

 

 

엑셀 파일 생성하고 데이터 입력하기

앞서 설치한 openpyxl 라이브러리(모듈) 내에서는 파일을 생성하거나 삭제하거나 저장하는 등의 기능을 수행한다. 아래의 코드를 실행하면, 코드가 존재하는 폴더 내에 algorithm_1이라는 엑셀 파일이 생성된다.

• 워크북 생성 : a = openpyxl.Workbook()
• 워크북 저장 : a.save(directory and filename)

import openpyxl
import os

directory_path = os.getcwd()
file_name = "algoritm_1_result.xlsx"
xlsx_dir = os.path.join(directory_path, file_name)

workbook = openpyxl.Workbook()
workbook.save(xlsx_dir)

 

이제 파일이 생성되는 것을 확인했으니, ①번 지점에서  self.df_tracking_data 변수를 대상으로 to_excel()을 이용해서 데이터를 algorithm_1_result 파일 안에 입력해주도록 하자. 

class algorithm1():
    def __init__(self, start_date, end_date, all_range):
        self.all_range = all_range
        self.today = start_date

        self.account = {'date':[], 'code':[], 'buy_price':[], 'quantity':[], 'profit':[]}
        self.df_account = pd.DataFrame(self.account, columns=['date', 'code', 'buy_price', 'quantity', 'profit'])
        self.tracking_data = {'code':[], 'buy_date':[], 'buy_price':[], 'buy_value':[], 'sell_date':[], 'sell_price':[], 'sell_value':[], 'profit':[],
                              'ma5_buy':[], 'ma10_buy':[], 'ma20_buy':[], 'ma60_buy':[], 'ma120_buy':[],
                              'ma5_sell':[], 'ma10_sell':[], 'ma20_sell':[], 'ma60_sell':[], 'ma120_sell':[]}
        self.df_tracking_data = pd.DataFrame(self.tracking_data, columns=['code', 'buy_date', 'buy_price', 'buy_value', 'sell_date', 'sell_price', 'sell_value', 'profit',
                                                                          'ma5_buy', 'ma10_buy', 'ma20_buy', 'ma60_buy', 'ma120_buy',
                                                                          'ma5_sell', 'ma10_sell', 'ma20_sell', 'ma60_sell', 'ma120_sell'])
        self.init_money = 10000000
        self.money_by_unit = 1000000
        self.all_profit = 0

        while self.today != end_date:
            self.today_profit = 0
            self.yesterday = self.cal_subday(self.today, 1)

            print("현재 조회일자:", self.today, "(self.today값), ", self.yesterday, "(self.yesterday값)")
            self.buy_list = self.check_list()
            self.buy()

            print("현재 보유 종목")
            print(self.df_account)

            self.sell()
            print("현재 잔고:", self.init_money)
            print("일별 손익:", self.today_profit)
            self.all_profit = self.all_profit + self.today_profit
            print("누적 손익:", self.all_profit)
            print("거래 이력:")
            print(self.df_tracking_data)

            ## 추가 코드 ##
            self.df_tracking_data.to_excel(xlsx_dir, sheet_name='algorithm_1')
            print("거래 데이터가 저장되었습니다.")

            self.today = self.cal_addday(self.today, 1)

## 출력 결과 ##
현재 조회일자: 20200103 (self.today값),  20200102 (self.yesterday값)
현재 보유 종목
       date    code buy_price  quantity  profit
0  20200103  004170    292500       3.0     NaN
현재 잔고: 9122500
일별 손익: 0
누적 손익: 0
거래 이력:
     code  buy_date buy_price  ...  ma20_sell  ma60_sell  ma120_sell
0  004170  20200103    292500  ...        NaN        NaN         NaN
[1 rows x 18 columns]
거래 데이터가 저장되었습니다.

현재 조회일자: 20200106 (self.today값),  20200103 (self.yesterday값)
현재 보유 종목
       date    code buy_price  quantity  profit
0  20200103  004170    292500       3.0     NaN
현재 잔고: 9122500
일별 손익: 0
누적 손익: 0
거래 이력:
     code  buy_date buy_price  ...  ma20_sell  ma60_sell  ma120_sell
0  004170  20200103    292500  ...        NaN        NaN         NaN
[1 rows x 18 columns]
거래 데이터가 저장되었습니다.

현재 조회일자: 20200107 (self.today값),  20200106 (self.yesterday값)
현재 보유 종목
       date    code buy_price  quantity  profit
0  20200103  004170    292500       3.0     NaN
현재 잔고: 9122500
일별 손익: 0
누적 손익: 0
거래 이력:
     code  buy_date buy_price  ...  ma20_sell  ma60_sell  ma120_sell
0  004170  20200103    292500  ...        NaN        NaN         NaN
[1 rows x 18 columns]
거래 데이터가 저장되었습니다.

 

거래 데이터가 입력되어 있는 모습 ①

거래 데이터가 입력되어 있는 모습 ②

 

ERROR : "PermissionError: [Errno 13] Permission denied:"

    이 오류는 to_excel() 메서드(함수)를 실행할 때 발생하는 오류이다. 즉, to_excel()을 통해 데이터를 입력한 후 저장을 하고자 했으나 파일이 열려 있어 데이터를 저장할 수 없다는 것이다. 그렇다면 우리는 ①번 지점에서 데이터를 저장하는 게 무슨 의미가 있는가? 어차피 중간에 데이터를 확인하다가 오류가 발생하게 되면 코드는 멈춘다. 
    따라서 그냥 ②번 지점에서 데이터를 입력해도 되고, 아니면 매도 종목이 발생한 시점마다 파일을 열어서 전체 선택(Alt + A) 후 복사하기(Ctrl + C)를 해서 다른 엑셀 파일을 열어서 결과물을 분석해도 된다.

Traceback (most recent call last):
  File "C:\anaconda\envs\py36\lib\zipfile.py", line 1113, in __init__
    self.fp = io.open(file, filemode)
PermissionError: [Errno 13] Permission denied: 'C:\\Users\\~~\\PycharmProjects\\trading\\post_algorithm\\algoritm_1_result.xlsx'

 

 

 

---