엑셀 파이썬이라는 날개를 달다

파이썬을 알고 있는가?

파이썬 파이썬 최근 주변에서 얘기하는 것을 자주 접했을 것이다. 파이썬 무엇인가? 직장인에게는 내업 무를 자동으로 해줄 도구이며, 회사에게는 오류 없이 자동으로 처리해줄 도구가 된다. 파이썬은 쉽게 말해 컴퓨터와 대화할 수 있는 언어다. 그런데 그 언어가 아주 쉽다. 보통 프로그래밍 언어라고 하면 C언를 많이들 알 것이다. 검은색 바탕에 함호 같은 알파벳들이 나열되어있는.....

이런 것을 상상할 수 있다. 파이썬 역시 비슷 하지만 그 어떤 언어보다 접근이 쉽고 간단히 배울 수 있다는 장점이 있다. 파이썬의 장점들은 나열해도 끝이 없으며, 이번에 설명할 엑셀과의 호환도 매우 좋은 언어이다.

엑셀과 호환??

엑셀은 회사에서 빠질 수 없는 대표적인 소프트웨어다. 숫자를 다룬다면 엑셀만큼 최적화되고 안정화되어있는 프로그램을 찾을 수가 없다. 아무나 쉽게 숫자를 다룰 수 있도록 되어 있고 조금만 더 공부하면 엑셀 자체 프로그램을 탄생시킬수있다. 엑셀을 한 번이라도 써본 사람이라면 sum, count 함수를 쉽게 써보았을 것이다. 조금 더 엑셀을 고급스럽게 만들어 주는 함수가 sumif, vlookup, hlookup, match, offset 등이 있다. sumif와 vlookup과 기본적인 함수를 조합해서 업무를 자동화하는 경험을 한 번씩은 해보았으리라 생각된다. 요즘은 이런 함수를 모르더라도 예스폼과 같은 사이트에서 무료 또는 정액제 회원이라면 쉽게 자동화 양식을 구할 수 있다.

엑셀은 그 자체로 훌륭한 프로그램이지만 사용하다 보면 한 번씩 아쉬운 점들이 있다. 가장 아쉬운 점은 데이터양이 많아지면 느려진다. 순수하게 수식 없이 데이터만 저장된 경우는 조금 낳은 편이지만 데이터와 엑셀 수식이 같이 섞여있다면 말도 못 하게 느려지는 경우가 있다. 이런 아쉬운 점을 해결할 수 있는 부분이 엑셀 자체에서 가지고 있는 VBA라는 프로그래밍 언어이다. VBA 역시 하나의 프로그래밍 언어로서 엑셀과 조합해 훌륭한 역할을 하기도 한다. 그러나 VBA 역시 어렵다는 단점이 있다. 이런 점에 아쉬하던 개발자들은 파이썬으로 엑셀을 사용하는 방법들을 많이 내놓았다. 대표적으로 파이썬에는 판다 스라는 라이브러리가 있다. 데이터 프레임이라고 하는 개념으로 엑셀의 스프레드시트를 자유자재로 다룰 수 있고 엑셀의 거의 대부분의 기능을 파이썬 판 다스로 해결이 가능하다. 그 외에도 openpyxl, xlsxwriter와 같은 라이브러리가 있다. 이자체만으로도 훌륭하다. 단순히 파이썬만 알고 있으면 데이터를 가공해서 엑셀로 만들 수 있고 엑셀의 서식까지도 자유자재로 다룰 수 있으니까

그런데 이런 라이브러리들도 빠진 부분은 있다. 엑셀의 가장 큰 장점을 살리지 못한다는 점이다. 엑셀은 프로그램을 모르는 사람도 간단한 수식들로 데이터를 확인할 수 있고 아무 셀에나 원하는 답들이 나올 때까지 테스트도 가능하다. 이것은 컴퓨터와 대화하는 GUI의 대표적인 방식이다. 다. 파이썬이 이러한 엑셀 고유의 장점을 살려줄 수는 없다. 물론 아나콘다라고 하는 패키지툴내에 주피터 노트북을 사용하면 대화형으로 하나하나 직접 확인해가면서 사용이 가능하지만 데이터를 유동적으로 다루면서 직장상사에게 직관적으로 보여줄 만한 보고서까지 만드는 데는 한계가 있다. 그렇다면 최상의 조함은 무엇일까?? 엑셀의 기본 GUI에서 자유롭게 함수를 사용할 수 있으면서 파이썬을 활용해 대량의 데이터를 다룰 수 있으면 가장 좋은 조합이 아닐까??

