[Power Automate Desktop] 기업 시가총액 순위, 엑셀 저장 (Detail Version)

심플버전에서는 웹 페이지의 표를 전체 HTML테이블 추출을 통해 한번에 가져와 엑셀로 저장하였습니다. 그러다보니 엑셀이 완전한 형태로 만들어지지 않게 되었고 다음 작업을 자동화로 만들기에도 좀 애매합니다.

2021.06.17 - [RPA/Power Automate Desktop] - [Power Automate Desktop] 기업 시가총액 순위, 엑셀 저장 (Simple Version)

[Power Automate Desktop] 기업 시가총액 순위, 엑셀 저장 (Simple Version)

 

그래서 심플버전과 좀 다르게 데이터를 하나씩 읽어 하나씩 쓰는 방법으로 엑셀 데이터를 생성하고 완료 후 이메일 발송까지 해보겠습니다.

시가총액 웹 페이지까지 들어가는 것은 이전글로 갈음하겠습니다. 궁금하신 분은 위 링크에서 확인해주세요.

 

자동화를 구현하기 전 엑셀을 새로 열어 시가총액 타이틀을 따로 입력하고 저장해줍니다. 엑셀 생성 시 유의할 점은 전일비를 아래 그림과 같이 2개의 셀로 나누어 줘야 합니다. 이유는 아래서 설명합니다.

 

타이틀을 따로 만들어서 저장하는 이유는 타이틀 부분을 요소 추출하면 위 그림처럼 13행 1열의 형태로 추출이 되어야 하는데 반대로 1행 13열 형태로 추출이 됩니다. 바꾸려도 해도 방법도 없고 추출된 것을 수정도 불가능해 데이터를 넣을 엑셀을 별도로 만들고 타이틀을 미리 집어 넣었습니다.

 

웹 페이지의 제작 환경, 방법에 따라 데이터 추출이 달라집니다. 정답은 없고 해당 페이지의 상황에 맞게 처리해야 합니다.

 

먼저 전체 플로어는 아래와 같습니다.

 

이제 작업창에서 타이틀을 넣어서 미리 만들었던 데이터를 넣을 엑셀을 불러옵니다.

 

데이터 추출을 해야합니다. 좌측 웹 자동화 > 웹 데이터 추출 > 웹 페이지에서 데이터 추출을 끌어와 이전과 다르게 데이터 저장 모드를 변수로 선택합니다.

이렇게 되면 이전처럼 추출된 데이터를 바로 엑셀에 저장하는 것이 아니라 데이터 테이블 형태의 변수로 가지게 되고 이를 통해 값을 넣으면서 원하는 작업을 할 수 있습니다.

 

이 상태에서 시가총액 웹 페이지로 이동합니다. 그러면 라이브 웹 도우미 창이 자동으로 뜨고 N의 1 부분에 커서를 가져가서 마우스 우측 버튼을 클릭, 요소 값 추출 > 텍스트:('1')을 선택합니다.

 

다음 종목명, 현재가를 위와 같은 방법으로 추출하면 자동으로 전체 행이 추출되어 표시됩니다.

참고로 Value#2에 또 삼성전자가 들어가는데 신경 안써도 됩니다. 나중에 다시 정렬됩니다.

 

그런데 가면히 보면 전일비의 상한가, 하한가 아이콘이 빠져 있습니다. 이 내용도 추가해야 합니다. 해당 아이콘으로 커서를 가져가 추출을 합니다.

그런데 상한가, 하한가는 아이콘 이미지이다 보니 텍스트 값은 없고 이미지 소소 URL 값만 있습니다. 나중에 이 값으로 뭔가를 판단해서 넣어야 합니다. 일단 해당 URL값으로 추가합니다.

 

이렇게 전일비의 아이콘을 추가하면 라이브 웹 도우미 창에 이상한 곳에 URL이 추가됩니다. 추가로 패턴을 집어 넣으면 다시 정상으로 돌아오니 다음 작업으로 넘어갑니다.

 

이제 N값 2를 추출합니다.

 

그러면 갑자기 화면이 바뀌면서 원하는 형태로 데이터가 정렬됩니다.

새로운 <tr>에 값을 추출하니 자동으로 <tr>반복으로 판단하여 해당 데이터를 다시 정렬하였습니다. 그리고 라이브 웹 도우미 화면을 보면 Value#4까지 데이터가 있고 웹 페이지 화면을 보면 4번째, 즉 전일비 아이콘까지 초록색 점선으로 Border처리 되어 있습니다. 이것으로 보면 초록색 Border 부분의 데이터가 추출된다라는 것을 알 수 있습니다.

 

