환율/시장지표 모니터링과
비즈니스 영향 분석
Open API로 환율을 받아 엑셀 Power Query로 자동 갱신하고, 우리 회사의 외화 노출에 곱해 영향액을 계산한 뒤, 누가 무엇을 확인해야 하는지 알려 주는 경보까지 설계합니다.
1API와 Open API 소개
1-1API란?
API(Application Programming Interface): 프로그램과 프로그램이 서로 기능이나 데이터를 주고받기 위한 약속된 통로(규칙).
데이터 요청 창구 예시
여기서 OpenDART나 한국수출입은행 등이 만들어 놓은 데이터 요청 창구 하나하나가 API입니다.
프로그램 기능 호출 예시
지도 시스템 전체를 직접 개발할 필요 없이, 지도 서비스가 제공하는 API를 호출해서 해당 기능을 가져와 사용합니다.
일반적인 API 요청 방법
- 서비스 제공자가 발급하는 API Key를 받아서, 일정한 규칙의 API 요청 구문에 추가
- 서비스 제공자가 정해 놓은 규칙에 맞춰 요청하면 각 요청에 매칭되는 결과(데이터 등)를 반환
- 일반적으로 호출 횟수 제한, 인증, 유료 요금제 등이 있을 수 있음
1-2Open API란?
Open API(공개 API): 일반적으로 외부 사용자가 사용할 수 있도록 공개된 API.
한국수출입은행이 환율 데이터를 Open API로 제공하는 예시
- 한국수출입은행 홈페이지에서 API Key 발급 신청이 가능하며, 특별한 사유가 없는 한 바로 발급
- API Key를 API 요청에 첨부하여 필요한 데이터를 요청할 수 있음
실제 API 요청 예시
https://oapi.koreaexim.go.kr기준 주소/site/program/financial/exchangeJSON경로?authkey=발급받은키&searchdate=20260928&data=AP01요청 변수| 요청 변수 | 값 | 설명 |
|---|---|---|
authkey | 발급받은 인증키 | 필수 |
searchdate | 20260928 (YYYYMMDD) | 조회일. 생략하면 오늘 |
data | AP01 | AP01 환율 / AP02 대출금리 / AP03 국제금리 |
실제 요청 구문
https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON?authkey=ABqpelJFfhPPo7xayg5VCKlz6ZDN3JTO&searchdate=20260928&data=AP01
실제 응답 결과 (한 줄로 길게 옵니다 — 가로로 스크롤)
[{"result":1,"cur_unit":"CNH","ttb":"199.71","tts":"203.74","deal_bas_r":"201.73","bkpr":"201","yy_efee_r":"0","ten_dd_efee_r":"0","kftc_bkpr":"201","kftc_deal_bas_r":"201.73","cur_nm":"위안화"},{"result":1,"cur_unit":"EUR","ttb":"1,522.65","tts":"1,553.42","deal_bas_r":"1,538.04","bkpr":"1,538","yy_efee_r":"0","ten_dd_efee_r":"0","kftc_bkpr":"1,538","kftc_deal_bas_r":"1,538.04","cur_nm":"유로"},{"result":1,"cur_unit":"GBP","ttb":"1,770.2","tts":"1,805.97","deal_bas_r":"1,788.09","bkpr":"1,788","yy_efee_r":"0","ten_dd_efee_r":"0","kftc_bkpr":"1,788","kftc_deal_bas_r":"1,788.09","cur_nm":"영국 파운드"},{"result":1,"cur_unit":"JPY(100)","ttb":"849.32","tts":"866.47","deal_bas_r":"857.9","bkpr":"857","yy_efee_r":"0","ten_dd_efee_r":"0","kftc_bkpr":"857","kftc_deal_bas_r":"857.9","cur_nm":"일본 옌"},{"result":1,"cur_unit":"USD","ttb":"1,338.48","tts":"1,365.52","deal_bas_r":"1,352","bkpr":"1,352","yy_efee_r":"0","ten_dd_efee_r":"0","kftc_bkpr":"1,352","kftc_deal_bas_r":"1,352","cur_nm":"미국 달러"}]
| 필드 | 뜻 |
|---|---|
cur_unit | 통화 코드. 엔화는 JPY(100) |
cur_nm | 통화명 |
deal_bas_r | 매매기준율 — 이번 실습에서 쓰는 값 |
ttb | 전신환 매입률(송금 받을 때) |
tts | 전신환 매도율(송금 보낼 때) |
bkpr | 장부가격 |
kftc_deal_bas_r | 서울외국환중개 매매기준율 |
yy_efee_r, ten_dd_efee_r | 년·10일 환가료율 |
result | 1 성공 / 2 DATA 코드 오류 / 3 인증 코드 오류 / 4 일일 제한 초과 |
1-3API 요청 방법
Key = ABqpelJFfhPPo7xayg5VCKlz6ZDN3JTO
① 브라우저 주소창
브라우저 주소창에 API 요청 구문을 그대로 입력합니다.
https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON?authkey=발급받은키&searchdate=20260928&data=AP01
② PowerShell
윈도우즈 PowerShell에서 API 요청. 여기서는 USD 한 줄만 추출합니다.
$key = "발급받은키"
$r = Invoke-RestMethod "https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON?authkey=$key&searchdate=20260928&data=AP01"
$r | Where-Object { $_.cur_unit -eq "USD" } | Select-Object cur_unit, deal_bas_r, ttb, tts
③ Python
import json, urllib.parse, urllib.request
KEY = "발급받은키"
params = urllib.parse.urlencode({"authkey": KEY, "searchdate": "20260928", "data": "AP01"})
url = f"https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON?{params}"
rows = json.load(urllib.request.urlopen(url, timeout=30))
usd = next(r for r in rows if r["cur_unit"] == "USD")
rate = float(usd["deal_bas_r"].replace(",", ""))
print("USD 매매기준율:", rate)
④ Excel Power Query
let
인증키 = "발급받은키", // 실제 파일에서는 설정 시트에서 읽는다
응답 = Json.Document(
Web.Contents(
"https://oapi.koreaexim.go.kr",
[
RelativePath = "site/program/financial/exchangeJSON",
Query = [authkey = 인증키, searchdate = "20260928", data = "AP01"]
]
)
),
달러 = List.Select(응답, each _[cur_unit] = "USD"),
결과 = if List.IsEmpty(달러)
then #table({"통화", "매매기준율"}, {})
else #table({"통화", "매매기준율"},
{{"USD", Number.FromText(Text.Remove(달러{0}[deal_bas_r], ","), "en-US")}})
in
결과
2Open API 연동 기초
2-1주요 API 제공 사이트
| 출처 | 활용 | Open API 주소 |
|---|---|---|
| 한국은행 ECOS | 금리·환율·경제통계 시계열 | ecos.bok.or.kr/api |
| 한국수출입은행 환율 API | 통화별 고시 환율 | koreaexim.go.kr/ir/HPHKIR019M01 |
| 전자공시시스템(DART) | 기업 공시 정보(사업보고서, 감사보고서 등) | opendart.fss.or.kr |
| EIA | 원유·석유제품 가격·재고 | eia.gov/opendata |
각각의 Open API 제공 웹페이지에 들어가면 API Key 발급을 요청할 수 있습니다.
2-2한국수출입은행 환율 API 연결 순서
- Open API Key 신청https://www.koreaexim.go.kr/ir/HPHKIR019M01
- 발급된 Key 확인
ABqpelJFfhPPo7xayg5VCKlz6ZDN3JTO - Open API 개발명세 확인
요청 URL
https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON요청 변수
변수명 타입 변수설명 값설명 authkeyString(필수) 인증키 OpenAPI 신청 시 발급된 인증키 searchdateString 검색요청날짜 예) 2025-01-01, 20250101, (Default) 현재일 dataString(필수) 검색요청API타입 AP01: 환율, AP02: 대출금리, AP03: 국제금리 요청 예시
https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON요청 URL?authkey=발급받은키authkey&searchdate=20260928searchdate&data=AP01data출력 결과
출력 결과 (USD 한 건, 주석 포함) JSON{ "result":1, # 조회 결과(1:성공, 2:data코드오류, 3:인증코드오류, 4:일일제한횟수 마감 "cur_unit":"USD", # 통화코드 "ttb":"1,338.48", # 전신환(송금) 받으실때 "tts":"1,365.52", # 전신환(송금) 보내실때 "deal_bas_r":"1,352", # 매매 기준율 "bkpr":"1,352", # 장부가격 "yy_efee_r":"0", # 년환가료율 "ten_dd_efee_r":"0", # 10일환가료율 "kftc_bkpr":"1,352", # 서울외국환중개 장부가격 "kftc_deal_bas_r":"1,352", # 서울외국환중개 매매기준율 "cur_nm":"미국 달러" # 국가/통화명 }
이용 시 유의사항
비영업일의 데이터, 혹은 영업 당일 11시 이전에 해당일의 데이터를 요청할 경우null 값이 반환됩니다.2-3메타데이터의 중요성
하나의 “환율” 데이터에도 다양한 정보가 숨어 있습니다.
3엑셀 Power Query 구성하기
3-1엑셀 Power Query 기능 설명
Power Query: 엑셀에서 데이터를 가져오고 정리하는 과정을 기록해 두었다가, 버튼 하나로 다시 실행해 주는 기능.
- 위치: 데이터 탭 → 데이터 가져오기(가져오기 및 변환)
- Windows Excel 2016 이상에 기본 내장. Mac용·웹용 Excel은 지원 범위가 다름
| 항목 | 복사-붙여넣기 | Power Query |
|---|---|---|
| 처음 한 번 | 빠름 | 설정이 조금 필요 |
| 다음 달 | 처음부터 다시 | 새로 고침 한 번 |
| 무엇을 했는지 | 남지 않음 | 단계별로 기록됨 |
| 실수 | 사람마다, 매번 다름 | 같은 순서로 반복 → 결과가 같음 |
매번 눈대중으로 요리하는 것
레시피를 적어 두고, 재료(데이터)만 새로 넣으면 같은 요리가 나오는 것
(1) 기본 흐름 : 가져오기 → 정리 → 내보내기 → 새로 고침
| 단계 | 하는 일 | 환율 실습에서는 |
|---|---|---|
| 가져오기 | 데이터가 있는 곳에 연결 | 한국수출입은행 API에 날짜별로 요청 |
| 정리 | 필요한 형태로 다듬기 | USD·JPY(100)·CNH·EUR만 남기기, "1,352" → 1352 숫자로 |
| 내보내기 | 엑셀 시트에 결과 표 만들기 | Raw_FX 시트에 로드 |
| 새로 고침 | 같은 과정을 다시 실행 | 기간만 바꾸고 [모두 새로 고침] |
가져올 수 있는 데이터
- 파일: 엑셀, CSV, 텍스트, PDF, 폴더(같은 형식 파일 여러 개를 한 번에)
- 웹: 웹페이지 표, API(JSON·XML)
- 데이터베이스: SQL Server, Oracle 등 (회사 권한 필요)
(2) Power Query 편집기 화면
편집기를 여는 길은 상황에 따라 세 가지입니다. 이 모듈에서는 M 코드를 붙여넣어야 하므로 ①을 주로 씁니다.
| 상황 | 메뉴 경로 |
|---|---|
| ① 빈 쿼리에서 시작 (M 코드 붙여넣기) | 데이터 → 데이터 가져오기 → 기타 원본에서 → 빈 쿼리 |
| ② 파일에서 데이터 가져오기 | 데이터 → 데이터 가져오기 → 파일에서 → 파일 선택 → 탐색 창에서 [데이터 변환] |
| ③ 이미 만든 쿼리 고치기 | 데이터 → 쿼리 및 연결 → 오른쪽 목록에서 쿼리 이름 더블클릭 |
①로 빈 쿼리를 만들면 아래처럼 비어 있는 편집기가 열립니다. 아직 데이터가 없어서 가운데는 비어 있고, 적용된 단계에는 원본 하나만 있습니다.
- 오른쪽 쿼리 설정 → 이름의 ‘쿼리1’을 알아보기 쉬운 이름(예:
q환율)으로 바꿉니다. - 리본의 [보기] → 고급 편집기를 누릅니다. ([홈] 탭에도 있습니다)
- 열린 창의 내용을 모두 지우고 ChatGPT가 준 M 코드(3-2, 3-3)를 붙여넣은 뒤 [완료]를 누릅니다.
코드를 붙여넣고 [완료]를 누르면 아래처럼 미리 보기와 적용된 단계가 나타납니다. (3-3에서 만들 qry_Raw_FX를 실행한 화면)
고급 편집기에 넣은 코드의 GetSetting, AuthKey, … Sorted 같은 이름이 그대로 적용된 단계가 됩니다. 단계를 하나씩 눌러 보면 그 시점의 데이터를 확인할 수 있고, 아래 상태 표시줄에서 열·행 수(5개 열, 80개 행)를 볼 수 있습니다.
(3) 적용된 단계 = M 코드
화면에서 클릭으로 한 작업은 모두 M 코드라는 언어로 자동 기록됩니다. 반대로, M 코드를 붙여넣으면 그 내용이 적용된 단계로 보입니다.
원본 = ...,적용된 단계 1필터 = ...,적용된 단계 2 (앞 단계 이름을 받아서 다음 작업)정렬 = ...적용된 단계 3정렬최종 결과로 내보낼 단계let아래에 단계를 쓰고,in아래에 최종 결과 단계를 씀- 각 단계는 바로 앞 단계의 결과를 받아서 다음 작업을 함. 엑셀 셀에서 앞 셀을 참조하는 것과 같은 구조
(4) 내보내기(로드) 방법
편집기에서 홈 → [닫기 및 로드] 또는 [닫기 및 다음으로 로드…]
| 로드 방법 | 언제 쓰나 |
|---|---|
| 표 | 결과를 시트에서 보고 수식으로 계산할 때 (Raw_FX) |
| 연결만 만들기 | 시트에 펼치지 않고, 다른 쿼리가 가져다 쓰는 중간 단계로만 둘 때 |
| 피벗 테이블 보고서 | 시트에 펼치지 않고 바로 요약할 때 |
(5) 새로 고침
- 데이터 → [모두 새로 고침]: 파일 안의 모든 쿼리를 다시 실행
- 쿼리 및 연결 창 → 쿼리 오른쪽 클릭 → [새로 고침]: 그 쿼리만 다시 실행
- 쿼리 속성에서 설정 가능한 것
- 파일을 열 때 새로 고침
- 다음 간격으로 새로 고침(○분마다): 모니터링 시트를 켜 두면 일정 간격으로 자동 갱신
- 백그라운드에서 새로 고침: 끄면 오류가 바로 보임 (실습에서는 끄는 것을 권장)
결과 표의 숫자를 손으로 고치면?
- 새로 고침하면 원래 데이터로 다시 덮어씀. 손으로 고친 값은 사라짐
- → 결과 표(Raw_FX)는 고치지 않음. 계산은 다른 시트에서 결과 표를 참조해서 함
3-21차 — ChatGPT로 간단한 쿼리 만들기
인증키·날짜·통화를 코드 안에 직접 적는 가장 단순한 쿼리부터 만듭니다. ChatGPT에게 요청하고, 받은 코드를 붙여넣고, 결과를 확인하는 한 바퀴를 먼저 돌아 봅니다.
요청할 때 넣을 것
- API 정보: 요청 URL, 요청 변수(무엇을 어떤 형식으로 보내는지)
- 사용할 도구: 엑셀 Power Query(M 코드)
- 원하는 결과: 날짜, 통화, 가져올 값(
deal_bas_r매매기준율) - 인증키는 보내지 않기: “발급받은키”라고 적고, 코드를 받은 뒤 엑셀에서 바꿔 넣음
- ChatGPT에 요청하기
1차 요청 — 하루치 환율 가져오기
엑셀 Power Query(M 코드)로 한국수출입은행 환율 API에서 데이터를 가져오려고 해. - 요청 URL: https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON - 요청 변수: authkey=인증키, searchdate=조회일(yyyyMMdd), data=AP01(환율) - 인증키: 발급받은키 ← 실제 키 대신 이 글자 그대로 둘 것 - 조회일: 20260930 - 통화: USD, JPY(100), CNH, EUR - 가져올 값: 통화코드(cur_unit), 통화명(cur_nm), 매매기준율(deal_bas_r) 매매기준율은 "1,358.4"처럼 쉼표가 들어간 글자로 오니 숫자로 바꿔 줘. 고급 편집기에 그대로 붙여 넣을 수 있는 전체 코드를 짧게 작성해 줘.
조회일과 통화만 바꾸면 그대로 다시 쓸 수 있습니다. 조회일은 평일로 넣습니다. 주말·공휴일은 데이터가 없어 표가 비어 나옵니다.
ChatGPT 답변 예시 코드 보기 펼치기 / 접기
API를 한 번 호출해 JSON 목록을 표로 바꾸고, 네 통화만 남긴 뒤 매매기준율의 쉼표를 지워 숫자로 바꾸는 코드입니다. 답변은 매번 조금씩 다르지만 흐름은 같습니다.
M 코드 — 하루치, 4개 통화 Mlet // 설정값 — 코드 안에 직접 적음 인증키 = "발급받은키", 조회일 = "20260930", 통화목록 = {"USD", "JPY(100)", "CNH", "EUR"}, // API 호출 → JSON 받기 응답 = Json.Document( Web.Contents( "https://oapi.koreaexim.go.kr", [ RelativePath = "site/program/financial/exchangeJSON", Query = [authkey = 인증키, searchdate = 조회일, data = "AP01"] ] ) ), // JSON 목록 → 표, 대상 통화만 남기기 표 = Table.FromRecords(응답), 대상통화 = Table.SelectRows(표, each List.Contains(통화목록, [cur_unit])), 필요열 = Table.SelectColumns(대상통화, {"cur_unit", "cur_nm", "deal_bas_r"}), // "1,358.4" → 1358.4 숫자로 숫자변환 = Table.TransformColumns( 필요열, {{"deal_bas_r", each Number.FromText(Text.Remove(_, ","), "en-US"), type number}} ) in 숫자변환 - 코드 붙여넣고 인증키 바꾸기
데이터→데이터 가져오기→기타 원본에서→빈 쿼리→고급 편집기→코드 붙여넣기
붙여넣은 코드의
인증키 = "발급받은키"를 실습용 키로 바꾼 뒤 [완료]를 누릅니다.실습용 키:ABqpelJFfhPPo7xayg5VCKlz6ZDN3JTO - 처음 연결 허용하기
처음 실행하면 편집기 위쪽에 자격 증명을 묻는 노란 띠가 나옵니다. [자격 증명 편집] → 익명 → [연결]을 고릅니다. 이 API는 인증키를 주소에 붙여 보내므로 따로 로그인할 필요가 없습니다.
- 결과 확인하고 시트로 로드
미리 보기에 네 통화가 한 줄씩 나오면 성공입니다. 오른쪽 적용된 단계에 코드의 이름(
인증키…숫자변환)이 그대로 쌓여 있는지도 확인합니다.cur_unit cur_nm deal_bas_r CNH 위안화 202.51 EUR 유로 1,540.29 JPY(100) 일본 옌 863.16 USD 미국 달러 1,358.40 2026-09-30 매매기준율. 마지막으로 [홈] → 닫기 및 로드를 누르면 새 시트에 표로 들어갑니다.
다음 날 환율을 보려면?
- 고급 편집기를 열고
조회일을 고쳐야 함. 기간을 늘리거나 통화를 바꿀 때도 마찬가지 - → 자주 바뀌는 값(키·기간·통화)은 시트에 두고, 쿼리가 읽어 가게 하는 편이 낫다 (3-3)
실습이 잘 안 됐다면 — 1차 완성 파일
3-32차 — Settings 시트를 읽는 쿼리 만들기
이번에는 인증키·기간·통화를 Settings 시트에 두고, 쿼리는 그 값을 읽어 날짜별로 API를 호출하게 만듭니다. 기간을 바꿀 때는 셀만 고치고 [모두 새로 고침]을 누르면 됩니다.
키 · 기간 · 통화→Power Query
qry_Raw_FX날짜별 호출 · 정리→Raw_FX 시트
결과 표
- 실습 파일 둘러보기
시트 서식과 머리글은 미리 만들어 두었습니다. 이 모듈에서는 수식과 쿼리만 채웁니다.
시트 지금 상태 채우는 곳 Settings 인증키 · 기간 · 경보 기준 · 시나리오 충격 · 대상 통화. 값을 바꾸는 곳은 여기뿐 3장 Raw_FX 비어 있음 — 이번 쿼리 결과가 들어올 자리 3-3 FX_Wide · Exposure · Scenario 머리글만 있는 틀 4장 Alert 머리글만 있는 틀 5장 Settings 셀 항목 값 B5 인증키 실습용 키 B6 시작일 2026-09-01 B7 종료일 2026-09-30 A20:B24 대상 통화 표 API코드 / 통화명 — USD, JPY(100), CNH, EUR - 이름 확인하기
Power Query는
B5같은 셀 주소를 직접 읽지 못합니다. 대신 이름이 붙은 셀과 표를 찾아서 읽습니다. 실습 파일에는 이름을 미리 붙여 두었습니다.대상 이름 쓰는 곳 B5 cfgAuthKey쿼리 B6 · B7 cfgStartDate·cfgEndDate쿼리 대상 통화 표 tblCurrencies쿼리 B10 ~ B13 · B16 · B17 cfgAmbercfgRedcfgRedImpactcfgN·cfgModeratecfgStress4·5장 수식 [수식] → 이름 관리자에서 확인할 수 있습니다. 새 파일에서 직접 만들 때는 셀을 선택하고 수식 입력줄 왼쪽 이름 상자에 이름을 입력한 뒤 Enter, 표는 [테이블 디자인] → 표 이름에서 바꿉니다.
- 개인 정보 수준 검사 미리 끄기
이 쿼리는 엑셀 안의 값(인증키·기간)을 외부 웹 API로 보냅니다. 엑셀은 이것을 “파일 안의 데이터가 밖으로 나갈 수 있는 결합”으로 보고 막는데, 그때 나오는 것이
Formula.Firewall오류입니다. 쿼리를 실행하기 전에 꺼 두면 이 오류를 만나지 않습니다.데이터→데이터 가져오기→쿼리 옵션→현재 통합 문서 · 개인 정보→개인 정보 수준 무시 → 확인이 설정은 지금 열린 파일에만 적용됩니다. 공개 API와 실습용 설정값만 쓰는 이 파일에서만 끄고, 사내 데이터가 든 파일에서는 끄지 않습니다. - ChatGPT에 요청하기
이름과 표가 이미 있다는 점, 그리고 그 이름을 읽으라는 점을 분명히 적는 것이 핵심입니다. [ ] 안 내용만 바꾸면 다른 API나 시트에도 쓸 수 있습니다.
2차 요청 — Settings 연동 쿼리[요청] 엑셀 Settings 시트의 설정값으로 한국수출입은행 환율 API를 날짜별로 호출하고, 결과를 하나의 표로 돌려주는 Power Query(M 코드) 전체를 작성해 줘. [API] - 요청 URL: https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON - 요청 변수: authkey(인증키), searchdate(yyyyMMdd), data=AP01(환율) 고정 - 응답 예시: [{"result":1,"cur_unit":"USD","cur_nm":"미국 달러","deal_bas_r":"1,352"}, ...] 숫자는 쉼표가 들어간 글자로 오고, 주말·공휴일에는 빈 목록이 옴 [Settings 시트] 이름과 표 이름은 이미 만들어 둠 - cfgAuthKey: 인증키 (텍스트) - cfgStartDate, cfgEndDate: 시작일, 종료일 (날짜) - tblCurrencies: 대상 통화 표, 열은 API코드 / 통화명 (USD, JPY(100), CNH, EUR) → 셀 주소 대신 Excel.CurrentWorkbook()으로 이름과 표를 읽을 것 [처리 조건] - 시작일~종료일을 하루씩 호출해서 결과를 합칠 것 - tblCurrencies에 있는 통화만 남기고, deal_bas_r(매매기준율)을 숫자로 바꿀 것 - 주말·공휴일(빈 응답)은 오류 없이 건너뛰고, result가 1이 아니면 오류로 표시할 것 - 인증키·기간·통화를 코드에 직접 쓰지 말 것 [출력] - 열 순서: 조회일(날짜) | API코드 | 통화명(tblCurrencies 기준) | 환율항목 | 환율(숫자) - 조회일 → API코드 순으로 정렬 [답변 형식] 고급 편집기에 그대로 붙여 넣을 전체 코드 + 단계별 짧은 설명ChatGPT 답변 예시 단계 설명과 코드 보기 펼치기 / 접기
Settings의 이름과 표를 읽어 날짜 목록을 만들고, 하루씩 API를 호출해 대상 통화만 숫자로 정리한 뒤 하나로 합치는 구조입니다.
단계(코드 이름) 하는 일 GetSetting,AuthKey,StartDate,EndDateSettings의 이름 붙은 셀 읽기 Validate인증키가 비었는지, 시작일이 종료일보다 늦지 않은지 검사 CurrencyTabletblCurrencies표 읽기DateList시작일~종료일 날짜 목록 만들기 GetDailyFX하루치 호출 → 오류 검사 → 대상 통화만 → 숫자로 변환 Combined날짜별 결과를 하나로 합치기 Merged,ExpandedtblCurrencies의 통화명 붙이기 Reordered→Sorted열 순서 맞추기, 중복 제거, 정렬 M 코드 — qry_Raw_FX (실습 파일에서 실행 확인) Mlet // ========================================== // 1. Settings 시트의 설정값 읽기 // ========================================== GetSetting = (settingName as text) as any => Excel.CurrentWorkbook(){[Name=settingName]}[Content]{0}[Column1], AuthKey = Text.Trim(Text.From(GetSetting("cfgAuthKey"))), StartDate = Date.From(GetSetting("cfgStartDate")), EndDate = Date.From(GetSetting("cfgEndDate")), Metric = "deal_bas_r", // 가져올 값: 매매기준율 // ========================================== // 2. 입력값 유효성 검사 // ========================================== AllowedMetrics = { "ttb", "tts", "deal_bas_r", "bkpr", "yy_efee_r", "ten_dd_efee_r", "kftc_bkpr", "kftc_deal_bas_r" }, Validate = if AuthKey = "" then error "Settings!B5 인증키가 비어 있습니다." else if StartDate > EndDate then error "시작일이 종료일보다 늦습니다." else if not List.Contains(AllowedMetrics, Metric) then error "지원하지 않는 환율 항목: " & Metric else true, // ========================================== // 3. 대상 통화 목록 읽기 // ========================================== CurrencySource = Excel.CurrentWorkbook(){[Name="tblCurrencies"]}[Content], CurrencyTable = Table.TransformColumns( Table.SelectColumns( CurrencySource, {"API코드", "통화명"} ), { {"API코드", each Text.Trim(Text.From(_)), type text}, {"통화명", each Text.Trim(Text.From(_)), type text} } ), CurrencyList = List.Buffer(CurrencyTable[API코드]), // ========================================== // 4. 검색 날짜 목록 생성 // ========================================== DateList = if Validate then List.Dates( StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0) ) else {}, // ========================================== // 5. 날짜별 한국수출입은행 API 호출 함수 // ========================================== GetDailyFX = (SearchDate as date) as table => let DateText = Date.ToText( SearchDate, "yyyyMMdd", "en-US" ), Response = Json.Document( Web.Contents( "https://oapi.koreaexim.go.kr", [ RelativePath = "site/program/financial/exchangeJSON", Query = [ authkey = AuthKey, searchdate = DateText, data = "AP01" ], Timeout = #duration(0, 0, 0, 30) ] ) ), // 비영업일에는 빈 목록 또는 null 가능 ResponseList = if Response = null then {} else if Value.Is(Response, type list) then Response else error "API 응답 형식 오류: " & DateText, // 인증 오류, 호출 한도 오류 등 검사 ErrorRows = List.Select( ResponseList, each Record.FieldOrDefault(_, "result", 1) <> 1 ), CheckedRows = if List.Count(ErrorRows) > 0 then error Error.Record( "KoreaExim API Error", "API 오류 발생: " & DateText, ErrorRows ) else ResponseList, // 지정 통화만 추출 FilteredRows = List.Select( CheckedRows, each List.Contains( CurrencyList, Record.FieldOrDefault(_, "cur_unit", null) ) ), // 필요한 필드만 테이블로 변환 DailyTable = Table.FromRecords( FilteredRows, {"cur_unit", Metric}, MissingField.UseNull ), RenameColumns = Table.RenameColumns( DailyTable, { {"cur_unit", "API코드"}, {Metric, "환율"} } ), // 1,352.00 같은 문자열을 숫자로 변환 ConvertRate = Table.TransformColumns( RenameColumns, { { "환율", each if _ = null then null else Number.FromText( Text.Replace( Text.From(_), ",", "" ), "en-US" ), type nullable number } } ), AddDate = Table.AddColumn( ConvertRate, "조회일", each SearchDate, type date ) in AddDate, // ========================================== // 6. 날짜별 API 호출 및 결과 병합 // ========================================== DailyResults = List.Transform( DateList, each GetDailyFX(_) ), EmptyTable = #table( type table [ API코드 = text, 환율 = nullable number, 조회일 = date ], {} ), Combined = if List.Count(DailyResults) = 0 then EmptyTable else Table.Combine(DailyResults), // ========================================== // 7. Settings의 통화명 매핑 // ========================================== Merged = Table.NestedJoin( Combined, {"API코드"}, CurrencyTable, {"API코드"}, "CurrencyInfo", JoinKind.LeftOuter ), Expanded = Table.ExpandTableColumn( Merged, "CurrencyInfo", {"통화명"}, {"통화명"} ), // ========================================== // 8. 추출 환율 항목 표시 // ========================================== AddMetric = Table.AddColumn( Expanded, "환율항목", each Metric, type text ), // ========================================== // 9. 컬럼 정렬 및 중복 제거 // ========================================== Reordered = Table.ReorderColumns( AddMetric, { "조회일", "API코드", "통화명", "환율항목", "환율" } ), DistinctRows = Table.Distinct( Reordered, {"조회일", "API코드"} ), // ========================================== // 10. 날짜 및 통화 순서로 정렬 // ========================================== Sorted = Table.Sort( DistinctRows, { {"조회일", Order.Ascending}, {"API코드", Order.Ascending} } ) in SortedChatGPT 답변은 매번 조금씩 다릅니다. 받은 코드가 실행되지 않으면 위 코드를 그대로 붙여넣어도 됩니다.
- 코드 붙여넣고 실행하기
빈 쿼리→이름을 qry_Raw_FX로→고급 편집기→코드 붙여넣기→[완료]
자격 증명 띠가 나오면 3-2와 같이 익명 → [연결]을 고릅니다. 미리 보기에 날짜·통화별 환율이 나오면 성공입니다.
- Raw_FX 시트로 로드
[홈] → 닫기 및 다음으로 로드… → 테이블 · 기존 워크시트를 고르고 Raw_FX 시트의 A1 칸을 클릭(
=Raw_FX!$A$1) → 확인.조회일 API코드 통화명 환율항목 환율 2026-09-01 CNH 위안화 deal_bas_r 204.54 2026-09-01 EUR 유로 deal_bas_r 1,595.23 2026-09-01 JPY(100) 일본 엔 deal_bas_r 859.49 2026-09-01 USD 미국 달러 deal_bas_r 1,373.60 2026-09-02 CNH 위안화 deal_bas_r 204.08 … … … … … 모두 80행(20영업일 × 4통화)입니다. 주말과 9/24~9/28 추석 연휴는 결과에 없는 것이 정상입니다. 로드된 표의 이름은 쿼리 이름과 같은
qry_Raw_FX가 되고, 4·5장 수식이 이 이름을 씁니다. - 설정만 바꿔서 다시 불러오기
- B7 종료일을
2026-09-15로 바꾸고 데이터 → 모두 새로 고침 → 44행(11영업일 × 4통화)으로 줄어듦 - 코드는 한 줄도 고치지 않았다는 점을 확인
- 확인했으면 B7을 다시
2026-09-30으로 바꾸고 새로 고침 (4·5장은 9월 전체를 씀)
- B7 종료일을
막혔을 때 — 자주 만나는 메시지 펼치기 / 접기
| 메시지 / 증상 | 원인 | 해결 |
|---|---|---|
Formula.Firewall | 개인 정보 수준 검사가 켜져 있음 | 3단계 설정 후 [미리 보기 새로 고침] |
| 자격 증명 노란 띠 | 이 PC에서 처음 연결 | 익명 → [연결] |
키가 테이블의 행과 일치하지 않음 (Expression.Error) | 이름·표 이름 오타, 또는 이름을 안 붙임 | [수식] → 이름 관리자에서 철자 확인 |
API 오류 발생 | 인증키 오류 또는 하루 호출 한도 초과 | B5 키 확인, 기간을 줄여 다시 실행 |
| 표가 비어 있음 | 기간이 주말·공휴일뿐이거나 오늘 날짜(공시 전) | 시작일·종료일을 평일이 들어가게 조정 |
날짜마다 API를 한 번씩 부르므로 기간이 길수록 호출 수가 늘어납니다. 수강생이 같은 키를 쓰면 하루 한도(1,000회)에 걸릴 수 있으니 실습 기간은 짧게 둡니다.
| 항목 | 1차 (3-2) | 2차 (3-3) |
|---|---|---|
| 인증키·기간·통화 | 코드 안에 직접 | Settings 시트 |
| 기간을 바꾸려면 | 고급 편집기에서 코드 수정 | 셀 수정 → 모두 새로 고침 |
| ChatGPT 요청 | 짧게, 값을 직접 적음 | 이름·표 구조와 처리 조건을 적음 |
| 맞는 용도 | API가 되는지 빠르게 확인 | 매일·매달 반복하는 모니터링 |
실습이 잘 안 됐다면 — 3장 완성 파일
- 파일을 열고 위쪽에 노란 띠가 나오면 [편집 사용], [콘텐츠 사용]을 차례로 누릅니다.
- 새로 고침을 하다 자격 증명 띠나
Formula.Firewall오류가 나오면 3-3의 3단계(개인 정보 수준 무시)와 5단계(익명 연결)를 똑같이 적용합니다.
4외화 순노출액과 영향 계산
4-1실습 준비 — 3장 파일에 이어서
3-3에서 쓴 파일을 그대로 이어서 씁니다. Raw_FX에 9월 데이터 80행이 들어 있으면 준비 완료입니다. 3장을 끝내지 못했다면 3장 끝의 완성 파일을 내려받아 시작합니다.
3장 결과→FX_Wide
날짜 × 통화→Exposure
9월 영향→Scenario
앞으로의 영향→Alert
경보 · 알림 (5장)
| 시트 | 미리 준비된 것 | 이번에 채울 것 |
|---|---|---|
| FX_Wide | 머리글(날짜·통화), 영업일 수(B2) | A4:E23 날짜·환율 (4-2) · G4:J23 변화율 (5-7) |
| Exposure | 통화(A5:A8), 머리글, 합계(I9) | 노출 입력, B~J열 수식 (4-5) |
| Scenario | 통화, 충격 비율(3행), 혼합 충격(H열), 합계(I9) | B~F열, I열 수식 (4-6) |
| Alert | 통화, 확인 담당, 머리글 | 기준일·상태·변화율·등급·문장·백테스트 (5장) |
cfgAmber, cfgN …)으로 참조합니다. 기준을 바꿀 때 Settings 값만 고치면 됩니다.4-2FX_Wide 시트 — 날짜 × 통화 가로표
Raw_FX는 날짜·통화마다 한 줄인 세로형입니다. 영향을 계산하려면 “9/1의 USD”, “9/30의 USD”처럼 날짜와 통화로 값을 바로 찾을 수 있는 가로표가 편합니다. B3:E3의 통화 머리글과 B2의 영업일 수(fxDays)는 미리 넣어 두었습니다.
- 날짜 채우기 (A열)
FX_Wide A4 — 첫 날짜 Excel
=MIN(qry_Raw_FX[조회일])
FX_Wide A5 — 다음 날짜 (A23까지 복사) Excel=IF(A4="","",IFERROR(SMALL(qry_Raw_FX[조회일],COUNTIF(qry_Raw_FX[조회일],"<="&A4)+1),""))
수식 풀어 읽기
IF(A4="","", …): 위 칸이 비어 있으면 이 칸도 비워 둠COUNTIF(qry_Raw_FX[조회일],"<="&A4): Raw_FX에서 위 날짜 이하인 행이 몇 개인지 셈 (9/1이면 4개 — 통화 4개)SMALL(qry_Raw_FX[조회일], 개수+1): 그다음 순서의 날짜 = 위 날짜 바로 다음 영업일IFERROR(…,""): 더 이상 날짜가 없으면 빈칸
- 환율 채우기 (B~E열)
FX_Wide B4 — 그 날짜·통화의 환율 (B4:E23으로 복사) Excel
=IF($A4="","",IFERROR(AVERAGEIFS(qry_Raw_FX[환율],qry_Raw_FX[조회일],$A4,qry_Raw_FX[API코드],B$3),""))
수식 풀어 읽기
AVERAGEIFS(환율, 조회일, $A4, API코드, B$3): 날짜가 A열과 같고 통화가 3행 머리글과 같은 행의 환율. 한 건뿐이라 평균 = 그 값$A4는 열 고정,B$3은 행 고정 → 오른쪽·아래로 복사해도 날짜는 A열, 통화는 3행을 봄- 날짜가 비었거나 값이 없으면 빈칸
- 확인
B2가 20, A23이 2026-09-30, B23(USD)이 1,358.40이면 성공입니다.
9월 20영업일에 맞춰 23행까지 만들었습니다. 기간을 늘리면 A·B~E열 수식을 더 아래로 복사하고, B2와 Exposure의
$23범위도 함께 늘립니다.
ChatGPT로 받는다면 요청문 보기 펼치기 / 접기
엑셀에서 세로형 표를 날짜 × 통화 가로표로 바꾸는 수식을 만들어 줘. [원본] Raw_FX 시트의 표 이름 qry_Raw_FX - 열: 조회일(날짜), API코드(USD, JPY(100), CNH, EUR), 통화명, 환율항목, 환율(숫자) - 날짜·통화마다 한 줄, 날짜 오름차순 [만들 표] FX_Wide 시트 - B3:E3에 USD, JPY(100), CNH, EUR가 적혀 있음 - A4부터 아래로 조회일을 중복 없이 오름차순으로 (23행까지 채우고, 날짜가 없으면 빈칸) - B4:E23에는 그 날짜·통화의 환율 (없으면 빈칸) [조건] Excel 2016에서 동작하는 함수만 사용(UNIQUE, FILTER 사용 금지), 표의 열 이름으로 참조 A4, A5, B4에 넣을 수식과 어디까지 복사하면 되는지 알려 줘.
받은 수식이 위와 달라도 3단계 확인값이 같으면 됩니다.
4-3순노출액
환율이 움직일 때 회사가 얼마나 영향을 받는지는 외화를 받을 돈과 줄 돈의 차이로 결정됩니다.
| 통화 | 예상 유입 | 예상 유출 | 순노출액 | 원화 약세(환율 상승) 시 |
|---|---|---|---|---|
| USD | 5,000,000 USD | 8,000,000 USD | −3,000,000 USD | 불리 |
| JPY | 200,000,000 JPY | 80,000,000 JPY | +120,000,000 JPY | 유리 |
| CNH | 12,000,000 CNH | 17,000,000 CNH | −5,000,000 CNH | 불리 |
| EUR | 0 | 0 | 0 | 영향 없음 |
- 달러를 300만 달러 더 줘야 하는 회사에게 달러 강세는 같은 달러를 사는 데 원화가 더 든다는 뜻. 그래서 불리
- 엔화를 1.2억 엔 더 받는 회사에게 엔화 강세는 받은 엔화를 원화로 바꾸면 더 많아진다는 뜻. 그래서 유리
- EUR은 노출이 없음. 환율이 움직여도 영향이 0이어야 함 (검산용으로 남겨 둠)
참고) 실무의 노출은 더 복잡함
- 외화 자산·부채, 확정 계약, 예상 거래, 파생상품(헤지), 결제 시점, 회계처리가 추가됨
- 여기서 계산하는 것은 헤지와 가격 전가를 빼고 본 1차 민감도
4-4환율 영향 산식
일반 통화
100엔 기준 JPY
왜 JPY만 100으로 나누나?
- 한국수출입은행 API는 엔화를
JPY(100), 즉 100엔당 원화로 줌 (2026-09-30 기준 863.16원 = 100엔) - 1.2억 엔에 863.16원을 그대로 곱하면 100배 큰 숫자가 나옴
- → Exposure 시트의
표기단위열이 API코드에(100)이 있으면 100, 없으면 1을 자동으로 넣음
=IF(ISNUMBER(SEARCH("(100)",A5)),100,1)| 환율 | 순노출 | 영향 |
|---|---|---|
| 상승 ↑ | 양수 (수취 초과) | + 유리 |
| 상승 ↑ | 음수 (지급 초과) | − 불리 |
4-5Exposure 시트 — 9월 한 달 동안 얼마나 영향을 받았나
“월초(9/1) 대비 최신일(9/30)” 기준으로 영향액을 계산합니다. 월초 환율과 최신 환율은 FX_Wide에서 가져옵니다.
- 노출 입력 (C·D열)
A 통화 (이미 입력됨) C 예상 유입 (클릭하면 복사) D 예상 유출 (클릭하면 복사) USD 50000008000000JPY(100) 20000000080000000CNH 1200000017000000EUR 004-3의 E사 가상 노출입니다. 칸의 숫자를 클릭하면 복사됩니다 — 엑셀에서 그 칸(C5~D8)을 한 번 클릭하고 Ctrl+V. 쉼표는 서식이 붙여 줍니다. → 엑셀에서 C5 한 칸만 선택하고 Ctrl+V 하면 C5:D8이 한 번에 채워집니다.
- 수식 넣기 (5행 → 8행까지 복사)
표의 수식은 클릭하면 복사됩니다. 5행에 붙여넣은 뒤 채우기 핸들(칸 오른쪽 아래 점)을 끌어 아래 행으로 복사합니다.
셀 수식 (클릭하면 복사) 뜻 B5 표기단위 =IF(ISNUMBER(SEARCH("(100)",A5)),100,1)JPY(100)이면 100, 나머지는 1 E5 순노출액 =C5-D5유입 − 유출 F5 월초 환율 =INDEX(FX_Wide!$B$4:$E$23,1,MATCH(A5,FX_Wide!$B$3:$E$3,0))FX_Wide 첫 행(9/1)에서 그 통화 값 G5 최신 환율 =INDEX(FX_Wide!$B$4:$E$23,fxDays,MATCH(A5,FX_Wide!$B$3:$E$3,0))FX_Wide의 fxDays번째 행(9/30)에서 그 통화 값 H5 환율 변화 =G5-F5최신 − 월초 I5 1차 영향액 =E5/B5*H5순노출 ÷ 표기단위 × 환율 변화 J5 방향 =IF(I5>0,"유리",IF(I5<0,"불리","영향 없음"))+면 유리, −면 불리 F·G열 풀어 읽기
MATCH(A5, FX_Wide!$B$3:$E$3, 0): A5의 통화가 FX_Wide 머리글에서 몇 번째 열인지 찾음 (USD=1, JPY(100)=2 …)INDEX(FX_Wide!$B$4:$E$23, 행, 열): 그 열에서 지정한 행의 값을 꺼냄- 월초는 행 =
1(첫 영업일), 최신은 행 =fxDays(영업일 수 = 마지막 영업일)
ChatGPT로 받는다면 I열 수식 요청문 보기 펼치기 / 접기
수식을 외울 필요는 없습니다. 계산 규칙을 말로 설명하고 수식을 받은 뒤, 검산은 직접 합니다.
ChatGPT 요청 — 1차 영향액 수식엑셀 Exposure 시트에서 외화 환율 영향액을 계산하려고 해. 열 구성 (5~8행에 통화별로 한 줄) - A열: API코드 (USD, JPY(100), CNH, EUR) - B열: 표기단위 (JPY(100)은 100, 나머지는 1) - E열: 순노출액(외화) = 유입 - 유출. 음수면 지급 초과 - F열: 월초 환율, G열: 최신 환율 (원화, 매매기준율) I열에 1차 영향액(원)을 구하는 수식을 5행 기준으로 만들어 줘. - JPY는 100엔당 환율이라는 점을 반영할 것 - Excel 2016에서도 동작하는 함수만 사용 수식과 함께, 아래 두 경우에 결과가 양수인지 음수인지 설명해 줘. 1) 순노출 -3,000,000 USD, 환율 1,350 → 1,417.5 2) 순노출 +120,000,000 JPY, 환율 900 → 945
받은 수식이 위 표의
=E5/B5*H5와 모양이 달라도, 3단계 결과 표와 같은 값이 나오면 됩니다. ChatGPT가 설명한 부호(양수·음수)는 4단계 방향성 검산에서 직접 확인합니다. - 결과 확인
E사 9월 결과 한국수출입은행 매매기준율
통화 순노출액 9/1 9/30 환율변화 1차 영향액 방향 USD −3,000,000 1,373.60 1,358.40 −15.20 +45,600,000원 유리 JPY(100) +120,000,000 859.49 863.16 +3.67 +4,404,000원 유리 CNH −5,000,000 204.54 202.51 −2.03 +10,150,000원 유리 EUR 0 1,595.23 1,540.29 −54.94 0원 영향 없음 합계 +60,154,000원 유리 - 방향성 검산
수식을 믿기 전에 방향이 맞는지 먼저 시험합니다.
- EUR은 순노출이 0 → 환율이 3% 넘게 움직였어도 영향액 0
- USD 예상 유입을 잠시 8000000으로 바꿔 순노출을 0으로 → 영향액 0 (확인 후 되돌리기)
- USD 환율이 떨어졌고 지급 초과 → 영향액은 +(유리). 반대로 나오면 E열의 유입−유출 순서를 확인
- JPY 영향액이 4억 원대로 크게 나오면 → 표기단위(100)가 빠진 것
방향이 하나라도 다르면 부호(유입−유출 순서) 또는 표기단위부터 다시 확인합니다.
주요 포인트
- USD줄 달러가 더 많은데, 달러 환율이 15.2원 떨어짐 → 같은 달러를 더 싸게 살 수 있음 → 유리.
- JPY받을 엔화가 더 많은데, 엔화 환율이 올라감 → 받은 엔화의 원화 가치가 커짐 → 유리.
- CNH줄 위안화가 더 많은데, 위안화 환율이 떨어짐 → 유리.
- EUR환율은 3.4%로 가장 크게 움직였지만 노출이 0 → 영향 0. 환율이 많이 움직인 통화가 꼭 중요한 통화는 아님.
9월은 세 통화 모두 유리하게 움직였음. 좋은 소식?
- 1차 영향일 뿐. 헤지 계약, 실제 결제일, 판매가격 조정은 반영하지 않았음
- 같은 노출을 그대로 들고 있으면, 환율이 반대로 움직일 때 같은 크기로 불리해짐
- → “지금까지 얼마”보다 “앞으로 움직이면 얼마”를 같이 봐야 함 (4-6)
4-6Scenario 시트 — 앞으로 움직이면 얼마인가
최신 환율(9/30)에서 일정 비율만큼 움직인다고 가정하고 영향을 계산합니다. 3행의 충격 비율(=cfgModerate 등)과 H열의 혼합 충격은 미리 넣어 두었습니다.
- 수식 넣기
표의 수식은 클릭하면 복사됩니다. 5행에 붙여넣은 뒤 채우기 핸들(칸 오른쪽 아래 점)을 끌어 아래 행으로 복사합니다.
셀 수식 (클릭하면 복사) 뜻 B5 최신 환율 (→ B8) =Exposure!G5Exposure의 최신 환율 C5 시나리오 영향 (→ C5:F8) =Exposure!$E5/Exposure!$B5*$B5*C$3순노출 ÷ 표기단위 × 최신 환율 × 3행 충격 I5 혼합 영향액 (→ I8) =Exposure!$E5/Exposure!$B5*$B5*H5같은 계산을 H열의 통화별 충격으로 C5 풀어 읽기
Exposure!$E5,Exposure!$B5: 순노출액과 표기단위.$로 열을 고정해 오른쪽으로 복사해도 E·B열을 봄$B5: 이 시트 B열의 최신 환율C$3: 3행의 충격 비율. 행만 고정해서 D·E·F열로 복사하면 −3%, +5%, −5%로 바뀜
| 통화 | Moderate 상승(+3%) | Moderate 하락(−3%) | Stress 상승(+5%) | Stress 하락(−5%) |
|---|---|---|---|---|
| USD | −122,256,000 | +122,256,000 | −203,760,000 | +203,760,000 |
| JPY | +31,073,760 | −31,073,760 | +51,789,600 | −51,789,600 |
| CNH | −30,376,500 | +30,376,500 | −50,627,500 | +50,627,500 |
| EUR | 0 | 0 | 0 | 0 |
| 합계 | −121,558,740 | +121,558,740 | −202,597,900 | +202,597,900 |
Stress 상승(+5%) 시 통화별 1차 영향액
단위: 백만원 · 막대에 마우스를 올리면 정확한 값이 보입니다
- 네 통화가 모두 같은 방향으로 움직이는 가정. 원화가 전체적으로 5% 약세가 되면 약 2억 원 불리
- 가장 큰 위험은 USD. 노출 300만 달러 × 환율 1,358원 × 5% ≈ 2억 원
혼합 시나리오 — 통화마다 다르게 움직이는 경우
| 통화 | 충격 | 1차 영향액 |
|---|---|---|
| USD | +5% | −203,760,000 |
| JPY | +5% | +51,789,600 |
| CNH | −5% | +50,627,500 |
| 합계 | −101,342,900 |
- USD 손실 2억 원 중 절반가량을 JPY·CNH가 메워 줌
참고) 민감도와 시나리오
- 민감도: 한 가지만 움직여 봄 (USD만 +5%)
- 시나리오: 여러 가지가 함께 움직이는 하나의 이야기 (원화 약세 + 위안화 약세)
- 같은 환율 영향을 원재료비 계산에 한 번 더 넣지 않도록 주의 (중복 반영)
실습이 잘 안 됐다면 — 4장 완성 파일
5경보 설계
5-1경보의 세 층
경보는 한 종류가 아닙니다. 무엇이 잘못됐는지에 따라 세 층으로 나눕니다.
| 층 | 무엇을 보나 | 예 | 받는 사람 |
|---|---|---|---|
| ① 데이터 경보 | 데이터 자체가 믿을 만한가 | 값 누락, 0 이하, 중복, 오래된 데이터 | 데이터 담당자 |
| ② 시장 경보 | 환율이 기준 이상 움직였나 | 5영업일 동안 3% 이상 변동 | 모니터링 담당 |
| ③ 비즈니스 경보 | 우리 회사 영향이 큰가 | 1차 영향액 1억 원 이상 | 자금·구매·영업 담당 |
| 상태 | 조건 | 조치 |
|---|---|---|
| MISSING | 해당 통화 데이터가 한 건도 없음 | 영향 계산 중단 |
| INVALID | 환율이 0 이하 | 데이터 오류 후보 |
| CHECK | 영업일 수보다 데이터가 적음 (빠진 날이 있음) | 원출처 교차 확인 |
| OK | 위에 해당 없음 | 계산 진행 |
참고) 실무에서 더 보는 것
- 중복: 같은 날짜·통화가 두 번 들어옴
- 오래됨: 최신일이
TODAY()보다 며칠 넘게 지나 갱신이 멈춤 - 급변: 전일 대비 20% 이상 튐 → 입력 오류일 가능성
5-2나쁜 경보와 좋은 경보
“EUR 3% 하락!”
AMBER 2026-09-30 기준 EUR 매매기준율 1,540.29원, 5영업일 전 대비 −3.0%. 순노출 0 EUR, 월초 대비 1차 영향 0원. 헤지·결제시점 미반영. 확인 담당: 자금팀 / 출처: 한국수출입은행 매매기준율
좋은 경보에 들어가는 것
- 등급얼마나 급한가
- 기준일과 값언제, 무엇이, 얼마나
- 우리 노출과 영향액그래서 우리 회사에 얼마인가
- 가정무엇을 빼고 계산했나
- 담당과 출처누가 확인하고, 숫자는 어디서 왔나
5-3네 종류의 임계값
| 유형 | 예 | 장점 | 한계 |
|---|---|---|---|
| 절대 수준 | USD 1,400원 초과 | 이해가 쉬움 | 기준이 바뀌면 다시 정해야 함 |
| 변화율 | 5영업일 ±3% | 급격한 변화를 잡음 | 노출과 무관하게 울림 |
| 변동성 | 과거 움직임 범위 이탈 | 평소와 비교 가능 | 설명이 어려움 |
| 영향액 | 1차 영향 1억 원 이상 | 업무 중요성과 바로 연결 | 노출 데이터가 정확해야 함 |
5-4Alert 시트 — 경보 등급 만들기
통화(A열)와 확인 담당(I열)은 미리 넣어 두었습니다. 위에서부터 차례로 채웁니다.
- 기준일과 비교일 (2행)
셀 수식 (클릭하면 복사) 뜻 B2 기준일 =INDEX(FX_Wide!$A$4:$A$63,fxDays)FX_Wide의 마지막 영업일 (9/30) D2 비교일 =INDEX(FX_Wide!$A$4:$A$63,fxDays-cfgN)그보다 cfgN(5)영업일 전 (9/21) - 데이터 상태 (B5 → B8)
Alert B5 Excel
=IF(COUNTIF(qry_Raw_FX[API코드],A5)=0,"MISSING",IF(COUNTIFS(qry_Raw_FX[API코드],A5,qry_Raw_FX[환율],"<=0")>0,"INVALID",IF(COUNTIF(qry_Raw_FX[API코드],A5)<fxDays,"CHECK","OK")))
수식 풀어 읽기
COUNTIF(qry_Raw_FX[API코드],A5)=0→ Raw_FX에 이 통화가 한 줄도 없으면MISSINGCOUNTIFS(…API코드,A5, …환율,"<=0")>0→ 환율이 0 이하인 행이 있으면INVALIDCOUNTIF(…)<fxDays→ 행 수가 영업일 수보다 적으면(빠진 날)CHECK- 셋 다 아니면
OK. 위에서부터 먼저 걸리는 상태가 나옴
- 변화율과 영향액 (C~G열, 5행 → 8행)
표의 수식은 클릭하면 복사됩니다. 5행에 붙여넣은 뒤 채우기 핸들(칸 오른쪽 아래 점)을 끌어 아래 행으로 복사합니다.
셀 수식 (클릭하면 복사) 뜻 C5 최신 환율 =Exposure!G5Exposure의 최신 환율 D5 N영업일 전 환율 =INDEX(FX_Wide!$B$4:$E$63,fxDays-cfgN,MATCH(A5,FX_Wide!$B$3:$E$3,0))FX_Wide에서 (fxDays − cfgN)번째 행 = 9/21의 그 통화 값 E5 N영업일 변화율 =C5/D5-1최신 ÷ 5영업일 전 − 1 (백분율) F5 순노출액 =Exposure!E5Exposure에서 가져옴 G5 1차 영향액 =Exposure!I5Exposure에서 가져옴 - 등급 (H5 → H8)
판정 순서 위에서부터 먼저 걸리는 것
순서 조건 등급 1 데이터 상태가 OK 아님 DATA_CHECK 2 |영향액| ≥ Red 영향액(1억) RED 3 |N영업일 변화율| ≥ Red 변화율(5%) RED 4 |N영업일 변화율| ≥ Amber 변화율(3%) AMBER 5 그 외 GREEN Alert H5 Excel=IF(B5<>"OK","DATA_CHECK",IF(ABS(G5)>=cfgRedImpact,"RED",IF(ABS(E5)>=cfgRed,"RED",IF(ABS(E5)>=cfgAmber,"AMBER","GREEN"))))
수식 풀어 읽기
B5<>"OK"→ 데이터가 믿을 만하지 않으면 다른 판정 없이DATA_CHECKABS(G5)>=cfgRedImpact→ 영향액 크기가 1억 이상이면RED(손실·이익 모두)ABS(E5)>=cfgRed→ 변화율 크기가 5% 이상이면REDABS(E5)>=cfgAmber→ 3% 이상이면AMBER, 아니면GREENABS는 부호를 떼고 크기만 봄. 오르든 내리든 많이 움직이면 경보
| 통화 | 데이터 상태 | 5영업일 변화율 (9/21 → 9/30) | 영향액(월초 대비) | 등급 |
|---|---|---|---|---|
| USD | OK | −1.8% | +45,600,000 | GREEN |
| JPY(100) | OK | −2.0% | +4,404,000 | GREEN |
| CNH | OK | −2.1% | +10,150,000 | GREEN |
| EUR | OK | −3.0% | 0 | AMBER |
EUR은 노출이 0인데 AMBER가 뜸. 이 경보는 필요한가?
- 변화율 기준만 쓰면 우리와 상관없는 통화에도 경보가 울림
- 반대로 USD는 영향액이 가장 크지만 변화율이 작아서 GREEN
- → 그래서 변화율과 영향액 기준을 함께 써야 함. 노출 0인 통화는 “참고용”으로 따로 표시하는 방법도 있음
해 보기
- Settings의 Amber 변화율을 3% → 1.5%로 바꿔 봄 → 네 통화 모두 AMBER
- Red 영향액을 1억 → 4천만 원으로 바꿔 봄 → USD가 RED
→ 기준값 하나로 경보 개수가 크게 달라짐. 기준은 감으로 정하지 않고 백테스트(5-7)로 확인합니다.
5-5알림 미리보기 문장
수업에서는 메일·메신저를 실제로 보내지 않고, Alert 시트에 보낼 문장을 수식으로 만들어 봅니다.
문장 틀
순노출 ○○ 통화, 월초 대비 1차 영향 ±○○원. 헤지·결제시점 미반영.
확인 담당: ○○ / 출처: 한국수출입은행 매매기준율
수식 (Alert J5 → J8까지 복사)
=IF(H5="GREEN","(경보 없음)",IF(H5="DATA_CHECK","[DATA_CHECK] "&A5&" 데이터 상태 확인 필요. 확인 담당: "&I5,"["&H5&"] "&TEXT($B$2,"yyyy-mm-dd")&" 기준 "&A5&" 매매기준율 "&TEXT(C5,"#,##0.00")&"원, "&cfgN&"영업일 전 대비 "&TEXT(E5,"+0.0%;-0.0%;0.0%")&". 순노출 "&TEXT(F5,"#,##0")&" "&A5&", 월초 대비 1차 영향 "&TEXT(G5,"+#,##0;-#,##0;0")&"원. "&"헤지·결제시점 미반영. 확인 담당: "&I5&" / 출처: 한국수출입은행 매매기준율"))
수식 풀어 읽기
IF(H5="GREEN","(경보 없음)", …): 경보가 아니면 짧게 “(경보 없음)”IF(H5="DATA_CHECK","[DATA_CHECK] …", …): 데이터 문제면 확인 요청 문장만- 그 외(AMBER·RED)는 문장 틀의 빈칸을 셀 값으로 채움:
"["&H5&"] "등급,TEXT($B$2,"yyyy-mm-dd")기준일,TEXT(C5,"#,##0.00")환율 TEXT(E5,"+0.0%;-0.0%;0.0%"): 변화율에 +/− 부호를 붙여 소수 한 자리 %로TEXT(G5,"+#,##0;-#,##0;0"): 영향액에 부호와 천 단위 쉼표
TEXT(값, "형식")&IFChatGPT로 받는다면 요청문 보기 펼치기 / 접기
엑셀 Alert 시트에서 경보 알림 문장을 수식으로 만들고 싶어. 열(5행부터 통화별 한 줄): A 통화, C 최신 환율, E N영업일 변화율, F 순노출액, G 1차 영향액(원), H 등급, I 확인 담당 기준일: $B$2 / 이름 정의: cfgN (비교 영업일 수) 문장 틀: [등급] 날짜 기준 통화 매매기준율 ○○원, N영업일 전 대비 ±○.○%. 순노출 ○○ 통화, 월초 대비 1차 영향 ±○○원. 헤지·결제시점 미반영. 확인 담당: ○○ / 출처: 한국수출입은행 매매기준율 조건: - H가 GREEN이면 "(경보 없음)" - H가 DATA_CHECK면 "[DATA_CHECK] 통화 데이터 상태 확인 필요. 확인 담당: ○○" - 날짜는 yyyy-mm-dd, 환율은 소수 둘째 자리, 금액은 천 단위 쉼표와 +/- 부호 - Excel 2016에서 동작하는 함수만 사용 J5에 넣을 수식 하나로 만들어 줘.
EUR 줄에 5-2의 “좋은 경보” 문장이 그대로 나오고, 나머지 세 통화는 “(경보 없음)”이면 성공입니다.
5-6알림 피로를 줄이는 규칙
경보가 너무 자주 울리면 아무도 보지 않게 됩니다. (양치기 소년)
운영 상태 흐름
5-7백테스트 — 이 기준이면 9월에 몇 번 울렸나
지금 정한 기준을 과거 데이터에 적용해서, 경보가 몇 번 울렸을지 세어 봅니다.
- 날짜마다 N영업일 변화율 (FX_Wide G4 → G4:J23)
FX_Wide 오른쪽의 BackTest 영역(G~J열, 머리글 준비됨)에 넣습니다.
FX_Wide G4 Excel=IF(OR($A4="",ROW()-ROW($A$4)<cfgN),"",B4/OFFSET(B4,-cfgN,0)-1)
수식 풀어 읽기
ROW()-ROW($A$4)<cfgN: 표의 처음 5행은 비교할 5영업일 전 값이 없으므로 빈칸OFFSET(B4,-cfgN,0): 같은 열에서 5행 위(= 5영업일 전) 칸B4/OFFSET(…)-1: 오늘 환율 ÷ 5영업일 전 환율 − 1 = 그날의 5영업일 변화율
- 울린 날 세기 (Alert B13:D13 → 16행)
표의 수식은 클릭하면 복사됩니다. 5행에 붙여넣은 뒤 채우기 핸들(칸 오른쪽 아래 점)을 끌어 아래 행으로 복사합니다.
셀 수식 (클릭하면 복사) 뜻 B13 계산된 일수 =COUNT(INDEX(FX_Wide!$G$4:$J$63,0,MATCH(A13,FX_Wide!$G$3:$J$3,0)))그 통화의 변화율이 계산된 날 수 C13 AMBER 이상 =COUNTIF(INDEX(FX_Wide!$G$4:$J$63,0,MATCH(A13,FX_Wide!$G$3:$J$3,0)),">="&cfgAmber)+COUNTIF(INDEX(FX_Wide!$G$4:$J$63,0,MATCH(A13,FX_Wide!$G$3:$J$3,0)),"<="&-cfgAmber)±3% 이상인 날 수 D13 RED =COUNTIF(INDEX(FX_Wide!$G$4:$J$63,0,MATCH(A13,FX_Wide!$G$3:$J$3,0)),">="&cfgRed)+COUNTIF(INDEX(FX_Wide!$G$4:$J$63,0,MATCH(A13,FX_Wide!$G$3:$J$3,0)),"<="&-cfgRed)±5% 이상인 날 수 수식 풀어 읽기
MATCH(A13, FX_Wide!$G$3:$J$3, 0): A13 통화가 BackTest 영역의 몇 번째 열인지INDEX(FX_Wide!$G$4:$J$63, 0, 열): 행 번호 0 = 그 열 전체를 범위로 꺼냄COUNTIF(범위,">="&cfgAmber)+COUNTIF(범위,"<="&-cfgAmber): +3% 이상인 날과 −3% 이하인 날을 더함
| 통화 | 계산된 일수 | AMBER 이상(±3%) | RED(±5%) | 참고: 기준 1.5%면 |
|---|---|---|---|---|
| USD | 15 | 1 | 0 | 9일 |
| JPY(100) | 15 | 0 | 0 | 5일 |
| CNH | 15 | 3 | 0 | 9일 |
| EUR | 15 | 1 | 0 | 10일 |
- 9월 20영업일 중 앞의 5일은 비교할 5영업일 전 값이 없어서 15일만 계산됨
- 3% 기준이면 한 달에 몇 번 정도 울리는 수준
- 기준을 1.5%로 낮추면 USD 9일, JPY 5일, CNH 9일, EUR 10일로 훨씬 자주 울림. Settings 값을 바꿔 가며 개수를 비교해 봄
5-8실습 정리
확인값이 같으면 다음 단계로 넘어갑니다. 막히면 해당 장 끝의 완성 파일로 이어 갑니다.
| 순서 | 시트 | 할 일 | 확인 |
|---|---|---|---|
| 1 | Raw_FX | 3-3 쿼리 결과 (9/1~9/30) | 80행 |
| 2 | FX_Wide | 날짜(A), 환율(B~E) | 영업일 수 20, 9/30 USD 1,358.40 |
| 3 | Exposure | 노출 입력, B~J열 수식, 방향성 검산 | 합계 +60,154,000 |
| 4 | Scenario | ±3%·±5% 영향, 혼합 영향 | 혼합 합계 −101,342,900 |
| 5 | Alert | 기준일, 데이터 상태, 변화율, 등급 | 기준일 9/30 · 비교일 9/21 · EUR만 AMBER |
| 6 | Alert | 알림 문장 | EUR 줄에 좋은 경보 문장 |
| 7 | FX_Wide · Alert | 날짜별 변화율, 백테스트 | AMBER 이상 USD 1 · JPY 0 · CNH 3 · EUR 1 |
| 8 | Settings | Amber 1.5%, Red 영향액 4천만 원으로 바꿔 보기 | 등급·백테스트가 바로 바뀜 (확인 후 되돌리기) |
더 해 보기 — DATA_CHECK 만들어 보기
- Exposure A8의
EUR을GBP로 바꿈 → Raw_FX에 GBP가 없으므로 Alert 데이터 상태 MISSING, 등급 DATA_CHECK (확인 후 EUR로 되돌리기)