Created
March 4, 2018 14:25
-
-
Save peterkeen/56099681190c22d57c92ae3fc6a2250c to your computer and use it in GitHub Desktop.
Export a Tiller transaction spreadsheet as a ledger file while checking for duplicates in ledger-web database.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
require 'rubygems' | |
require 'bundler/setup' | |
require 'pg' | |
require 'sequel' | |
require 'ledger_gen' | |
require 'google_drive' | |
SPREADSHEET_ID = 'your-google-sheet-id' | |
DATABASE_URL = 'postgres://username:password@host/database' | |
DB = Sequel.connect(DATABASE_URL) | |
ACCOUNT_RULES = [ | |
[ /(Meijer|Kroger)/i, 'Expenses:Food:Groceries' ], | |
[ /(Hulu|Netflix|Kindle)/i, 'Expenses:Entertainment' ], | |
] | |
session = GoogleDrive::Session.from_config('.config.json') | |
def for_each_row(session, spreadsheet, worksheet) | |
worksheets = session.spreadsheet_by_key(spreadsheet).worksheets | |
ws = worksheets.detect { |w| w.title == worksheet } | |
titles = [] | |
(1..ws.num_cols).each do |col| | |
titles << ws[1,col] | |
end | |
(2..ws.num_rows).each do |row| | |
data = {} | |
(1..ws.num_cols).each do |col| | |
data[titles[col -1]] = ws[row, col] | |
end | |
yield data | |
end | |
end | |
def transaction_exists?(txn_id) | |
DB.fetch("select 1 from ledger where '#{txn_id}' in (select btrim(x) from unnest(string_to_array(jtags->>'tiller_id', ',')) x) limit 1").all.length > 0 | |
end | |
def account_for_row(row) | |
account = row['Account'] | |
description = row['Description'] | |
ACCOUNT_RULES.each do |rule| | |
if description =~ rule.first | |
return rule.last | |
end | |
end | |
'Expenses:Misc' | |
end | |
journal = LedgerGen::Journal.new | |
for_each_row session, SPREADSHEET_ID, "Transactions" do |row| | |
txn_id = row['Transaction ID'] | |
txn_date = Date.strptime(row["Date"], "%m/%d/%Y") | |
next if txn_date < Date.new(2018,2,1) | |
next if transaction_exists? txn_id | |
amount = row["Amount"].gsub('$', '').gsub(',', '').to_f | |
journal.transaction do |txn| | |
txn.cleared! | |
txn.date txn_date | |
txn.payee row["Description"] | |
txn.comment "tiller_id: #{txn_id}" | |
txn.posting account_for_row(row), amount * -1 | |
txn.posting row["Account"] | |
end | |
end | |
puts journal.pretty_print('-y "%m/%d" --sort=date') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment