매개변수 활용 웹 쿼리로, 네이버 주가지수 데이터를 엑셀로 자동으로 가져와 주가지수 실시간 예측 하기

주가지수 예측

앞선 글에서, 엑셀의 예측 기능을 활용하려는 실험의 하나로……

네이버의 분 단위 주가지수 실시간 데이터를 엑셀로 불러와, 이를 토대로 5~10분 후의 주가지수를 엑셀의 예측 기능으로 예측해 보고, 이를 실제 값과 비교해 보았었는데, 20~40분 정도의 분 단위 주가지수를 이용하면 향후 5~10분 정도의 주가지수는 실제 값과 차이가 거의 없었음을(편차 기준 0.1% 이하) 소개한 바 있었다. (10분 이내에 대해서는 편차 기준 2.5 이내, % 기준으로는 0.1% 이내였다.)

또한 동일한 방법으로 매일의 주가지수 종가를 이용하여, 향후 5일 정도의 주가지수도 유사하게 예측해 보고, 실제치와 비교해 보기도 했는데, 실제와 오차가 그리 크지는 않았으나, 분 단위 예측에 비해 일일 단위의 주가지수 종가 추정치는 실제 값 대비 차이가 분 단위 예측치보다 좀 더 컸었다. (향후 2-3일은 예측치-실제 값 편차가 10 이하로 작아 실제 값에 매우 근사했고, 5일 이후는 편차가 40~60 정도로 실제 값 대비 약 2.2% 수준)

해당 내용에 대해서는 다음 글을 먼저 참조하기 바란다.

https://kongyine.com/223412883613

엑셀로 향후 (5~10분, 2-3일) 주가지수를 예측해보고, 실제값과 비교해 보기

네이버 주가지수 데이터를 엑셀로 가져오기 – 웹에서 데이터 가져오기

네이버의 분 단위 주가지수 실시간 데이터를 엑셀로 가져오는 방법 자체는….

앞선 글에서 소개했듯이, 다음과 같은 네이버 증권 화면에서 “시간별 시세” 부분에 마우스를 위치시키고,

다음과 같이 마우스 우측 버튼 메뉴에 있는 “프레임 소스 보기”를 이용해

여기서, 다음과 같은 분 단위 주가지수에 대한 외부참조 URL을 확인하여,

엑셀의 “데이터” –> “웹” 메뉴를 선택하고,

여기에 복사해둔 URL 주소를 넣는 방법으로 가져온 것이었다.

그런데 이러한 분 단위 주가지수를 실시간으로 가져와, 실시간 예측에 활용하려면 풀어야 할 다음 두 가지 기술적 문제가 있다.

문제 1 – 실시간으로 달라지는 URL 만들기

문제 2 – 가급적 많은 데이터를 한 번에 자동으로 가져오기

실시간으로 변하는 주가지수를 가져오기 – 자동으로 갱신되는 변수 포함 URL 만들기

첫 번째 문제는 URL 주소에 다음처럼 검색의 기준으로 하는 시간이 URL의 일부로 들어가야 한다는 것이다.

즉, 매 시점마다, URL을 시간을 갱신해 주지 않으면, 항상 동일한 시간을 기준으로 검색해서 항상 동일한 데이터를 가져오게 되므로, 실시간으로 변화되는 분 단위 주가지수를 가져오려면 URL의 일부인 기준 시간을 현재 시점에 맞추어 자동으로 갱신해 주는 게 필요하다.

이것은 다음과 같은 방법으로 해결이 가능하다.

즉, 검색 기준 시간을 현재 시간을 나타내는 함수를 사용하되, 이를 문자열 형태로 변환하여 특정 셀에 넣고(아래 예에서는 B2), 이를 URL 내에서 변수의 형태로 받아서, URL 주소를 완성시키도록 만들어주면 된다. (참고: 엑셀의 & 연산자를 이용해 문자열을 결합시켰다.)

