제이익스패스 만들기(JexPass) 강좌 2강입니다.
지난 1강에서는 제이익스패스(JexPass)를 구성하는
구글시트의 기본 틀을 준비하고,
엔화 스플릿 투자를 위한 기본 설정값을 관리하는 시트를 만들어 보았습니다.
구글시트는
- 앱시트(AppSheet)를 통해 모바일 앱에서 엔화 스플릿 투자를 운영하고,
- 파이썬 시스템이 환율을 실시간으로 자동 감시하는
- 제이익스패스의 중앙 데이터베이스 역할을 합니다.
이번 강의에서는 이 구글시트를 기반으로,
실제 매수·매도 판단이 자동으로 이루어지는 핵심 시트들을 완성해 보겠습니다.
- Auto 시트 : 실시간 엔화 환율 기록 및 매수·매도 조건을 색상 신호로 표시
- JPYKRW_Buy_System 시트 : 다음 회차 매수 환율 기준 계산
- JPYKRW_Books 시트 : 실제 매수·매도 기록, 매도 가능 여부, 수익 결과를 관리
이번 강의까지 완료하면,
“지금이 매수 구간인지, 매도 가능한 상태인지”를
구글시트가 스스로 계산하고 알려주는 구조가 눈에 보이기 시작합니다.
2강 목차
1️⃣ Auto 시트 만들기
먼저 실시간 엔화 환율이 자동으로 기록될 수 있는 기본 구조를 만들어 보겠습니다.
(1) Auto 시트 선택
지난 강의에서 만든 구글시트(JexPass_CLASS)를 실행한 뒤,
화면 하단에 있는 Auto 시트를 선택합니다.
(2) Auto 시트 구성
🗂️ 시트 정리
- A1, B1 셀만 남기고
- 나머지 행과 열은 모두 삭제합니다.
Auto 시트는 사용자가 직접 값을 입력하는 시트가 아닙니다.
환율 수집과 매수·매도 조건 판단이 자동으로 이루어지는 전용 시트입니다.
따라서 불필요한 요소는 모두 제거하고,
구조를 최대한 단순하게 유지하는 것이 중요합니다.
📐 셀 서식
- A1 : 맞춤 숫자 형식 → #,000.00
⌨️ 함수 입력
- A1 : =TRUNC(GOOGLEFINANCE(“JPYKRW”)*100, 2)
📘 함수 설명
- GOOGLEFINANCE(“JPYKRW”) ▶ 엔/원 환율을 가져옵니다.
- *100 ▶ 100엔 기준 환율로 변환합니다.
- TRUNC( , 2) ▶ 소수점 둘째 자리까지만 표시합니다.
📌 GOOGLEFINANCE 함수 임시사용 안내
제이익스패스(JexPass)는 정확하고 안정적인 시스템 운영을 위해
실제 거래소 화면에 표시되는 환율을 OCR로 인식하는 방식을 사용합니다.
다만 이번 강의에서는 OCR 설정 이전 단계이기 때문에,
GOOGLEFINANCE 함수를 임시로 사용해서 엔/원 환율을 불러오도록 하겠습니다.
이 함수는 환율을 별도 설정 없이 바로 사용할 수 있지만,
실제 거래소 환율 차이가 발생되기 때문에
시트 구조와 자동 매매 로직을 이해하기 위한 연습용으로만 사용되며
이후 강의에서는 OCR로 수집한 실제 환율 값으로 완전히 대체될 예정입니다.
2️⃣ JPYKRW_Books 시트 만들기
JPYKRW_Books 시트는 엔화 스플릿 매수·매도 기록을 관리하는 핵심 시트입니다.
각 회차별로,
- 얼마에 매수했는지,
- 현재 매도 가능한 상태인지를 자동으로 판단하고,
그 결과를 Auto 시트와 연동해 자동 감시할 수 있도록 구성됩니다.
👉 쉽게 말해
“얼마에 샀고, 지금 팔 수 있는 상태인가?” 를 판단하는 중심 시트입니다.
(1) 시트 기본 구조 정리
① JPYKRW_Books 시트 선택
② A1 ~ Q5 셀만 남기고 나머지 행과 열은 모두 삭제합니다.
이 시트는 앱시트(AppSheet) 모바일 앱과 연동되어,
사용자가 데이터를 입력하면 자동으로 아래 방향으로 행이 추가되는 구조입니다.
따라서, 미리 많은 행을 만들어 둘 필요는 없고,
구조만 깔끔하게 정리해 두는 것이 중요합니다.
(2) 항목 작성 및 셀 서식 설정
2행에 위 이미지와 같은 항목(컬럼명)을 입력하고,
아래 표와 같이 각 열의 셀 서식을 설정합니다.
| 열 | 항목 이름 | 셀 서식 |
|---|---|---|
| A | ID | 자동 |
| B | 매수날짜 | 맞춤 날짜 및 시간 → 1930-08-05 |
| C | 매수환율 | 맞춤 숫자 형식 → #,##0.00 |
| D | 매수엔화 | 맞춤 통화 → ￥1,000 |
| E | 매수금액 | 맞춤 숫자 형식 → #,##0 |
| F | 목표환율 | 맞춤 숫자 형식 → #,##0.00 |
| G | 매도감지 | 자동동 |
| H | 매도날짜 | 맞춤 날짜 및 시간 → 1930-08-05 |
| I | 매도환율 | 맞춤 숫자 형식 → #,##0.00 |
| J | 매도엔화 | 맞춤 통화 → ￥1,000 |
| K | 매도금액 | 통화 (반올림) → ￦1,000 |
| L | 수익금액 | 통화 (반올림) → ￦1,000 |
| M | 비고 | 일반 텍스트 |
| N | 매수이미지 | 일반 |
| O | 매수환율OCR | 일반 |
| P | 매도이미지 | 일반 |
| Q | 매도환율OCR | 일반 |
📌 셀 서식 설정 방법
① 해당 열 전체 클릭
② 상단 메뉴 바에서 ‘123’ 아이콘 클릭
③ 셀 서식 선택
④ 형식 지정
⑤ 적용
이 과정은
이후 앱시트에서 컬럼 타입을 정확히 인식하게 만드는 중요한 단계입니다.
(3) 샘플 데이터 입력
아래 이미지와 같이 3개의 샘플 데이터를 입력합니다.
📌 왜 샘플 데이터가 필요한가?
다음 강의에서 만들 앱시트(AppSheet)는
구글시트를 연동할 때 기존 데이터의 형태를 기준으로 컬럼 타입을 자동 인식합니다.
따라서, 미리 샘플 데이터를 만들어 두면
앱시트 설정이 훨씬 수월해집니다.
(4) 매도감지 함수 만들기
이제 매도 가능 여부를 자동으로 판단하는 수식을 만들어 보겠습니다.
① G3 셀에 아래 함수를 입력합니다.
= IF(AND(ISBLANK(H3), F3 <= Auto!$A$1), "OK", "NO")
② G3 셀을 복사한 뒤, G4, G5 셀에 수식 붙여넣기를 합니다.
📘 함수 설명
- ISBLANK(H3) ▶ 아직 매도되지 않은 상태인지 확인
- F3 <= Auto!$A$1 ▶ 목표환율이 현재환율 이하인지 확인
- 두 조건을 모두 만족하면 ▶ “OK” (매도 가능)
- 그 외의 경우는 ▶ “NO” (매도 완료 또는 대기 상태)로 표시됩니다.
✅ 정리
이제 JPYKRW_Books 시트는
각 회차별로 매수한 엔화가 지금 매도 가능한 상태인지를
구글시트가 자동으로 판단해 주는 역할을 하게 됩니다.
이 판단 결과는
다음 단계에서 Auto 시트와 연결되어 실시간 감시 신호로 활용됩니다.
3️⃣ JPYKRW_Buy_System 시트 만들기
이 시트는 다음 회차 엔화 매수를 언제 할지 판단하는 계산 전용 시트입니다.
현재 환율을 기준으로
- 다음 매수 대기 환율을 자동으로 계산하고
- 매수 조건이 충족되었는지 여부를 판단합니다.
👉 쉽게 말해
“지금이 다음 회차 매수 타이밍인가?”를 시트가 대신 판단해 주는 역할입니다.
(1) 시트 양식 만들기
① JPYKRW_Buy_System 시트 선택
② A1 ~ D3 셀만 남기고 나머지 행과 열은 모두 삭제합니다.
📌 구조 설명
이 시트는 사람이 값을 입력하는 공간이 아니라,
다른 시트의 데이터를 바탕으로 자동 계산만 수행하는 시트입니다.
따라서, 필요한 정보만 남기고 구조를 단순하게 유지하는 것이 중요합니다.
(2) 항목 입력 및 셀 서식 설정
2행에는 항목 이름을 입력하고,
3행에는 각 항목에 맞는 셀 서식을 설정합니다.
| 열 | 항목 이름 | 셀 서식 |
|---|---|---|
| A | 대기환율 | 맞춤 숫자 형식 → #,000.00 |
| B | 대기엔화 | 맞춤 통화 → ￥1,000 |
| C | 현재환율 | 맞춤 숫자 형식 → #,000.00 |
| D | 매수감지 | 자동 |
(3) 함수 적용
이제 각 셀에 자동 계산을 위한 함수를 적용합니다.
① 대기엔화 (B3)
= JPYKRW_Setting!B3
- 매 회차 매수할 엔화 수량을
- 설정 시트(JPYKRW_Setting)에서 그대로 가져옵니다.
👉 매수 수량을 한 곳에서만 관리할 수 있어 구조가 깔끔해집니다.
② 현재환율 (C3)
= Auto!A1
- Auto 시트에 기록된 실시간 엔/원 환율을 참조합니다.
- 현재 단계에서는 GOOGLEFINANCE,
이후에는 OCR로 수집한 실제 환율이 들어오게 됩니다.
③ 대기환율 (A3)
= IFERROR(
MIN(
FILTER(
JPYKRW_Books!$C:$C,
(JPYKRW_Books!$B:$B <> ""),
(JPYKRW_Books!$H:$H = "")
)
) - JPYKRW_Setting!$C$3,
JPYKRW_Setting!$A$3
)
📘 함수 설명
- 아직 매도되지 않은 매수 회차 중 가장 낮은 매수 환율을 기준으로
- 설정된 매수 간격만큼 하락한 환율을 다음 회차의 대기 환율로 계산합니다.
📌 만약 매수 기록이 하나도 없다면 최초 기준 환율을 자동으로 사용합니다.
④ 매수감지 (D3)
= IF(A3 >= C3, "OK", "NO")
- 현재환율이 대기환율 이하로 내려오면
→ “OK” - 그렇지 않으면
→ “NO”
👉 이 값이 자동 매수 신호로 사용됩니다.
✅ 정리
이렇게 정리하면
사람이 직접 계산하던 매수 타이밍을 대신 판단하여,
- 언제 대기할지
- 언제 매수 조건이 충족됐는지를
명확한 신호(OK / NO)로 알려주는 역할을 하게 됩니다.
이 판단 결과는 다음 단계에서 Auto 시트와 연동되어 실시간 감시 신호로 활용됩니다.
4️⃣ Auto 시트 색상 신호 설정
마지막으로 Auto 시트를 ‘신호판’처럼 사용하는 단계입니다.
Auto 시트는
사람이 숫자를 보고 직접 판단하는 공간이 아니라,
“지금 매수 또는 매도 조건이 충족되었는지”를 색상으로 알려주는 감시용 시트입니다.
이를 위해
- 매수 신호 (JPYKRW_Buy_System 시트)
- 매도 신호 (JPYKRW_Books 시트)
를 하나로 모아 통합 판단하고,
조건이 충족되면 Auto 시트의 색상이 자동으로 바뀌도록 설정합니다.
(1) 통합 판단 셀 만들기 (B1)
Auto 시트에서 B1 셀을 선택한 뒤,
아래 함수를 입력합니다.
= IF(
JPYKRW_Buy_System!$D$3 = "OK", "OK",
IF(COUNTIF(JPYKRW_Books!$G$3:$G, "OK") >= 1, "OK", "NO")
)
📘 함수 설명
- JPYKRW_Buy_System!D3 = “OK”
→ 다음 회차 매수 조건이 충족된 경우 - COUNTIF(JPYKRW_Books!G3:G, “OK”) >= 1
→ 매도 가능한 엔화가 하나라도 존재하는 경우
두 조건 중 하나라도 만족하면 “OK”,
모두 만족하지 않으면 “NO”를 반환합니다.
📌 즉, B1 셀은
매수든 매도든 지금 행동이 필요한 상태인지를
하나의 값으로 정리해 주는 통합 상태 셀입니다.
(2) A1 셀에 색상 신호 설정
이제 B1 셀의 결과를 기준으로
Auto 시트의 A1 셀 색상이 자동으로 바뀌도록 설정합니다.
① A1 셀 선택
② 상단 메뉴 → 서식
③ 조건부 서식
조건부 서식 규칙을 아래와 같이 설정합니다.
- 조건 유형 : 맞춤 수식
- 수식
=B1="OK"
- 서식 지정 스타일 : 채우기 색상 → 빨강
설정 후 [완료] 버튼을 눌러 적용합니다.
📌 이제
- 매수 또는 매도 조건 충족 → A1 셀 빨간색
- 아무 조건도 충족되지 않음 → 기본 색상
으로 자동 표시됩니다.
(3) B열 숨기기
B1 셀은 자동 계산 전용 셀이므로
사용자가 직접 건드리지 않도록 숨겨두는 것이 좋습니다.
- B열 선택 → 마우스 우클릭 → 열 숨기기
이렇게 하면
👉 A1 셀 하나만 보고도 현재 상태를 색상으로 확인할 수 있습니다.
✅ 정리
이제 Auto 시트는 다음과 같은 역할을 수행합니다.
- 거래소 실시간 환율 자동 입력
- 실시간 환율을 기준으로 매수·매도 조건을 자동 판단
- 조건 충족 시 색상 신호로 즉시 표시
- 파이썬 자동화 시스템이 이 색상 감지해 텔레그램 알림 메시지 전송
📌 이 구조 덕분에
사람은 숫자를 계산하지 않아도 되고,
시스템은 항상 동일한 기준으로 일관되게 판단할 수 있습니다.
2강을 끝으로 구글시트를 완성했습니다.
다음 강의에서 AppSheet으로 모바일 앱을 만들어 봅시다.