xlwings 엑셀과 파이썬의 최상의 조합

파이썬에는 xlwigns라고 하는 라이브러리가 있다. 이 라이브러리는 엑셀과 상호호환을 할 수 있도록 설계된 라이브러리이다. 아예 설치부터 엑셀의 추가 기능으로 addin을 하고 시작한다. 이렇게 해서 엑셀의 모든 기본 기능을 사용하고 추가로 파이썬을 사용할 수 있도록 하는 것이다. 이 조합으로 어떤 것들을 할 수 있을까?? 나는 xlwings를 사용하기 이전에 구글 코랩을 활용해서 여러 금융데이터를 수집하고 분석하는 테스트를 해보았다. 주식투자를 위한 데이터를 분석하고, 때로는 텔레그램과 연결하여 챗봇을 만들어 사용하곤 했다. 분명히 훌륭한 기능이기는 하지만 더욱 직관적이고 체계화된 보고서 형식으로 프로그램을 구성하고 싶은 욕심이 있었는데, 이런 욕구를 xlwings라이브러리가 채워준다. 아래의 이미지들은 특정 데이터를 크롤링하는 이미지다.


-한국 신용평가 금리 스프레드 크롤링

www.kisrating.com/ratingsStatistics/statics_spread.do

한국신용평가

-상장사 목록 수집

kind.krx.co.kr/corpgeneral/corpList.do?method=loadInitPage

대한민국 대표 기업공시채널 KIND

-주가 데이터 수집

-comp.fnguide 재무제표 수집

comp.fnguide.com/SVO2/ASP/SVD_main.asp?pGB=1&gicode=A005930&cID=&MenuYn=Y&ReportGB=&NewMenuID=11&stkGb=&strResearchYN=

삼성전자(A005930) | Snapshot | 기업정보 | Company Guide

 

이렇게 수집한 데이터를 이용해서 별도의 시트에서 특별한 분석을 해볼 수 있다. 간단한 분석을 해보고 유의미한 결과를 도출한다면 그것 역시 파이썬으로 자동화를 시키는 방식이다.

xlwings로 엑셀에 날개 달기

그러면 본격적으로 엑셀에 날개를 달아주는 방법을 알아보도록 하자! xlwings를 사용하려면 기본적으로 엑셀이 필요하다. 엑셀은 2007 버전 이후부터 모두 적용이 가능하지만 특정 몇 개의 버전의 경우 버그가 존재한다. 현재까지 사용해본바로 2010 이후 버전부터는 오류 없이 잘되는 것을 확인했다. 2007 버전, 2016 버전에서 몇 가지 오류를 발견했는데 2007 버전은 아예 addin이 안 되는 경우도 찾아볼 수 있었다. 그러니 엑셀은 최신의 버전이 오류 없이 사용이 가능할 것으로 예상된다.

엑셀을 기본적으로 가지고 있다면 파이썬의 설치가 필요하다. 파이썬 설치는 아래 링크에서 다운로드하고 파이썬 버전은 상관없다. 그냥 가장 최신 버전을 사용하자!! 특별히 설치 시 고민할 부분은 없고 한 가지 꼭 필요한 것은 Add python 3.x to PATH를 클릭하시고 진행하는 것이 좋다. 이것을 클릭하지 않는 경우도 사용하는데 지장이 없지만 나중에 이상한 오류(환경변수 설정)들을 간혹 만날 수 있다. 입문자일수록 반드시 클릭하고 넘어가는 것이 좋다.

https://www.python.org/

Welcome to Python.org

 

체크 꼭!! 필요

 

여기까지 완료했다면 이제부터 xlwings라이브러리를 설치해야 한다. xlwings라이브러리에 대한 공식 홈페이지는 링크를 통해 확인 가능하다. 그러나 처음 할 때 이런 문서 본다고 알 수 있을 리가 없다. 나중에 시간 날 때 그리고 더깊이 알고 싶을 때 때 찬찬히 읽어보도록 하자

https://www.xlwings.org/

Python for Excel

그러면 가장 처음 명령 프롬프트를 찾자 윈도 검색창에서 "명령 프롬프트" 또는 "cmd"라고 검색하고 클릭하면 씨 커먼 창이 나온다.