그러면 다음과 같이 현재 시간에 따라, 기준 시간이 변화하는 URL이 만들어져서, 첫 번째 문제는 쉽게 해결이 가능하다.

참고로, 실제에 있어서는 주식시장이 주말이나, 공휴일 등에는 주가지수가 변하지 않기 때문에, 네이버 증권의 값들은 항상 최근 값을 표시하는 형태이므로, 주식시장이 개장하는 않은 공휴일이나, 시장이 끝난 이후 시점에 위의 방법을 단순히 그대로 적용하면 오류가 발생할 수도 있다. (즉, 그날 거래가 있었더라도 이미 주식 시장이 끝났다면 대체로 15:30 기준으로, 만일 그날 거래가 없었다면 이전 최종거래일의 15:30 기준으로 URL이 만들어져야 한다.)

이 때문에, 최종적으로 URL의 일부가 될 Timemark는 주식시장 개장 여부 및, 이미 주식시장이 끝났는지를 판별하여 만들어야 하므로, 다음과 같이 URL에 반영할 Timemark 문자열을 상황을 고려하여 조합되도록 만들었는데,

각 셀의 수식은 다음과 같이 만들었다.

이때 최종 개장 일자를 판별하는 방법은 여러 가지 방법이 가능(예: 공휴일 목록) 하나,

내 경우에는 다음과 같은 네이버 증권의 일별 주가지수 표의 최상단 일자가 바로 최종 거래 일임에 착안하여,

이를 별도의 시트에 가져오고, 이 테이블의 최상단 일자(=A2 셀의 값)를 이용하여 비교하는 방법을 사용하였다.

다수 URL 자료를 한 번에 자동으로 가져오기 – 매개변수를 이용한 웹 쿼리

두 번째 해결이 필요한 문제는….

여러 URL 주소의 자료를 한 번에, 그리고 자동으로 가져와야 한다는 것이다.

네이버 증권의 분 단위 주가지수는 하나의 URL 페이지당 6개의 자료로 구성되어 있기 때문에, 신뢰성 있는 예측을 위해 30-40분 분량의 데이터를 가져오려면, 최소한 5-6개의 URL 페이지 데이터를 가져와야 하는데, 이 작업을 수작업으로 반복할 수는 없기 때문이다. 더구나 URL은 검색 기준 시간을 반영하여 매번 달라지는 점 때문에, 이 부분은 반드시 자동화가 필요하다.

그런데 이 부분은 위에서 설명한 웹 데이터를 가져오는 부분에 매개변수를 이용하여 해결할 수 있다.

이 방법은 URL들로 먼저 하나의 테이블을 만들고, 이 테이블에 작동하는 매개변수를 할당하고, 표준화된 동작을 함수로 지정함으로써, 여러 URL을 한 번에 처리하여 웹으로부터 데이터를 가져오는 방법이다.

방법을 설명하면….

먼저, 사용할 여러 URL이 있는 부분을 테이블로 만들어 주어야 한다.

URL이라는 머릿글을 하나 추가한 상태에서, 다음과 같이 테이블로 만들 부분을 마우스로 선택한 후 Ctrl+T를 입력하여 다음 화면이 나오면

확인 버튼을 눌러 다음과 같은 테이블 형태로 바꾸어준다.

그리고 이 테이블을 마우스로 선택한 상태에서, 엑셀의 데이터–>테이블 순서로 선택해 준다.

(주의: 테이블/범위에서 글 선택해 주어야 한다.)

그러면 다음과 같은 테이블이 되는데, 여기서 URL 중 하나를 복사해 두고

“매개변수 관리” –> “새 매개변수”를 선택한다.

그러면 다음과 같은 화면이 나오는데, 여기에 임의로 이름을 지정해 주고,

“현재 값” 부분에 복사해둔 URL을 붙여 넣어준다. 그리고 확인을 눌러준다.

