엑셀 in 파이썬, 공식 업데이트! 기초-활용 20분 총정리

드디어, 엑셀에 파이썬이 정식으로 공개되었습니다! 다양한 데이터 분석 툴을 실무에 바로 적용해보세요!

# 데이터분석 # 파이썬 # 파워쿼리

작성자 :
오빠두엑셀
최종 수정일 : 2023. 09. 15. 06:31
URL 복사
메모 남기기 : (10)

엑셀 in 파이썬, 공식 업데이트! 기초-활용 20분 총정리

엑셀 in 파이썬 목차 바로가기
영상 강의

큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀고급] 엑셀 Python, 공식 업데이트! 기초부터 활용까지 20분 핵심 정리
    예제파일
  • [엑셀고급] 엑셀 Python, 공식 업데이트! 기초부터 활용까지 20분 핵심 정리
    완성파일

엑셀 Pyhon 기능 소개 (베타버전 기준, 장/단점)

2023년 8월 22일부터 M365 베타버전 사용자를 대상으로 엑셀 Python 기능이 순차적으로 배포가 시작되었습니다. 23년 9월 현재, 2310 버전 (16907 이상) 사용자는 엑셀에서 Python 기능을 바로 사용할 수 있습니다.

오빠두Tip : M365 버전별 엑셀 베타 버전 신청 방법은 아래 관련 게시글에 자세히 정리했습니다.

엑셀 Python 기능은 ① MS 클라우드에서 동작하며, Python 핵심 패키지인 ② 아나콘다를 기본으로 제공합니다. 따라서 별도의 Python 프로그램을 설치하지 않아도 누구나 손쉽게 활용할 수 있는데요. 그러나 사용할 수 있는 라이브러리는 ③ 아나콘다 패키지에서 제공하는 라이브러리로 제한된다는 단점도 있습니다.

엑셀 python 클라우드_R
엑셀 Python 기능은 MS 클라우드에서 동작합니다.
장점 :
① 별도의 프로그램을 설치하지 않아도 엑셀에서 Python을 편리하게 사용할 수 있음.
② MS에서 보안을 철저히 관리하므로, 외부의 해킹 위협으로부터 상대적으로 안전하게 사용할 수 있음.
③ 클라우드에서 동작하므로 PC 자원을 절약하고 안정적으로 Python 코드를 실행할 수 있음.
단점 :
① 사용할 수 있는 라이브러리가 'Anaconda'에서 제공하는 라이브러리로 제한됨.
② 클라우드에서 동작하므로 사용자 PC에서 파일을 저장하거나 메일을 보내는 등의 직접적인 업무 자동화는 구현 불가능.
③ 많은 양의 데이터(n만행 이상)로 차트 이미지를 출력할 경우, 처리속도가 느리지만 추후 개선될 것으로 보임.

엑셀 Python 기능 기본 사용법

엑셀 Python 기능 사용법은 매우 간단하며, 3가지 방법으로 Python 코드 편집기를 실행할 수 있습니다.

방법① : 셀 안에 "=py()"로 py 함수 입력

엑셀 python 입력
=py 함수를 입력하면 파이썬 편집기가 실행됩니다.

방법② : [수식] 탭 - [Python 삽입] 버튼 클릭

엑셀 python 삽입
수식탭에서 Python 편집기를 실행할 수 있습니다.

방법③ : 단축키 Ctrl + Alt + Shift + P 동시 입력

엑셀 python 실행 단축키
단축키 Ctrl + Alt + Shift + P 를 동시에 누르면 파이썬 편집기가 실행됩니다.

Python 편집기를 실행한 후, 코드를 작성하면 엑셀에서 Python 을 바로 사용할 수 있습니다. 편집기에서 Enter 키를 입력하면 줄바꿈이 추가되며, Ctrl + Enter 를 동시에 입력해서 작성한 Python 코드를 실행할 수 있습니다.

엑셀 python 실행하기
작성한 Python 코드는 Ctrl + Enter 로 실행합니다.
오빠두Tip : 엑셀 Python 기능을 사용할 때 알아두면 유용한 단축키는 실습파일에 자세히 정리했습니다.

엑셀 python 단축키 모음
엑셀 Python 관련 단축키는 예제파일을 확인하세요!

엑셀 Python 기본기 다지기