씨꺼먼창이 떴으면 아래 명령어를 순차적으로 입력한다.

1. xlwings 설치하기

pip install xlwings 

 

2. xlwigns addin 하기 : 엑셀에 추가 기능으로 설치하는 것
- 간혹 이 명령어가 안 먹힐 때까 있다. 그럴 땐 재부팅을 해보거나 "pip uninstall xlwings"를 하고 다시 install을 하도록 하자

xlwings addin install

3. xlwigns설치 확인

pip list

완료 후 엑셀 확인 : xlwings탭 생성됨

 

xlwings설치는 마쳤으며, 엑셀에 설정을 해줘야 한다. 엑셀이 정상적으로 파이썬을 추가 기능으로 인식할 수 있도록 해주는 작업이니 한 번만 해두면 된다.



1. 엑셀 추가 기능 설정

2. 엑셀 보안설정

이렇게 엑셀에 날개를 달아주는 작업을 모두 마쳤다. 이제 가볍게 기본 샘플 코드를 이용해서 어떻게 엑셀에서 파이썬 코드를 사용하는지 알아보도록 하자.

xlwings 샘플 코드 실행해보기

xlwings는 quickstart기능을 가지고 있다. 다른 설정 없이 엑셀과 파이썬파일을 연결해서 하나의 세트로 만들어주는 작업을 한다. cmd창이나 편집기를 이용해서 엑셀 파일을 생성할 경로로 이동한다. 나는 폴더 편집 등 편리한 이유로 vscode를 사용한다. 예시는 vscode로 진행하지만 cmd에서도 동일하게 하면 된다. 하위 폴더로 이동할 때는 "cd 폴더명" 상위 폴더로 이동할 때는 "cd.."을 하면 된다. 자 이제 원하는 위치로 이동해서 사용할 엑셀 파일을 생성한다.

생성된 파일

 

파일을 열고 구조를 살펴보기로 하자. 파일을 열어보면 빈 시트 하나와 xlwings.conf라는 시트가 보인다. xlwings.conf라는 시트만 놔두고 나머지는 모두 엑셀 기본 기능과 동일하다. 자유롭게 작성해도 된다.

다시 빈 시트를 클릭하고 상단 리본 메뉴의 xlwings탭의 좌측 Runmain을 클릭해보면 무언가 빈 시트에서 글자가 생성되는 것을 확인할 수 있다. 계속 클릭해보자 계속 "hello xlwings"와 "bye xlwings"를 반복한다.

이번에는 조금 다른 기능을 알아보자 xlwings탭에서 import Funtions를 클릭하고 우측 모듈명에 처음 생성했던 파일명을 입력한다. 그리고 아무 셀을 선택하고 sum함수를 쓰는 방식으로 "=hello("본인 이름")"을 입력한다.

xlwings는 기본적으로 이런 방식으로 함수처럼 사용할 수 있는 기능과 run명령을 통해 사용할 수 있는 방식을 제공한다는 것을 알려주고 있다. 왠지 활용만 잘하면 멋진 걸 만들 수 있겠다는 생각이 들지 않는가?? 이제 이런 게 어떻게 가능 한지 알아보겠다. 엑셀과 같이 생성되었던 파이 썬파일이 있다. 그것을 편집기에서 열어보면 아래와 같은 코드가 있다. 기본 코드인데 def main(): 이하 코드가 엑셀에서 run 했을 때 작동했던 코드이고, @xw. func이하 코드가 셀에서 함수처럼 사용하는 것이다. 그런데 이 코드만으로 엑셀에서 작동했다고???

잠깐 위에서 엑셀은 VBA라는 프로그래밍 언어를 가지고 있다고 했다. 그런데 아예 밑바탕이 VB인 엑셀 프로그램에 그냥 붙었을까? 사실은 그렇지 않다. 엑셀로 돌아와서 "Alt+F11"을 눌러보자!! 팝업창이 뜨는데 그곳에 VB언어로 작성된 코드가 있다. 결론적으로 엑셀과 파이 썬파일을 중간에서 VBA가 중개역할을 하고 있는 것이다.

