Banana QIF Importer

ch.banana.filter.import.qif.js

// @id = ch.banana.filter.import.qif
// @api = 1.0
// @pubdate = 2018-06-09
// @publisher = Banana.ch SA
// @description = Quicken Interchange Format (*.qif)
// @doctype = *
// @docproperties =
// @task = import.transactions
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @inputfilefilter = QIF (*.qif);;All files (*.*)
// @inputfilefilter.de = QIF (*.qif);;Alle Dateien (*.*)
// @inputfilefilter.fr = QIF (*.qif);;Tous (*.*)
// @inputfilefilter.it = QIF (*.qif);;Tutti i files (*.*)
// @timeout = 10000

/**
 * Parse the data and return the data to be imported as a tab separated file.
 */

var dateFormat = "d.m.y";

function exec( string) {
   var accounting = convertQifToObject( string);
   var accounts = accounting['accounts'];
   if ( accounts.length > 1) {
      if ( accounts[0]['Date'] > accounts[accounts.length-1]['Date'])
         accounts = accounts.reverse();
   }

   var toReturn = '';
   if (Banana.script.getParamValue('task') === 'import.accounts') {
      var accountHeaders = ['Account','Description','BClass','Opening'];
      var accountsToImport = accounting['accounts'].concat(
               accounting['accounts'],
               accounting['categories'],
               accounting['costcenters'],
               accounting['segments']);
      toReturn = Banana.Converter.objectArrayToCsv(accountHeaders, accountsToImport, '\t');
   } else if (Banana.script.getParamValue('task') === 'import.transactions') {
      var transactionsHeaders = ['Date','DateValue','Doc','Description','Account','ContraAccount','IsDetail','Income','Expenses','Cc1'];
      var transactionsToImport = accounting['transactions'];
      toReturn = Banana.Converter.objectArrayToCsv(transactionsHeaders, transactionsToImport, '\t');
   }
   return toReturn;
}

// String method startsWith
if (!String.prototype.startsWith) {
   String.prototype.startsWith = function(searchString, position){
      return this.substr(position || 0, searchString.length) === searchString;
   };
}

/**
 * Convert a qif file to an objects of accounts and transactions
 */

