Google Sheets as a Trading Journal with Live API Data

const API_KEY = "YOUR_API_KEY";
const BASE_URL = "https://tickatlas.com/v1";

/**
 * Fetch a technical indicator value.
 * Usage: =CLAW_INDICATOR("EURUSD", "RSI_14", "H4")
 */
function CLAW_INDICATOR(symbol, indicator, timeframe) {
  const url = \`\${BASE_URL}/indicator?symbol=\${symbol}\` +
    \`&indicator=\${indicator}&timeframe=\${timeframe}\

/**
 * When a new trade is logged, auto-fill indicator columns.
 * Triggered by an onEdit event on the Trades sheet.
 */
function onTradeEntry(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Trades") return;

  const row = e.range.getRow();
  const col = e.range.getColumn();

  // Column B = Symbol (trigger when symbol is entered)
  if (col !== 2 || row < 2) return;

  const symbol = sheet.getRange(row, 2).getValue();
  if (!symbol) return;

  // Fetch RSI and MACD at the time of entry
  const rsi = CLAW_INDICATOR(symbol, "RSI_14", "H4");
  const macd = CLAW_INDICATOR(symbol, "MACD", "H4");

  // Write to RSI column (K) and MACD column (L)
  sheet.getRange(row, 11).setValue(rsi);
  sheet.getRange(row, 12).setValue(macd);
}

// Install the trigger
function createTrigger() {
  ScriptApp.newTrigger("onTradeEntry")
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create();
}

/**
 * Refresh all prices on the Live Prices tab.
 * Set up a time-driven trigger to run every 5 minutes.
 */
function refreshWatchlist() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Live Prices");
  const symbols = sheet.getRange("A2:A20").getValues().flat().filter(Boolean);

  for (let i = 0; i < symbols.length; i++) {
    const row = i + 2;
    const symbol = symbols[i];

    try {
      const price = CLAW_PRICE(symbol);
      const rsi = CLAW_INDICATOR(symbol, "RSI_14", "H4");
      const atr = CLAW_INDICATOR(symbol, "ATR_14", "H4");

      sheet.getRange(row, 2).setValue(price);   // Column B: Price
      sheet.getRange(row, 3).setValue(rsi);     // Column C: RSI
      sheet.getRange(row, 4).setValue(atr);     // Column D: ATR
      sheet.getRange(row, 5).setValue(new Date()); // Column E: Last Update
    } catch (err) {
      sheet.getRange(row, 2).setValue("ERR");
    }
  }
}

// Set up auto-refresh every 5 minutes
function createRefreshTrigger() {
  ScriptApp.newTrigger("refreshWatchlist")
    .timeBased()
    .everyMinutes(5)
    .create();
}

Win Rate:
=COUNTIF(Trades!H2:H, ">"&0) / COUNTA(Trades!H2:H)

Average R-Multiple:
=AVERAGE(Trades!I2:I)

Profit Factor:
=SUMIF(Trades!H2:H, ">"&0) / ABS(SUMIF(Trades!H2:H, "<"&0))

Max Consecutive Losses:
(Use Apps Script for this -- too complex for a formula)

Expectancy:
=(Win Rate * Average Win) - ((1 - Win Rate) * Average Loss)