업무자동화기초 파이썬을 활용한 금융 데이터 분석 기초 및 심화 과정 1 1

COM (Component Object Model) Platform-independent, distributed, object-oriented system for creating binary software components that can interact COM is the foundation technology for Microsoft’s OLE (compound documents), ActiveX (Internet-enabled component), as well as others Once It is compiled in accordance with standards, COM objects can be shared regardless of the programming language in use

COM으로 익스플로러 다루기 Windows Internet Explorer's modular architecture enables developers to reuse some of its components as well as extend and enhance the browser's functionality.  As Internet Explorer's component architecture is based on Component Object Model (COM), there are many different ways to extend its capabilities.

실습 import win32com.client 다음 파이썬 코드를 실행해 봅시다. (01_Automation/01.py) import win32com.client explore = win32com.client.Dispatch("InternetExplorer.Application") explore.Visible = True

실습 import win32com.client import time 다음 파이썬 코드를 실행해 봅시다. (01_Automation/02.py) import win32com.client import time explore = win32com.client.Dispatch("InternetExplorer.Application") while 1: explore.Visible = True time.sleep(1) explore.Visible = False

Navigate import win32com.client (01_Automation/03.py) Navigate https://msdn.microsoft.com/en-us/library/aa752084(v=vs.85).aspx import win32com.client explore = win32com.client.Dispatch("InternetExplorer.Application") explore.Visible = True explore.Navigate("http://www.naver.com")

COM으로 엑셀 다루기 import win32com.client (01_Automation/04.py) COM으로 엑셀 다루기 import win32com.client excel = win32com.client.Dispatch("Excel.Application") excel.Visible = True wb = excel.Workbooks.Add() ws = wb.Worksheets("Sheet1")

엑셀 Cell에 값 입력하기 import win32com.client (01_Automation/05.py) 엑셀 Cell에 값 입력하기 import win32com.client excel = win32com.client.Dispatch("Excel.Application") excel.Visible = True wb = excel.Workbooks.Add() ws = wb.Worksheets("Sheet1") ws.Cells(1,1).Value = "성함" ws.Cells(1,2).Value = "소속"

엑셀 Cell에 값 입력하기 import win32com.client (01_Automation/06.py) 엑셀 Cell에 값 입력하기 반복문을 사용하기 import win32com.client excel = win32com.client.Dispatch("Excel.Application") excel.Visible = True wb = excel.Workbooks.Add() ws = wb.Worksheets("Sheet1") for row in range(0, 5): for col in range(0, 2): ws.Cells(row+1, col+1).Value = 0.2

엑셀 파일 저장하기 SaveAs() 메서드, Quit() 메서드 (01_Automation/07.py) import win32com.client excel = win32com.client.Dispatch("Excel.Application") excel.Visible = True wb = excel.Workbooks.Add() ws = wb.Worksheets("Sheet1") for row in range(0, 5): for col in range(0, 2): ws.Cells(row+1, col+1).Value = 0.2 wb.SaveAs("C:\\Users\\Jason\\Desktop\\excel_com.xlsx") excel.Quit() 경로를 지정할 때 / 대신 \\를 사용해야 합니다.

엑셀 파일 읽기 import win32com.client (01_Automation/08.py) 엑셀 파일 읽기 import win32com.client excel = win32com.client.Dispatch("Excel.Application") excel.Visible = False wb = excel.Workbooks.Open("C:\\Users\\Jason\\Desktop\\excel_com.xlsx") ws = wb.ActiveSheet print(ws.Cells(1,1).Value) excel.Quit() Visible이 필요 없는 경우 False로 설정 가능

셀에 값이 있는 범위 확인하기 특정 excel sheet에 있는 값을 읽고자 할 때 cell의 최대 범위를 알아야 함 (01_Automation/09.py) 셀에 값이 있는 범위 확인하기 특정 excel sheet에 있는 값을 읽고자 할 때 cell의 최대 범위를 알아야 함 import win32com.client excel = win32com.client.Dispatch("Excel.Application") excel.Visible = False wb = excel.Workbooks.Open("C:\\Users\\Jason\\Desktop\\excel_com.xlsx") ws = wb.ActiveSheet last_col = ws.UsedRange.Columns.Count last_row = ws.UsedRange.Rows.Count print(last_col, last_row) excel.Quit()

(01_Automation/10.py) 이미지 넣기 https://msdn.microsoft.com/VBA/Excel-VBA/articles/shapes-addpicture-method-excel expression . AddPicture( Filename , LinkToFile , SaveWithDocument , Left , Top , Width , Height ) import win32com.client excel = win32com.client.Dispatch("Excel.Application") excel.Visible = True wb = excel.Workbooks.Add() ws = wb.Worksheets("Sheet1") range = ws.Range("C3") ws.Shapes.AddPicture("C:\\Users\\Jason\\Desktop\\ad.jpg", False, True, range.Left, range.Top, 100, 100) C3 셀부터 이미지 출력

이미지 스크래핑 및 삽입 네이버 금융에서 이미지를 다운로드한 후 엑셀에 삽입

이미지 스크래핑 및 삽입 (1/2) (01_Automation/11.py) import requests from PIL import Image from io import BytesIO import win32com.client r = requests.get("http://imgfinance.naver.net/chart/item/area/year/006800.png?sidcode=1498956220331") im = Image.open(BytesIO(r.content)) width, height = im.size im.save("c:\\Users\\Jason\\Desktop\\temp.png")

이미지 스크래핑 및 삽입 (2/2) (01_Automation/11.py) excel = win32com.client.Dispatch("Excel.Application") excel.Visible = True wb = excel.Workbooks.Add() ws = wb.Worksheets("Sheet1") range = ws.Range("C3") ws.Shapes.AddPicture("C:\\Users\\Jason\\Desktop\\temp.png", False, True, range.Left, range.Top, width, height)

연습 문제 문제-1 다음과 같이 종목의 배당 수익율을 가져온 후 엑셀에 저장하세요. (01_Automation/ex01.py) companies ={"000660": "SK하이닉스", "039490":"키움증권", "006800":"미래에셋대우"}

연습 문제 풀이 ws.Cells(1, 1).Value = "회사명" ws.Cells(1, 2).Value = "종목코드" ws.Range("A1:C1").Interior.ColorIndex = 35 row = 2 for k, v in companies.items(): code = k name = v dividend = get_dividend_earning_rate(code) ws.Cells(row, 1).Value = name ws.Cells(row, 2).Value = code ws.Cells(row, 3).Value = dividend row += 1