엑셀·구글시트로 먹튀검증 배당률 계산기 만들기 – 단계별 튜토리얼

1️⃣ 기본 데이터 시트 설계

시트 1 – 사이트 리스트 (Sites)

다음 컬럼을 포함한다.

A: 사이트명
B: 도메인
C: 위험 점수 (0‑100)
D: 현재 배당
E: 평균 배당(30일)
F: 보너스 비율(%)
G: 배팅 배수(N)
H: 인출 제한(일)
I: 최소 인출 금액(원)
J: 환율(USD → KRW)
    

시트 2 – 입금·베팅·출금 (Transactions)

A: 날짜
B: 사이트명 (Dropdown → ‘Sites!A2:A100’)
C: 입금액(원)
D: 보너스(원) – 자동 계산 (함수)
E: 베팅액(원) – 사용자가 입력
F: 배당 적용 후 예상 수익(원) – 자동 계산
G: 실제 출금액(원) – 사용자가 입력
H: 손익(원) – 자동 계산
    

2️⃣ 위험 점수와 기대값(EV) 계산

위험 점수 적용 규칙

위험 점수가 80점 이상이면 ‘베팅 금액 × 0.9’로 가중치를 낮춘다. 60‑79점은 0.95, 60점 미만은 1.0으로 적용한다.

=IF(C2>=80,0.9,IF(C2>=60,0.95,1))

EV 공식 구현 (시트 Transactions!F)

=E2 * (D2/100) * (1 + (C2/100)) * (위험 가중치) - (C2/100)

여기서 D2는 배당(예: 2.5), C2는 보너스 비율, 위험 가중치는 위 함수에 의해 자동 반환된다.

3️⃣ 보너스·배팅 배수·인출 제한 계산

보너스 자동 산출 (시트 Transactions!D)

=VLOOKUP(B2, Sites!$A$2:$G$100, 6, FALSE) * C2 / 100

‘Sites’ 시트에서 보너스 비율을 찾아 입금액에 곱한다.

배팅 배수 충족 여부 (조건부 서식)

‘Transactions’ 시트에 ‘배팅액 ≥ 보너스 × 배팅 배수’를 검사한다.

=E2 >= (D2 * Sites!$G$2)

조건부 서식으로 ‘초과 시 초록, 미충족 시 빨강’ 표시한다.

인출 제한 검증 (시트 Transactions!G)

=IF(TODAY()-LookupDate <= Sites!$H$2, "제한", "가능")

‘LookupDate’는 최초 입금일을 기록하고, 인출 가능 여부를 표시한다.

4️⃣ 환율·다국적 입금 적용

해외 입금(USD) → 원화 변환을 위해 ‘GoogleFinance(“CURRENCY:USDKRW”)’ 함수를 사용한다.

=IF(입금통화="USD", C2 * GoogleFinance("CURRENCY:USDKRW"), C2)

‘Sites’ 시트에 ‘환율’ 컬럼을 두고, 자동 업데이트되도록 설정한다.

5️⃣ 차트·시각화 – 위험·수익 트렌드

‘삽입 → 차트 → 꺾은선 차트’를 선택하고, X축에 ‘날짜’, Y축에 ‘EV’(Transactions!F)와 ‘손익’(Transactions!H)를 동시에 표시한다.

차트에 ‘위험 점수 색상 구분’(위험>80 → 빨강, 60‑79 → 주황, <60 → 초록) 조건부 서식을 적용해 시각적으로 위험 수준을 파악한다.

6️⃣ 자동 보고서 생성 – 구글시트 Apps Script 활용

‘도구 → 스크립트 편집기’에 아래 코드를 입력한다.

function generateReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const trans = ss.getSheetByName('Transactions');
  const data = trans.getRange('A2:H' + trans.getLastRow()).getValues();

  const report = ss.insertSheet('Report_' + new Date().toISOString().slice(0,10));
  report.appendRow(['날짜','사이트','입금액','보너스','EV','손익']);

  data.forEach(row => {
    const ev = row[5]; // EV 계산된 셀
    const profit = row[7]; // 손익 셀
    report.appendRow([row[0], row[1], row[2], row[3], ev, profit]);
  });
}
    

스크립트를 저장하고 ‘실행 → generateReport’ 하면, 현재 거래 데이터를 기반으로 일별·사이트별 보고서 시트를 자동 생성한다.

7️⃣ 실전 적용 예시 (데이터 입력 예시)

위 표는 실제 입력값을 기반으로 자동 계산된 EV와 손익이다. 위험 점수가 80점 이상인 A사이트는 가중치 0.9 적용으로 EV가 약간 낮아졌다.

FAQ – 배당률 계산기 제작

Q1. 위험 가중치를 직접 수정하고 싶다면?

A1. ‘위험 가중치’ 함수를 별도 셀에 두고 =IF(C2>=80,0.9,...)을 해당 셀에 입력한 뒤, EV 수식에서 셀 참조하도록 변경한다.

Q2. 구글시트에서 실시간 환율 업데이트가 안 될 때?

A2. ‘GoogleFinance’ 함수는 가끔 지연될 수 있다. 수동으로 ‘=IMPORTXML(“https://...”)’를 활용해 최신 환율을 가져오거나, 스크립트에서 API 호출을 구현한다.

Q3. 엑셀에서 동일 기능을 구현하려면?

A3. 엑셀에서는 ‘WEBSERVICE’·‘FILTERXML’ 함수로 환율을 가져오고, ‘VLOOKUP·IF·CONDITIONAL FORMATTING’으로 나머지 로직을 동일하게 구현한다.

Q4. 대량 거래(수천 건) 처리 시 성능 이슈가 있나요?

A4. 구글시트는 5천 행 정도에서 성능 저하가 시작된다. 대규모 데이터는 ‘BigQuery’ 연동 혹은 엑셀 ‘Power Query’ 사용을 권장한다.

Q5. 보너스·배팅 배수 조건을 자동으로 검증하는 방법?

A5. ‘조건부 서식’ 대신 ‘데이터 검증(Validation)’ 기능을 활용해 ‘배팅액>=보너스*배수’가 만족되지 않을 경우 입력 제한을 두면 오류를 사전에 방지한다.