그렇다면 아이고!! VB도 공부해야 해??? 아니다 절대 그렇지 않다 알면 금상첨화겠지만 몰라도 된다. VB로 작성된 코드를 자세히 살펴보자!! 뭐진 모르겠는데 뭐라 뭐라 되어 있다. 그리고 끝에. main()이라는 게 보인다. 파이 썬파일에서 보았던 그 함수다. 그렇다는 얘기는 파이 썬파일에 다른 코드를 작성하면 이걸로 불러 갈 수 있겠네??라는 생각이 가능하다. 그리고 더 확대해서 무언가 엑셀에서 VB를 호출할 수 있는 장치를 만들고 VB가 파이썬 파일의 함수들을 불러 준다면 여러 개의 기능도 가능하겠네??라고 생각할 수 있다. 재밌지 않을까??

지금까지 알아본 것을 종합해보면 사실 단순한 얘기는 아니다. 그러나 한번 세팅만 잘해놓으면 정말 두고두고 유용한 기능들을 엑셀에서 활용하고 또 생각지 못한 다른 것이 갑자기 해보고 싶다면 골머리 싸멜필요없이 그냥 엑셀의 함수를 활용해서 간단히 추가해도 좋다. 처음부터 기획해서 뭔가 뚝딱 만들어 우와!! 잘 만들었다 이러려면 기획을 잘해야 한다. 그러면 기획자이자 개발자일을 하겠지.. 일반 직장 다니겠는가 우리는 그냥 회사에서 조금 편하게 일을 하고 싶을 뿐이다. 그러면 지금 당장 내가 반복적으로 하는 업무가 무언가?부터 파악하고 직접 도전해보자! 도전해보고 하나씩 하나씩 개선해나가면 된다. 파이썬 언어의 학습이 부족한 것은 엑셀이 채워 줄 것이고 엑셀로 부족한 부분은 파이썬이 채워 주는 신기한 경험을 할 수 있을 것이다

xlwings로 직접 만들어 보고 싶다면??

네이버나 구글에서 원하는 기능의 함수를 검색해보면 쉽게 얻을 수 있다. 우리보다 시작을 먼저 한 분들이나 전문 개발자분들이 무료로 공개해놓은 소스들이 많다. 그런 것을 이용해서 가볍게 접해보자!! 처음부터 욕심내서 나는 파이썬을 처음부터 공부할 거야!! 하고 접하면 빨리 포기할 수 있다. 남이 만들어 놓은 코드들을 하나씩 실행해보면서 왜 이렇게 만들었을까 이건 무슨 역할을 할까?? 를 고민해보자 조금 더 빨리 체득할 수 있다. 아래 링크는 네이버에서 운영하고 있는 파이썬 소개 블로그다. 참고해보도록 하자

blog.naver.com/o12486vs2/222272083217

간단 금리스프레드 수집 자동화 엑셀 만들기

학습할 여유가 없으니 자동화 프로그램을 즉시 사용하고 싶다면??

여러 가지 복잡한 사항이 싫고 처음부터 뭘 해야 할지 알아보기 귀찮다면 전문가 사이트를 이용해도 좋다. 요즘은 전문가를 직접 연결해주는 사이트가 많다. 대표적으로 크몽을 예로 들 수 있다. 여기서 전문가에게 나의 아이디어와 적당한 수고비를 지급하고 개발해달라고 하면 된다. 나의 경우 몇 가지 프로그램을 기성품 형식으로 크몽에 판매하고 있는 것이 있다. 금융데이터를 수집해서 SRIM을 자동으로 계산하는 프로그램과 네이버 부동산의 매물정보를 크롤링하는 기능을 가진 엑셀 프로그램, 공시채널의 투자유의사항에 지정된 종목들을 크롤링해서 관리할 수 있는 프로그램 등이다. 이외에도 내가 실무적으로 사용하고 있는 프로그램들도 있지만 아직 안정되지 않아 게시하지 못하고 샘플 코드로 블로그에 게시 해 둔 것이 있으니 참고하면 된다.




https://kmong.com/gig/302471

상장사 모든종목 요약 투자정보 자동수집 엑셀 드립니다. | 49900원부터 시작 가능한 총 평점 5점

https://kmong.com/gig/304277

기업공시채널KIND 투자유의사항 자동수집 엑셀 드립니다. | 12900원부터 시작 가능한 총 평점 5점의

https://kmong.com/gig/304476

부동산 매물정보 자동수집 엑셀 드립니다. | 49900원부터 시작 가능한 총 평점 5점의 IT·프로그래