우선 가장 쉬운 예제로 엑셀에 작성된 범위를 Python 으로 출력하는 방법을 알아보겠습니다.

  1. 범위를 DataFrame으로 변환하기 : 예제파일을 실행한 후, [엑셀xPython] 시트로 이동합니다. "1. 파이썬 데이터 분석 기초" 아래의 I3셀을 선택한 후, =py() 를 입력해서 Python 편집기를 실행합니다.

    엑셀 파이썬 코드 입력하기
    I3셀을 선택한 후, Python 편집기를 실행합니다.
  2. Python 편집기에서 왼쪽에 데이터가 작성된 B2:G23 범위를 마우스로 드래그하면 아래 그림과 같이 코드가 자동으로 작성됩니다.
    xl("B2:G23", headers=True)
    #B2:G23 범위의 데이터를 DataFrame 개체로 생성
    엑셀 파이썬 범위 선택
    편집기를 실행 후 범위를 드래그하면 Python 코드가 작성됩니다.
    오빠두Tip : xl 함수는 Python 편집기에서 사용되는 함수로 '범위, 이름범위, 표, 쿼리'를 참조할 수 있습니다.

  3. Ctrl + Enter 로 Python 코드를 실행하면 셀안에 'DataFrame' 개체가 생성됩니다. 개체 왼쪽에 표시된 마름모 모양의 아이콘을 클릭하면 개체 안에 작성된 데이터를 미리볼 수 있습니다.
    엑셀 python dataframe
    개체 왼쪽의 아이콘을 클릭하며 개체 미리보기가 표시됩니다.
    오빠두Tip : DataFrame은 파이썬 Pandas 라이브러리에서 사용하는 2차원 배열로, 엑셀의 이름 범위와 유사한 개념입니다. 그러나 DataFrame을 효과적으로 활용하려면, 기본적인 코딩 공부가 필요하며, 이에 대한 상세 내용은 추후 Python 특강에서 다룰 예정입니다.
  4. Python 편집기 반환 형식 변경 : Python 편집기로 반환되는 값은 'Python 개체' 또는 'Excel 값' 형태로 출력할 수 있습니다.
    · Python 개체 : DataFrame, Image, Array.. 등의 개체를 출력합니다. 개체로 출력할 경우, Python 편집기에서 해당 셀을 바로 참조해서 활용할수 있습니다.
    · Excel 값 : 배열, 이미지를 엑셀 시트 위에 바로 출력합니다.
  5. 방금 전 Python 코드를 작성한 I3셀을 선택한 후, 수식입력줄 왼쪽의 마름모 모양 아이콘을 클릭하면 'Python 출력' 형식을 선택할 수 있습니다. 출력 방식으로 'Excel 값'을 선택하면 DataFrame의 데이터가 엑셀 범위로 출력됩니다.

    엑셀 python 개체 excel 값 출력
    수식입력줄 왼쪽의 마름모 아이콘을 클릭해서 Python 반환 형식을 변경할 수 있습니다.
  6. 또는 Python 코드를 작성한 셀을 선택한 후, 단축키 Ctrl + Alt + Shift + M 을 동시에 눌러서 파이썬 출력 방식을 빠르게 변환할 수 있습니다.

    엑셀 Python DataFrame 출력
    단축키 Ctrl + Alt + Shift + M 으로 Ptyhon 출력 방식을 빠르게 전환할 수 있습니다.
  7. DataFrame 특정 필드 선택 : 이제 DataFrame의 아래쪽 셀인 I4셀을 선택한 후, Python 편집기를 실행하고 아래와 같이 코드를 작성합니다. DataFrame 뒤에 마침표(.)를 찍고, 머릿글을 작성하면 DataFrame에서 특정 필드를 선택할 수 있습니다.
    xl("I3").이름
    #이름 필드 선택
  8. 작성한 Python 코드를 Ctrl + Enter로 실행하면 Series(1차원 배열)이 반환되며, 반환 형식을 Excel 값으로 변경하면 DataFrame에서 이름 범위만 선택된 것을 확인할 수 있습니다.

    엑셀 python DataFrame 필드 선택
    "DataFrame.필드명" 으로 특정 필드를 선택할 수 있습니다.
  9. 그러나 마침표(.)를 이용해 필드를 지정할 때, 필드 이름에 공백이 있으면 오류가 발생합니다. 따라서 공백이 있는 필드를 참조할 경우에는 아래와 대괄호를 사용하며, 이때 "마침표는 포함하지 않습니다."
    xl("I3")["사회 탐구"]
    #공백이 들어간 필드 선택

    python 공백 필드 선택 방법
    필드 이름에 공백이 있을 경우, DataFrame["필드명"] 으로 필드를 선택합니다.
    오빠두Tip : 대괄호 안에 필드를 참조할 때에는 큰따옴표(") 또는 작은따옴표(')를 모두 사용할 수 있지만, 시작과 종료 기호는 반드시 일치해야 합니다.
  10. DataFrame 여러 필드 선택 : 대괄호를 사용하면 여러 필드를 동시에 선택할 수 있습니다. DataFrame에서 여러 필드를 선택할 때에는 아래와 같이 코드를 작성하며, 작성한 순서대로 필드가 선택됩니다.
    xl("I3")[["사회 탐구","이름","성별"]]
    #여러 필드 동시 선택

    엑셀 python 여러 필드 선택
    DataFrame[["필드1","필드2",..]] 로 여러 필드를 동시에 선택할 수 있습니다.
  11. 변수로 개체 참조하기 : Python 코드를 작성 시, 개체가 반환된 셀을 직접 참조할 수도 있지만 변수를 사용하면 효율적으로 코드를 작성할 수 있습니다. I4셀의 Python 코드를 제거하고, I3셀의 Python 코드를 아래와 같이 수정합니다. 아래 코드를 작성하면, B2:G23 범위의 데이터를 포함한 DataFrame이 'test' 라는 이름의 변수로 할당됩니다.
    test = xl("B2:G23", headers=True)
    #DataFrame을 test라는 변수로 할당

    엑셀 python 변수 할당
    변수를 할당하면 Python 코드를 더욱 편리하게 작성할 수 있습니다.
  12. 이제 I4셀을 선택한 후, 아래와 같이 코드를 작성하면 test 의 '이름' 필드가 선택됩니다.
    test.이름
    #test라는 변수의 '이름' 필드 선택

    엑셀 python 개체 변수 참조
    변수를 사용하면 코드 작성이 더욱 편해집니다.
  13. 엑셀 Python의 계산 방향 : Python 코드를 여러 셀에 나누어 작성할 경우, 엑셀 Python의 계산 순서인 좌→우, 상→하를 고려해야합니다. I4셀에 작성한 코드를 잘라내기 후, H3셀로 옮기면 아래 그림과 같이 "test라는 이름의 변수를 찾을 수 없습니다."라는 오류가 발생하는데, 이는 I3셀에서 'test'라는 변수가 할당되기 이전에 H3셀에서 코드가 실행되기 때문입니다.

    엑셀 python 계산 순서
    엑셀 Python 코드를 여러 셀에 작성할 경우, 좌→우, 상→하 방향으로 계산되는 것을 주의합니다.
  14. 이제 엑셀 Python을 사용할 때 알아야 할 기본기를 모두 알아봤습니다. 지금부터 본격적으로 다양한 예제와 함께 엑셀 Python 코드를 실습해보겠습니다!

예제1. describe 함수 - 원클릭 기술통계

엑셀 Python을 활용하면 데이터 기술 통계를 함수 하나로 편리하게 구할 수 있습니다.

  1. 원클릭 기술통계 구하기 : 예제파일의 [엑셀xPython] 시트로 이동한 후, I3 셀에 아래 파이썬 코드를 작성하면 B2:G23 범위 데이터가 test 라는 이름의 변수로 할당됩니다.
    test = xl("B2:G23", headers=True)
    #B2:G23 범위의 데이터를 DataFrame 개체로 생성
  2. 이어서 아래 Python 코드를 작성 후 실행하면 B2:G23 범위에 작성된 데이터의 기술통계 값을 얻을 수 있습니다.
    test.describe()
    #test로 할당된 DataFrame의 기술통계 출력

    엑셀 파이썬 여러 필드 분석
    test 데이터의 기술통계 결과를 출력합니다.
  3. 기술통계는 기본적으로 숫자 데이터를 집계합니다. 만약 특정 필드의 기술통계를 구하거나, 문자 데이터를 포함한 전체 기술통계 값을 구하려면 아래와 같이 코드를 작성합니다.
    test[["이름", "성별"]].describe() #특정 필드의 기술통계를 구합니다.
    test.describe(include="all") #모든 필드의 기술통계를 구합니다.

    엑셀 python 기술통계 기초
    특정 필드를 선택하거나 모든 필드의 기술통계를 구할 수 있습니다.
  4. query 함수로 필터 적용하기 : query 함수를 이용하면 여러 개의 복잡한 조건을 문장으로 작성하여 데이터를 편리하게 필터링할 수 있습니다. 시트 M3셀에 아래와 같이 python 코드를 작성 후 실행하면, test 데이터에서 수학 점수가 90점 이상인 항목을 필터링합니다.
    test.query("수학 >= 90")
    #test 데이터에서 수학점수가 90점 이상인 항목을 필터링

    엑셀 파이썬 query 함수
    query 함수를 사용하면 복잡한 조건도 편리하게 필터링할 수 있습니다.
  5. 아래 그림과 같이 Python 코드의 조건을 엑셀 셀과 연동해서 실시간 필터링 분석 보고서를 만들 수 있습니다. Python 코드를 엑셀 셀과 연동하는 방법은 영상 강의를 참고하세요!

    엑셀 파이썬 Query 함수 자동화
    query 함수를 셀과 연동하여 실시간 필터링 보고서를 만들 수 있습니다.

예제2. pivot_table/plot 함수 - 자동화 피벗테이블/차트 만들기

Python 함수를 활용하면 값이 바뀌었을 때 실시간으로 구조가 업데이트 되는 자동화 피벗테이블을 만들 수 있습니다.

  1. DataFrame 만들기 : 예제파일에서 [피벗&차트분석] 시트로 이동합니다. I2셀에 아래 Python 코드를 작성 후 실행하여, 왼쪽 표에 작성된 직업별 연봉 표를 DataFrame 개체로 생성합니다.
    xl("표_3[#모두]", headers=True)
    #표_3의 데이터를 DataFrame으로 생성

    엑셀 python 피벗테이블 select
    왼쪽 표에 작성된 데이터를 DataFrame 개체로 변환합니다.
  2. 실시간으로 구조가 갱신되는 피벗테이블 : I6셀을 선택한 후, 아래 피벗테이블 코드를 실행하면 연도별 급여 평균이 집계된 피벗테이블이 작성됩니다.
    xl("I2").pivot_table(index="work_year",values="salary")
    #I2셀에 출력한 DataFrame 개체로 피벗테이블 만들기
    엑셀 python 피벗테이블 만들기
    DataFrame에서 기본 피벗테이블을 생성합니다.
    · index : 엑셀 피벗테이블에서 '행 영역'에 들어갈 필드입니다.
    · values : 값 영역에 들어갈 필드입니다.
    · columns : 열 영역에 들어갈 필드입니다.
    · aggfunc : 값의 집계방식을 정할 수 있습니다. 기본값은 'mean(평균)'입니다.


    aggfunc 목록 : count(개수), nunique(고유개수), min(최소), max(최대), first/last(처음,마지막), unique(고유값), std(표준편차), sum(합계), mean/median/mode(평균/중앙/최빈값), mad(평균절대편차)
    ※ pivot_table에 대한 자세한 설명은 pandas 공식 문서를 참고하세요.

  3. 아래 그림과 같이 pivot_table 함수의 인수를 엑셀 셀과 연동하면 값이 바뀌었을 때 행, 열, 집계방식 등의 구조가 실시간으로 갱신되는 피벗테이블을 만들 수 있습니다.
    pivot = xl("I2").pivot_table(index=xl("J5"),values="salary",aggfunc=xl("K5"),columns=xl("L5"))
    #index, value, aggfunc, columns 인수를 엑셀과 연동하여 구조가 실시간으로 바뀌는 피벗테이블 만들기

    엑셀 python 피벗테이블 자동화
    피벗테이블 인수를 엑셀과 연동하여 자동으로 갱신되는 피벗테이블을 만들 수 있습니다.
  4. 실시간으로 구조가 바뀌는 차트 : plot 함수를 사용하면 차트의 'x축 및 y축'을 포함하여 '차트 종류'가 실시간으로 갱신되는 자동화 차트를 만들 수 있습니다. O6셀에 아래 코드를 작성 후 실행하면 I6셀에 생성한 피벗테이블을 시각화한 차트가 생성됩니다.
    pivot.plot()
    #pivot 이라는 데이터를 차트로 시각화하기

    엑셀 python 차트 만들기
    피벗테이블을 차트로 시각화합니다.
  5. plot 함수의 인수를 변경하면 x축, y축, 차트 종류, 차트 제목 등을 변경할 수 있습니다. O6셀에 작성한 Python 코드를 아래와 같이 바꾼 후, P5셀의 목록을 line, bar, barh 로 변경하면 차트 종류가 실시간으로 변경됩니다.
    pivot.plot(kind=xl("P5"))
    #P5셀에 선택한 차트 종류로 실시간 갱신하기
    엑셀 python 차트 자동화 plot
    plot 함수의 인수를 엑셀과 연동하여 값이 바뀌면 자동으로 갱신되는 차트를 만들 수 있습니다.
    · kind : 차트 종류 (line, bar, barh, hist, box 등..)
    · x, y : x축, y축에 사용할 데이터
    · xlabel, ylabel : x축, y축에 표시할 축 레이블
    · title : 차트 제목
    · figsize : 차트 이미지 크기


    ※ plot에 대한 자세한 설명은 pandas 공식 문서를 참고하세요.

예제3. regex 라이브러리 - 비정제 데이터 가공하기

엑셀 Python에서 regex 라이브러리를 활용하면 정규표현식으로 불규칙한 데이터로부터 정제된 데이터를 편리하게 추출하고 가공할 수 있습니다.

오빠두Tip : 'regex' 라이브러리는 Anaconda 패키지에 기본으로 포함되어 있지만, 엑셀 Python 에서는 자동으로 로드되지 않습니다. 따라서, 'import re' 명령어를 작성해 별도로 불러온 후 사용합니다.
  1. 불규칙한 댓글에서 날짜 데이터 추출 : 우선 첫번째 예제로 쇼핑몰에 작성된 제품 리뷰 중, "2023년 1월 1일", "2023-01-01", "2023/01/01", "23년1월1일" 등 불규칙하게 작성된 날짜 데이터만 추출한 후 올바른 날짜로 가공하는 regex 코드를 작성하겠습니다. 예제파일을 실행한 후, [날짜 추출] 시트로 이동합니다.

    엑셀 python 댓글 분석
    불규칙하게 작성된 제품 후기에서 날짜 데이터를 추출합니다.
  2. D2셀을 선택한 후, 아래 Python 코드를 실행하면 제품 후기에서 정제된 날짜 데이터가 깔끔하게 정리됩니다.
    #Regex 패키지 추가
    import re
     
    #제품 후기 DataFrame 불러오기
    df = xl("표2[[#모두],[제품 후기]]", headers=True)
     
    #제품 후기에 Index 열 추가
    df["index"] = df.index
     
    #추출한 날짜를 저장할 범위 생성
    df_result = pd.DataFrame(index=df.index)
    df_result["추출한 날짜"] = None
    df_result["날짜"] = None
     
    #Regex 패턴
    pattern = r'(?:(\d{2,4})년\s(0?[1-9]|1[0-2])월\s(0?[1-9]|[12][0-9]|3[01])일)|(?:(\d{4}|[0-9]{2})[\/-](0?[1-9]|1[0-2])[\/-](0?[1-9]|[12][0-9]|3[01]))'
     
    #제품 후기를 하나씩 돌아가면서 날짜 추출
    for r in range(0, len(df)):
    matches = re.finditer(pattern, df.iat[r,0])
    corrected_dates = []
    for match in matches:
    original_date = match.group(0)
    date = match.groups()
    year, month, day = None, None, None
     
    #날짜가 년/월/일 패턴일 경우
    if date[0]:
    year, month, day = date[0], date[1], date[2]
    #날짜가 yyyy-mm-dd 패턴일 경우
    else:
    year, month, day = date[3], date[4], date[5]
    #년도가 2자리일 경우 앞에 20 추가
    if len(year) == 2:
    year = "20" + year
     
    #출력 범위에 기존 날짜 및 수정한 날짜 추가
    df_result.iat[r, 0] = original_date
    df_result.iat[r, 1] = f'{year}-{int(month):02d}-{int(day):02d}'
     
    #범위 출력
    df_result
    엑셀 python 날짜 데이터 추출
    제품 후기에서 날짜 데이터를 추출한 후, 올바른 날짜 형식으로 정리합니다.
    오빠두Tip : 날짜 추출 시트에는 데이터가 입력되면 자동으로 머릿글과 줄무늬 서식을 적용하는 조건부서식이 적용되어 있습니다. 조건부서식에 대한 자세한 설명은 아래 5분 기초 영상강의를 참고하세요!
  3. 불규칙한 주소 데이터 정제하기 : Regex를 사용하면 시/구/동, 시/군/면/읍 등으로 불규칙하게 작성된 주소 데이터를 시/구/동 단위로 정리할 수 있습니다. 예제파일에서 [주소 추출] 시트로 이동한 후, D2셀에 아래 코드를 실행하면 주소 데이터에서 시/구/동 단위별로 데이터가 깔끔하게 정리됩니다.
    #Regex 패키지 추가
    import re
     
    #주소 DataFrame 만들기
    df = xl("표3[[#모두],[주소]]", headers=True)
     
    #Regex 패턴
    pattern = "(?:(.+[도시]\s)(.+[시구군]\s)(.+?[읍면동])\s)"
    results = []
     
    #각 항목을 하나씩 돌아가며 시/구/동 추출
    for r in range(0, len(df)):
    match = re.search(pattern, df.iat[r,0])
    if match:
    도시, 시구군, 읍면동 = match.groups()
    results.append((도시, 시구군, 읍면동))
     
    #df_result 출력 결과에 추출한 필드를 추가
    df_result = pd.DataFrame(results, columns=["도시", "시구군", "읍면동"])
     
    #출력 결과 마지막 행에 원본 주소 필드 추가
    df_result.insert(3, '기존 주소', df[:len(results)])
     
    #결과 출력
    df_result

    python 주소 데이터 추출
    불규칙하게 작성된 주소 데이터를 시/구/동 단위로 정리합니다.
  4. 정규표현식을 쉽게 만드는 방법 : 정규표현식은 초보자에게는 다소 복잡해 보일 수 있습니다. 만약 정규표현식에 익숙하지 않다면, ChatGPT를 활용해서 코드를 편리하게 작성할 수 있습니다. 샘플 데이터로 입력값과 원하는 결과값 샘플을 ChatGPT에게 제공하고, 정규표현식을 활용한 Python 코드를 요청하면 훌륭하게 코드 작성을 도와줍니다. 그렇지만, 항상 완벽하게 동작할 것이라는 보장은 없으므로, 약간의 코딩 지식이 있으면 도움이 됩니다. 코딩 지식이 없더라도 걱정하지 마세요! 문제에 대한 설명과 해결 방법을 ChatGPT에게 요청하면, ChatGPT가 친절한 설명과 함께 코드 작성을 도와줍니다.
    엑셀 python ChatGPT 활용
    ChatGPT를 사용해서 정규표현식 및 Python 코드를 편리하게 작성할 수 있습니다.

    아래 입력값에서 결과값을 얻기 위한 정규표현식과 Python 코드를 작성해주세요.

    입력값 :
    서울시 강남구 대치동 123번지
    서울시 강동구 상일동 123번지 201동 501호
    등등.. 입력값 작성

    결과값 :
    서울시 강남구 대치동
    서울시 강동구 상일동
    등등.. 원하는 결과값 형태 작성

예제4. 실시간 주식 분석 차트 만들기

엑셀 파워쿼리와 Python을 활용하면 인터넷에서 실시간 주식 정보를 받아온 후, 주식 트렌드를 분석하는 것도 가능합니다.

오빠두Tip : 파워쿼리를 활용한 실시간 비트코인 정보 웹 크롤링 강의는 이전 영상 강의를 확인하세요!

예제파일에서 [주식분석] 시트의 L2셀에 아래 Python 코드를 작성한 후 실행하면 선택한 주식의 최근 10일간 주식 트렌드를 분석하는 차트가 작성됩니다.

엑셀 python 주식 분석
아래 Python 코드를 실행하면 주식 트렌드 차트가 작성됩니다.
#보조축 차트 생성을 위한 라이브러리
from matplotlib.ticker import MultipleLocator
 
#데이터프레임 인덱싱
df_stock = xl("일별시세[#모두]", headers=True)
df_stock = df_stock[["날짜","종가","거래량"]]
df_stock["날짜"] = pd.to_datetime(df_stock["날짜"])
indexed_df = df_stock.set_index(["날짜"])
 
#이동평균 예측 (단위 : 전체 기간 ÷ 10)
forecast_cnt = int(len(indexed_df.index)/10)
diff = indexed_df["종가"].iloc[-1] - indexed_df["종가"].iloc[-2]
future_dates = [indexed_df.index[-1] + pd.DateOffset(days=i) for i in range(1, forecast_cnt+1)]
future_values = [indexed_df["종가"].iloc[-1] + i*diff for i in range(1, forecast_cnt+1)]
future_거래량 = [np.nan]*forecast_cnt
df_future = pd.DataFrame({'종가': future_values, '거래량': future_거래량}, index=future_dates)
 
#기존 데이터에 예측치 추가
df_combined = pd.concat([indexed_df, df_future])
 
#5일, 10일 이평선 추가
df_combined['5_day_MA'] = df_combined['종가'].rolling(window=xl("U2")).mean()
df_combined['10_day_MA'] = df_combined['종가'].rolling(window=xl("U3")).mean()
 
#차트 X축 표시용 텍스트 변환
df_combined.index = pd.to_datetime(df_combined.index).strftime('%m/%d')
indexed_df.index = pd.to_datetime(indexed_df.index).strftime('%m/%d')
df_future.index = pd.to_datetime(df_future.index).strftime('%m/%d')
 
#차트 플롯 (주축)
fig, ax1 = plt.subplots()
x = df_combined.index
x_actual = indexed_df.index
x_forecast = df_future.index
ax1.plot(x_actual, indexed_df["종가"], color="red", zorder=1, label='Actual')
ax1.plot(x_forecast, df_future["종가"], color="blue", linestyle='--', zorder=2, label='Forecast')
ax1.plot(df_combined.index, df_combined['5_day_MA'], color="orange", linestyle='-', zorder=3, label='5-day MA', lw=2, alpha=0.7)
ax1.plot(df_combined.index, df_combined['10_day_MA'], color="green", linestyle='-', zorder=3, label='10-day MA', lw=2, alpha=0.7)
 
#차트 플롯(보조축)
ax2 = ax1.twinx()
ax2_max = df_combined["거래량"].max()
ax2.bar(x, df_combined["거래량"], color=(210/250,210/250,210/250), alpha=0.6, zorder=3)
ax2.set_ylim(0,ax2_max*4)
 
#차트 시각화
ax1.xaxis.set_major_locator(MultipleLocator(5))
for label in ax1.xaxis.get_ticklabels():
label.set_fontsize(8)
label.set_rotation(45)
#ax1.set_title("제목을 입력합니다.")
ax1.set_xlabel("Date")
ax1.set_ylabel("Closing Price")
ax2.set_ylabel("Trading Volume")
 
#차트 출력
plt.show(fig)

이제 B3셀에서 원하는 주식 종목을 선택하면, 쿼리가 실행되면서 선택한 종목의 최근 10일 주식 정보와 함께 차트가 갱신됩니다.

엑셀 파워쿼리 주식 정보 가져오기
주식 종목을 선택하면 최근 10일 정보를 받아와 분석 결과가 갱신됩니다.
오빠두Tip : 만약 주식 종목을 변경해도 쿼리가 갱신되지 않을 경우, 웹에서 데이터를 받아오기 위한 보안 설정을 확인합니다. [데이터] 탭 - [쿼리 및 연결] 을 클릭한 후, 쿼리 목록에서 '일별 시세'를 우클릭 - 편집으로 이동합니다. 파워쿼리 편집기가 실행되면 편집기 상단의 "개인 정보 보호에 대한 정보가 필요합니다" 에서 [계속] 버튼을 클릭한 후, 확인란에 체크 → 저장을 클릭하여 네이버 증권(https://finance.naver.com)에서 데이터를 받아올 수 있도록 설정을 변경하면 주식 정보를 실시간으로 갱신할 수 있습니다.

엑셀 파워쿼리 개인 정보 수준 설정
파워쿼리 갱신이 안될 경우, 웹 데이터 정보 수집 설정을 확인합니다.

5 8 투표
게시글평점
10 댓글
Inline Feedbacks
모든 댓글 보기
10
0
여러분의 생각을 댓글로 남겨주세요.x