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

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

 

 

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

 

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

 

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

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

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 웹 페이지 정보 시트에서 실시간 자료 볼 수 있도록 세팅하기 ▶ 엑셀(Excel) VBA - 웹브라우저(인터넷 익스플로러) 띄우는 방법 ▶ 엑셀 Excel 통합 문서에서 하이퍼링크 이용하기 ▶ 엑셀 Excel 텍스트 링크 삽입하는 방법 ▶ 엑셀 Excel 마이크로소프트 템플릿 사이트에서 서식 파일 다운로드 하기

 

 

 

 

 

 

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

 

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

 

 

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

 

 

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

 

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

 

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

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

 

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

 

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

 

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

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 웹 페이지 정보 시트에서 실시간 자료 볼 수 있도록 세팅하기 ▶ 엑셀(Excel) VBA - 웹브라우저(인터넷 익스플로러) 띄우는 방법 ▶ 엑셀 Excel 통합 문서에서 하이퍼링크 이용하기 ▶ 엑셀 Excel 텍스트 링크 삽입하는 방법 ▶ 엑셀 Excel 마이크로소프트 템플릿 사이트에서 서식 파일 다운로드 하기