그러면 전일비 숫자부터 ROE까지 차례로 요소 값을 추출합니다.

 

모두 추출하면 아래 그림과 같은 형태가 됩니다.

 

100위까지 추출해야하니 2페이지까지 가야합니다. 페이징을 위해 스크롤을 하단으로 내려 2번 페이지 링크를 우측 버튼으로 클릭 > 요소를 페이저로 설정을 클릭합니다.

"요소를 페이저로 설정"하여 처리할 "최대 웹 페이지 수"를 지정하는 것은 시가총액과 같은 pagination 형태가 아니라 예를 들면 NEXT 버튼을 클릭하여 다음 페이지로 이동하는 경우 NEXT 버튼을 "요소를 페이저로 설정" 에서 지정하고 "최대 웹 페이지 수"에서 몇 번 링크를 클릭할 지를 정의하는 내용입니다.

잘못된 부분 정정합니다.

 

다음 작업창으로 돌아와 처리할 최대 웹 페이지 수를 2로 입력하고 저장합니다.

 

이제 DataFromWebPage 변수에 시가총액 100위까지의 정보가 데이터 테이블 형태로 들어가 있는 상태입니다. (위 변수명과 다른건 이미지 캡쳐을 위해 새로 생성, 캡쳐해서 그렇습니다.)

상단 실행 버튼을 클릭하여 테스트 하고 작업창 우측 흐름 변수에서 DataFromWebPage 우측을 클릭 보기를 선택하여 어떤 데이터가 들어가 있는지 확인합니다.

 

그러면 DataFromWebPage를 반복문을 통해 행과 열 정보를 꺼내와 엑셀에 넣는 작업을 해야합니다.

우선 좌측 메뉴 변수 > 변수 설정을 끌어와 변수명은 row, 값은 2로 행에 대한 변수를 저장합니다. 초기값이 2인 이유는 데이터를 넣으려는 엑셀의 1행은 이미 타이틀이 있기 때문에 2행부터 시작해야 합니다.

 

다음은 좌측 메뉴 반복 > 각각의 경우를 끌어와 변수명을 rowValue로 수정하고 저장합니다.

 

좌측 메뉴 변수 > 변수 설정을 끌어와 변수명은 col, 값은 1로 열에 대한 변수를 저장합니다.

 

이제 열이 반복되면서 값을 넣어야 합니다. 좌측 메뉴 반복 > 각각의 경우를 끌어와 반복할 값은 rowValue, 변수명은 colValue로 저장합니다.

이전글에서 설명했듯이 데이터 테이블 형태는 2차원 배열로 생각하면 쉽습니다. 따라서 첫번째 반복문에서는 반복되는 행값을 가져오고, 두번째 반복문에서는 하나의 행에 있는 반복되는 열값을 가져오면 됩니다.

 

이제 colValue를 엑셀에 쓰면 됩니다. 그런데 전일비의 상한가, 하한가 아이콘 이미지 처리를 해야합니다. 엑셀에 URL 이미지 쓰는 방법을 찾아보니 매크로를 만들어서 써야한다는 내용이 있습니다. 여기서는 그냥 URL의 아이콘 명으로 구분하여 상승, 하락으로 표현하겠습니다. 이쁘게 만들고 싶으시다면 매크로를 이용해서 해당 URL을 표시하셔도 됩니다. 매크로 추가는 좌측 메뉴 Excel > 고급 > Excel 매크로 실행에서 처리할 수 있습니다. 아니면 URL 값을 넣고 작업 종료 후 파일을 열어 직접 매크로를 실행해도 될 듯 합니다.

좌측 메뉴 조건 > 만약을 끌어와 col값이 4일 경우로 저장합니다. 데이터 테이블을 보면 아이콘 URL이 4번 열에 있습니다.

 

다음으로 이미지 URL을 살펴보면 이미지 패스는 동일하고 아이콘 이름만 상한가는 ico_up.gif, 하한가는 ico_down.gif으로 되어 있습니다. 이를 이용하여 문자열을 검색, 매칭되는 값을 찾아 colValue값을 상승, 하락으로 변경하겠습니다.

우선 좌측 메뉴 텍스트 > 텍스트 구문 분석을 끌어와 분석할 텍스트는 colValue, 찾을 텍스트는 ico_up을 입력합니다. 다음으로 구문 분석 시작하기는 colVauel의 몇번째 index부터 검색할지 index값을 넣는 부분으로 처음부터 하려면 0, 특정 index부터 검색하려면 해당 index를 입력하면 됩니다. 찾는 문자의 index가 가까울 수록 속도는 빨라집니다. 다음 첫번째 항목만 옵션은 ico_up이 첫번째로 나오는 index값을 변수에 저장하게 되며 체크를 해제하면 일치하는 모든 index값을 변수에 저장합니다.