function convertQifToObject(string) {
   var accounting = { // readen data
      'accounts' : [],
      'categories' : [],
      'segments' : [],
      'costcenters' : [],
      'transactions': [],
      'accountsMap' : [],
      'accountsAlreadyImported': [],
      'accountsWithTransactions' : []
   };

   var objectState = '';  // can be one of 'account', 'segment', 'category', 'transaction' or '_skip'
   var accountNumber = ''; // account number defined by the last !account block
   var dataObject = {}; // data of the last block
   var accountObj = null;
   var date = '';
   var lineNr = 0;
   var swapDebitCredit = false;

   var lines = Banana.Converter.stringToLines(string);

   for (lineNr = 0; lineNr < lines.length; lineNr++) {
      var lineStr = lines[lineNr];
      if (lineStr.startsWith('!')) {
         // Block start
         if (lineStr.startsWith('!Account')) {
            objectState = 'account';
         } else if (lineStr.startsWith('!Type:Tag')) {
            objectState = 'costcenter';
         } else if (lineStr.startsWith('!Type:Cat')) {
            objectState = 'category';
         } else if (lineStr.startsWith('!Type:Bank') ||
                    lineStr.startsWith('!Type:Cash') ||
                    lineStr.startsWith('!Type:CCard') ||
                    lineStr.startsWith('!Type:Oth A') ||
                    lineStr.startsWith('!Type:Oth L')) {
            objectState = 'transaction';
         } else {
            // guess transactions are following
            objectState = '_skip';
         }

      } else if (lineStr.startsWith('^')) {
         // Block end
         if (objectState === 'account') {
            accountObj = getAccountObject(accounting, dataObject['Account']);
            accountObj['Description'] = dataObject['Description'];
            accountObj['BClass'] = dataObject['BClass'];
         } else if (objectState === 'category') {
            accounting['categories'].push(dataObject);
         } else if (objectState === 'costcenter') {
            accounting['costcenters'].push(dataObject);
         } else if (objectState === 'segment') {
            accounting['segments'].push(dataObject);
         } else if (objectState === 'transaction') {
            // If on same account and it is the first transaction, register as opening balance
            if (dataObject['Account'] && dataObject['Account'] === dataObject['ContraAccount'] &&
                  !accountHasTransactions(accounting, dataObject['Account'])) {
               accountSetOpeningAmount(accounting, dataObject['Account'], dataObject['Income']);
            } else if (!dataObject['ContraAccount'] || !accountIsAlreadyImported(accounting, dataObject['ContraAccount'])) {
               if (swapDebitCredit) {
                  var tempIncomeAmount = dataObject['Income'];
                  dataObject['Income'] = dataObject['Expenses'];
                  dataObject['Expenses'] = tempIncomeAmount;
               }
               accounting['transactions'].push(dataObject);
            } else {
               // Transaction already inserted by another account card
            }
            accountSetAlreadyImported(accounting, dataObject['Account']);
            accountSetHasTransactions(accounting, dataObject['Account']);
         } else {
            // skip
         }
         swapDebitCredit = false;
         dataObject = {};

      } else {
         // Block of data

         // Account block
         if (objectState === 'account') {
            if (lineStr.startsWith('N')) { // Name
               accountNumber = lineStr.substr(1);
               dataObject['Account'] = accountNumber;
               if (!dataObject['Description'])
                  dataObject['Description'] = accountNumber;

            } else if (lineStr.startsWith('D')) { // Description
               dataObject['Description'] = lineStr.substr(1);

            } else if (lineStr.startsWith('T')) { // Type
               var accountType = lineStr.substr(1);
               if (accountType === 'Oth L')
                  dataObject['BClass'] = '2';
               else
                  dataObject['BClass'] = '1';

            } else {
               // skip
            }

            // Category or segment block
         } else if (objectState === 'category' || objectState === 'segment') {
            if (lineStr.startsWith('N')) { // name
               accountNumber = lineStr.substr(1);
               if (accountNumber.indexOf(':') >= 0) {
                  objectState = 'segment';
                  dataObject['BClass'] = '';

                  var segments = accountNumber.split(':');
                  var segmentPrefix = new Array(segments.length).join(':');
                  var segmentName = segments[segments.length - 1];
                  accountNumber = segmentPrefix + segmentName;
                  dataObject['Account'] = accountNumber;
                  if (!dataObject['Description']) {
                     dataObject['Description'] = segmentName;
                  }
               } else {
                  objectState = 'category';
                  dataObject['BClass'] = '3';

                  accountNumber = lineStr.substr(1);
                  dataObject['Account'] = accountNumber;
                  if (!dataObject['Description']) {
                     dataObject['Description'] = accountNumber;
                  }
               }

            } else if (lineStr.startsWith('D')) { // Description
               dataObject['Description'] = lineStr.substr(1);

            } else if (lineStr.startsWith('I')) { // Income category
               if (objectState === 'category')
                  dataObject['BClass'] = '4';

            } else if (lineStr.startsWith('E')) { // Expense category
               if (objectState === 'category')
                  dataObject['BClass'] = '3';

            } else {
               // skip
            }

            // Cost center block
         } else if (objectState === 'costcenter') {
            if (lineStr.startsWith('N')) {
               accountNumber = lineStr.substr(1);
               dataObject['Account'] = '.' + accountNumber;
               if (!dataObject['Description'])
                  dataObject['Description'] = accountNumber;
            } else if (lineStr.startsWith('D')) {
               dataObject['Description'] = lineStr.substr(1);
            } else {
               // skip
            }

            // Transaction block
         } else if (objectState === 'transaction') {
            if (!dataObject['Account'])
               dataObject['Account'] = accountNumber;
            if (!dataObject['Description'])
               dataObject['Description'] = '';

            if (lineStr.startsWith('D')) { // Date
               date = lineStr.substr(1);
               if (date) {
                  if (date.indexOf("-") > 0) {
                     // Local format YYYY-MM-DD
                     // Leave unchanged
                  } else {
                     date = date.replace("' 0", "'2000");
                     date = Banana.Converter.toInternalDateFormat(date, dateFormat); // Local format m/d'y
                  }
               }
               dataObject['Date'] = date;
            } else if (lineStr.startsWith('T') || lineStr.startsWith('$')) {
               var amount = lineStr.substr(1);
               dataObject['Income'] = Banana.Converter.toInternalNumberFormat(amount, getDecimalSeparator(amount));
               dataObject['Expenses'] = '';

            } else if (lineStr.startsWith('L') || lineStr.startsWith('S')) {
               var contraAccountName = lineStr.substr(1);
               var costCenterName = '';
               if (contraAccountName.indexOf('/') >= 0) {
                  var terms = contraAccountName.split('/');
                  contraAccountName = terms[0];
                  costCenterName = terms[1];
               }
               if (contraAccountName.startsWith('[')) {
                  contraAccountName = contraAccountName.substring(1, contraAccountName.length-1);
                  swapDebitCredit = true;
               }

               if (lineStr.startsWith('S')) { // Split Row
                  if (!dataObject['IsDetail']) {
                     // Insert previous transaction as counterpart transaction
                     dataObject['IsDetail'] = 'S';
                     dataObject['ContraAccount'] = '';
                     dataObject['Cc1'] = '';
                  }
                  accounting['transactions'].push(dataObject);

                  // Create detail transaction
                  dataObject = {
                     'Date': date,
                     'Description': '',
                     'ContraAccount': contraAccountName,
                     'IsDetail': 'D',
                     'Cc1': costCenterName
                  };
               } else {
                  dataObject['ContraAccount'] = contraAccountName;
                  dataObject['Cc1'] = costCenterName;
               }

            } else if (lineStr.startsWith('P')) {
               if (dataObject['Description'])
                  dataObject['Description'] += ', ';
               dataObject['Description'] += lineStr.substr(1);

            } else if (lineStr.startsWith('M')) {
               if (dataObject['Description'])
                  dataObject['Description'] += ', ';
               dataObject['Description'] += lineStr.substr(1);

            } else if (lineStr.startsWith('E')) {
               if (dataObject['Description'])
                  dataObject['Description'] += ', ';
               dataObject['Description'] += lineStr.substr(1);

            } else {
               // skip
            }

         } else {
            // skip
         }

      }
   }

   return accounting;
}

function getDecimalSeparator(amount) {
   if (!amount)
      return ".";

   for (var i = amount.length -1; i >= 0; i--) {
      if (amount[i] === ".")
         return ".";
      else if (amount[i] === ",")
         return ",";
   }

   return ".";
}

function getAccountObject(accounting, account) {
   var accountObject = accounting.accountsMap[account];
   if (!accountObject) {
      accountObject = {};
      accountObject['Account'] = account;
      accounting['accounts'].push(accountObject);
      accounting['accountsMap'][account] = accountObject;
   }
   return accountObject;
}

function accountIsAlreadyImported(accounting, account) {
   return accounting.accountsAlreadyImported[account] === true;
}

function accountSetAlreadyImported(accounting, account) {
   accounting.accountsAlreadyImported[account] = true;
}

function accountHasTransactions(accounting, account) {
   return accounting.accountsWithTransactions[account] === true;
}

function accountSetHasTransactions(accounting, account) {
   accounting.accountsWithTransactions[account] = true;
}

function accountSetOpeningAmount(accounting, account, openingAmount) {
   var accountObject = getAccountObject(accounting, account)
   accountObject['Opening'] = openingAmount;
   accountSetHasTransactions(accounting, account);
}