gistfile1.js

/**
 * Stock Portfolio Management App Script for Google Spreadsheet
 *
 * v0.1
 *
 * The script creates two sheets when your first run it. You can enter your transactions
 * in the sheet named "Transactions", e.g. GOOG, 540.3, 100, Buy. Selecting "Update Stock Info"
 * from Finance menu populates the summary sheet with a summary of your portfolio.
 *
 * Author: Jiayao Yu
 */
var DEFAULT_CURRENCY = "GBP"
var SUMMARY = "Summary", TRANSACTIONS = "Transactions", BUY = "BUY", SELL = "SELL";
var HEADERS = {
    Summary: ["Symbol", "Name", "Exchange", "Beta", "P/E", "EPS", "Currency",
              "Market Price", "Avg Price Paid", "Volume", "Current Value " + DEFAULT_CURRENCY,
              "Profit/Loss " + DEFAULT_CURRENCY],
    Transactions: ["Symbol", "Price", "Volume", "Action"]
};
var DUMMY_CELL = "Q50";

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      menuEntries = [ {name: "Update Stock Info", functionName: "updateStock"}];
  ss.addMenu("Finance", menuEntries);
  updateStock();
}

function openOrCreateSheet(name) {
  assert(name, "No sheet name given");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(name);
    Logger.log("Created sheet " + name);
    setHeaders(sheet);
  } else {
    Logger.log("Reading sheet " + name);
  }
  return sheet;
}

function getCurrencyExchange(currencyA, currencyB) {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(DUMMY_CELL).setValue(
    "=GoogleFinance(\"CURRENCY:" + currencyA + currencyB + "\", \"average\")");
  return sheet.getRange(DUMMY_CELL).getValue();
}

function updateStock() {
  var sheet = openOrCreateSheet("Summary"),
      symbols = [], stocksBySymbol, i, stockInfo, content, totalValue = 0,
      totalProfit = 0, forex, profit;
  Logger.log("Processing transactions");
  stocksBySymbol = processTransactions(symbols);
  sheet.clear();
  setHeaders(sheet);
  for (i = 0; i < symbols.length; i++) {
    stock = stocksBySymbol[symbols[i]];
    Logger.log("Updating symbol: " + stock.symbol);
    stockInfo = FinanceApp.getStockInfo(stock.symbol);
    if (stockInfo.currency != DEFAULT_CURRENCY) {
      forex = getCurrencyExchange(stockInfo.currency, DEFAULT_CURRENCY);
    } else {
      forex = 1;
    }
    profit = forex * stock.volume * (stockInfo.price - stock.price);
    content = [stock.symbol, stockInfo.name, stockInfo.exchange, fixed(stockInfo.beta),
               fixed(stockInfo.pe), fixed(stockInfo.eps), stockInfo.currency,
               fixed(stockInfo.price, 2), stock.price,
               stock.volume, fixed(forex * stock.volume * stock.price, 2),
               fixed(profit, 2)];
    sheet.getRange(i + 2, 1, 1, content.length).setValues([content]);
    sheet.getRange(i + 2, content.length).setFontColor(profit >= 0 ? "green" : "red");
    Logger.log("Forex=" + forex);
    totalValue += forex * stock.volume * stock.price;
    totalProfit += forex * stock.volume * (stockInfo.price - stock.price);
    
  }
  sheet.getRange(i + 2, content.length - 1).setValue(fixed(totalValue, 2));
  sheet.getRange(i + 2, content.length).setValue(fixed(totalProfit, 2));
  sheet.getRange(i + 2, content.length).setFontColor(totalProfit >= 0 ? "green" : "red");
}
  
function processTransactions(symbols) {
  var sheet = openOrCreateSheet(TRANSACTIONS), dataBySymbol = {},
      row, stock;
  for (row = 2; row < sheet.getMaxRows(); row++) {
    if (!sheet.getRange(row, 1).getValue()) {
      break;
    }
    stock = {};
    stock.symbol = sheet.getRange(row, 1).getValue();
    stock.price = sheet.getRange(row, 2).getValue();
    stock.volume = sheet.getRange(row, 3).getValue();
    stock.action = sheet.getRange(row, 4).getValue().toUpperCase();
    validateStock(stock);
    if (!dataBySymbol[stock.symbol]) {
      assert(stock.action === BUY, "Selling a stock we don't own at row " + row);
      dataBySymbol[stock.symbol] = stock;
    } else {
      merge(dataBySymbol[stock.symbol], stock);
    }
    if (symbols.indexOf(stock.symbol) === -1) {
      symbols.push(stock.symbol);
    }
  }  
  return dataBySymbol;
}

function validateStock(stock) {
  assert(stock.action === BUY || stock.action === SELL,
      "Action has to be Buy or Sell, but was " + stock.action);
}

// Merge stockB into stockA
function merge(stockA, stockB) {
  assert(stockA.symbol === stockB.symbol, "Merging different stocks");
  var newVolume, newPrice;
  if (stockB.action === BUY) {
    newVolume = stockA.volume + stockB.volume;
    newPrice = (stockA.price * stockA.volume + stockB.price * stockB.volume) / newVolume;
  } else {
    assert(stockB.volume <= stockA.volume,
         "Selling more " + stockA.symbol + " stocks than we have: " +
         stockA.volume + " > " + stockB.volume);
    newVolume = stockA.volume - stockB.volume;
    newPrice = stockA.price;
  }
  stockA.price = newPrice;
  stockA.volume = newVolume;
}


function setHeaders(sheet) {
  Logger.log("Setting headers for " + sheet.getName());
  var headers = HEADERS[sheet.getName()], i;
  assert(headers, "No headers for " + sheet.getName());
  for (i = 0; i < headers.length; i++) {
    sheet.getRange(1, i + 1).setValue(headers[i]);
  }
}

function fixed(number, width) {
  if (isNaN(number)) {
    return "-";
  }
  if (!width) {
    width = 3;
  }
  return "=FIXED(" + number +", " + width + ")";
}

function AssertException(message) { this.message = message; }
AssertException.prototype.toString = function () {
  return 'AssertException: ' + this.message;
};

function assert(exp, message) {
  if (!exp) {
    throw new AssertException(message);
  }
}
​