여기서는 만약 ico_up이 있으면 해당 문자열의 index값을 Position에 넣을 것이고 만약 일치하는 문자열이 없다면 -1값을 넣을 것입니다.

 

ico_down도 위와 동일하게 처리합니다.

 

이제 ico_up인지 판단해야 합니다. 좌측 메뉴 조건 > 만약을 끌어와 아래와 같이 값을 입력합니다. 아래 조건문의 내용은 ico_up값이 -1이 아니라면 (즉, ico_up값이 있다면)으로 해석하면 됩니다. 

 

ico_up이 있으니 colValue 변수를 상승으로 입력하고 저장합니다.

 

ico_down도 위와 동일하게 조건문과 변수 설정을 추가하여 저장합니다.

 

이제 열 값에 대한 처리가 끝났으므로 엑셀에 값을 쓰겠습니다. 좌측 메뉴 Excel > Excel 워크시트에 쓰기를 끌어와 쓸 변수명은 colValue, 열과 행은 col과 row를 입력하고 저장합니다.

 

다음으로 2번째 반복문인 col 처리 반복문이 끝나기 전 col 변수를 1 증가시켜줍니다. 좌측 메뉴 변수 > 변수 증가를 끌어와 아래와 같이 저장합니다.

 

다음 row를 처리하는 첫번째 반복문이 끝나기 전 row 변수를 1 증가시켜줍니다. 좌측 메뉴 변수 > 변수 증가를 끌어와 아래와 같이 저장합니다.

 

다음으로 엑셀을 저장하고 닫겠습니다. 좌측 메뉴 Excel > Excel 닫기를 끌어와 닫기 전 문서 저장을 선택하고 저장합니다. 현재 작업한 엑셀 파일은 기존에 만들어진 파일에 추가한 파일이라 단순 저장만 해도 됩니다.

 

여기까지가 데이터 추출해서 엑셀에 저장하는 작업입니다. 실제 실행해보면 한 10분 넘게 걸립니다. 100개의 행을 셀 하나하나씩 넣다보니 시간이 오래 걸립니다.

심플버전에서 했던 방식으로 테이블 형태로 한번에 넣어서 엑셀 파일을 편집하는게 좋은지, 아님 위와 같이 하는 것이 좋은지는 한번씩 해보고 판단하면 될 듯 합니다.

이제 생성된 파일을 메일로 보내 보겠습니다.

 

여기서는 SMTP(발신 메일 서버)를 Gmail을 이용합니다. 그러기 위해서는 먼저 아래와 같이 설정이 필요합니다. Gmail을 안쓴다면 아래 작업은 Skip하시기 바랍니다.

SMTP 서버를 Gmail을 통해 처리할 경우 App 비밀번호를 설정해야하며 App 비밀번호는 등록하는 기기에 한정하여 사용할 수 있습니다. 1. Google 2단계 인증 설정 크롬 브라우저 우측 상단 Gmail 계정 -> 계정관리 -> 보안 -> Google에 로그인 -> 2단계 인증 선택 -> 절차에 따라 2단계 인증 설정 2. Gmail App 비밀번호 설정 크롬 브라우저 우측 상단 Gmail 계정 -> 계정관리 -> 보안 -> Google에 로그인 -> 앱 비밀번호 선택 -> 절차에 따라 App 비밀번호 설정 -> 설정 후 문자 16자리 복사

 

먼저 이메일에 보내는 날짜를 넣고 싶습니다. 만약 반복된 작업이라면 날짜로 구분해야하기 때문입니다. 좌측 메뉴 날짜/시간 > 현재 날짜 및 시간 가져오기를 끌어와 아래와 같이 선택하고 저장합니다.

 

이제 이메일 정보 설정을 위해 좌측 메뉴 이메일 > 이메일 보내기를 끌어와 아래와 같이 입력합니다. 제목과 본문에 보면 위에서 정의한 현재날짜를 변수명으로 입력하였습니다. 또한 하단에 첨부파일을 추가하여 위에서 생성한 시가총액 엑셀파일을 첨부하면 됩니다.

 

모두 끝났습니다.

참... 참고로 실제 시가총액 데이터 추출해서 업무에 사용하신다면 위 추출작업은 주식시장 개장 후로 작업 시간을 설정해야 합니다. 개장 전에는 주식 관련된 항목이 전부 0으로 나옵니다.