그리고, 이 매개변수에 대해 다음과 같이 “새 원본”–>”기타 원본” –> “웹”을 선택해 준다.

그리고 다음 화면에서 URL 부분을 “매개변수”를 선택해 준다.

즉, URL 값을 매개변수를 이용해서 간접적으로 전달하는 방식이 되는 것이다.

다음과 같이 데이터가 불러와지는데, 이때 불러와진 테이블 항목에서 마우스 우측 메뉴로 “함수 만들기”를 선택해 준다.

임의로 함수 이름을 넣어주면, 일단 매개변수를 만드는 것과, 이 매개변수를 이용한 함수까지 만들어지게 된다.

지금까지는 URL을 가지고 이루어지는 동작에 대해, 일종의 Sample 동작을 하고, 이를 함수 형태로 만들어준 것으로 이해하면 된다.

이제 이렇게 만들어진 매개변수 함수를 활용하여, 여러 URL에 해당되는 값들을 한 번에 가져오기 위해

위에서 테이블 형태로 만들어둔 것을 선택한 후,

상단 메뉴에서 “열 추가”를 선택하고, “사용자 지정 함수 호출”을 선택해 준다.

다음 화면이 나오면, 다음과 같이 선택해 주면 된다.

확인을 눌러주면 다음과 같이 되는데, 다음 부분을 마우스로 클릭해 주면

이 부분이 확장되면서, 데이터로 불러올 항목들이 나타난다.

해당되는 항목들을 선택한 상태에서 확인을 눌러주면 다음과 같이 표에 있던 모든 URL에 해당되는 데이터들이 한 번에 로딩되며,

단기 및 로드 메뉴를 이용해

다음과 같이 엑셀 시트로 가져오게 된다.

시트로 데이터가 로딩된 후, 시간 항목은 다음과 같이 표시 형식을 시간으로 변경해 주면

다음과 같이 정상적으로 시간이 표시되는 것을 볼 수 있다.

참고로, 데이터 항목 중 불필요한 항목은 아래와 같이 로딩된 상태에서, 다음과 같이 “열 제거” 명령으로 삭제하면 된다.

또한 각 열의 이름은 다음과 같이 “이름 바꾸기”를 이용하여 변경하면 된다.

또한, 이렇게 만든 테이블이 정해진 시간 간격마다, 자동으로 갱신되도록 하려면,

다음과 같이 “쿼리” 메뉴에서 “속성”을 선택하고,

다음 부분에서 원하는 시간 간격을 원하는 대로 지정해 주면 된다.

위의 과정이 복잡해 보이지만, 막상 직접 해보면 간단하다. 전체 과정에 대한 동영상은 다음과 같다.

참고로, 이처럼 매개변수를 이용해 값을 가져오는데 사용했던 다음 URL 목록은 실제로는 쿼리에 들어있는게 아니라 매개변수로만 연결되어 있고, 매번 쿼리 동작이 작동할때마다 해당 쿼리에서 매개변수의 값으로 참고해야 하므로, 이 부분을 삭제해 버리면 더이상 쿼리가 동작하지 않으므로, 반드시 엑셀시트내에 유지해 두어야 한다.

주가지수 예측치 자동갱신하기

위에 소개한 방법으로 네이버의 1분 단위로 갱신되는 주가지수를 엑셀로 자동으로 가져오게 되면, 앞선 글에서 소개한 (https://kongyine.com/223412883613) 방법으로 1분단위로 향후 5~10분 정도의 주가지수 예측치를 갱신할 수 있는데, 이 예측 부분을 엑셀의 매크로 기능을 이용하면, 단축키 하나를 입력할때마다 자동으로 예측치가 갱신되도록 자동화할 수 있다.

매크로를 이용하여 위에 불러온 데이타로 주가지수 예측치를 갱신하는 방법은 다음과 같다.

먼저, 엑셀 메뉴에서 “매크로기록”을 선택해주고,