엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

환경: Microsoft Excel 2013

 

엑셀의 고급 기능 중 외부 원본 데이터와 연계해서 데이터를 가져오는 기능들이 있습니다. 대표적인 것이 데이터베이스인 MS SQL Access 입니다. 그 외에 웹 쿼리가 있습니다. 웹 페이지와 연계해서 실시간 데이터를 가져오는 것이죠. 새로 고침 간격을 조절할 수 있기 때문에 실시간 데이터를 가져올 때 많이 이용합니다. 오늘은 주식의 시세를 가져와서 자신이 매수한 가격과 비교해서 수익률을 계산하는 방법을 알아 보겠습니다.

 

이번 포스팅은 주식에 관심이 없으신 분들도 알아 두면 유용하게 쓰일 겁니다. 웹 쿼리를 이용해서 다른 실시간 데이터들을 활용할 수 있기 때문입니다. 우선 주식 시세를 알아 올 수 있는 사이트를 알아야겠죠. 팍스넷은 웹 상에서 실시간 시세를 제공합니다. 팍스넷에 접속해서 시세 탭으로 가서 자신이 주식을 매수한 회사를 고릅니다. 그리고 주소 입력창에 있는 URL 을 복사합니다.

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

가져올 데이터 주소를 알았으면 웹 쿼리를 만들어 보겠습니다. [데이터] 탭으로 가서 [] 리본 메뉴를 클릭합니다

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

새 웹 쿼리 입력창이 뜨면 복사한 URL 을 주소란에 입력하고 이동 버튼을 눌러 줍니다. 주소는 아래와 같은데 주식 마다 고유 번호가 있기 때문에 code 값만 알아서 변경해 주면 다른 주식들도 쉽게 추가할 수 있습니다.

http://paxnet.asiae.co.kr/asiae/stockIntro/indCurrent.jsp?code=003520

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

복사한 주소로 이동하면 데이터를 가져올 수 있는 곳에는 노란색 화살표가 표시됩니다. 그 중 상단에 실시간 주식 시세 부분을 클릭합니다. 그럼 화살표가 녹색으로 변경 됩니다. 그리고 아래로 내려가 [가져오기] 버튼을 클릭합니다

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

이제 가져온 데이터를 표시할 위치를 지정해야 합니다. 워크시트에 셀을 지정하고 확인 버튼을 눌러 창을 닫으면 바로 데이터를 가져와서 표시해 줍니다. 시세는 3번째 셀에 있는 값입니다. 시세와 등락률이 같이 붙어 있네요.

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

데이터 분리해서 수익률 계산하기

 

이제 자신이 구매한 주식의 가격과 실시간으로 올라오는 데이터를 비교해서 얼마의 수익률이 나는지 계산해 보겠습니다. 실시간으로 올라오는 셀 데이터를 복사합니다

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

복사한 실시간 데이터를 가격 계산을 위한 표에 연결해야겠죠. 오른 마우스를 눌러 붙여넣기 옵션에서 [연결] 아이콘을 클릭합니다. 그럼 값이 아닌 셀을 연결해서 데이터를 표시하게 됩니다

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

그런데 원본 데이터에는 현재가 뿐만 아니라 등락률도 같이 표시되어 있어서 데이터를 잘라야 합니다. “현재가만 잘라내기 위해서 FIND 함수와 LEFT 함수를 이용하였습니다. 첫 번째 공백이 있는 위치를 FIND 함수로 찾아내서 LEFT 로 텍스트를 잘라 내는 것이죠.

 

=LEFT(Sheet1!$A$3,FIND(" ",Sheet1!$A$3))

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

다음은 매수가와 현재가를 이용해서 수익률을 계산해야 합니다. 화면에 나와 있는 식을 참고해서 작성합니다. 데이터는 백분율로 표시를 해야 합니다

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

 

수익률의 소수점을 변경하고 싶다면 데이터 표시 형식 콤보박스 아래 아이콘을 클릭합니다. 왼쪽은 소수점을 늘이고 오른쪽은 줄이는 기능을 합니다

엑셀 Excel 웹 쿼리 이용해서 실시간 주식 시세 조회로 수익률 계산하기

  

이상으로 웹 쿼리 기능을 이용해 주식 시세를 알아 와서 실시간 수익률 계산을 해 보았습니다. 주식 프로그램을 깔아 놓기 곤란한 곳이라면 이렇게라도 시세를 확인해 보세요. 돈 주고 샀는데 얼마나 궁금하겠습니까


Posted by 녹두장군