MyPAYE -> QuickFile integration

README.md

MyPAYE -> QuickFile integration

This is a Groovy script to pull payroll data from MyPAYE and use it to create a journal and optionally a set of bank transactions in the QuickFile accounting package. The MyPAYE API is documented here (PDF), it's fairly straightforward if a bit clunky, and has a couple of weird things (like the payload you POST has to be XML but the Content-Type has to be form-urlencoded...) but it wasn't too difficult for me to get working.

There's a few hard-coded nominal codes in there but I think they are ones that should be consistent across all QuickFile accounts (7000 for gross wages, 7006 for Employer NI, etc.).

The script expects another config file in the same folder called config.groovy with the API credentials and a few other settings for both the QuickFile and MyPAYE sides.

Software requirements

• A Java runtime environment version 8 or later (if you don't already have one installed I recommend Zulu JDK)
• Groovy version 2.5.x or earlier (the httpbuilder library I'm using doesn't work with Groovy 3 or later, one day I may have reason to update it but that day is not yet)

You need to be able to run the groovy command from your command prompt/terminal/whatever it is called on your computer - how to do this varies from computer to computer, it may mean adding the groovy-<version>/bin folder to your PATH environment variable, or creating a symbolic link into a directory that is already on the PATH.

Setting up

Create an empty folder, and save the three .groovy files from this Gist into that.

The script needs access to both the QuickFile and MyPAYE APIs. For the QuickFile side you need to create an "app" with appropriate permissions:

• Go to "account settings" -> "3rd party integration" -> "API"
• Create a new app, give it a meaningful name like "MyPAYE integration"
• Select Invoices.Journal_Create and Invoices.Bank_CreateTransaction (the latter is only required if you want the script to generate bank transactions for the payment of net wages) in the left hand list and "add selected" to move them to the right hand list
• Save the app, go back to the list of apps
• Copy the "account API key" from above the list of apps, the "app ID" (click the blue question mark button) from the app you just created and the "account" number from the top right corner of the screen and paste them into your config.groovy in the appropriate slots in the quickfile { ... } section

If you want the script to create bank transactions for the payment of wages, set bankTransactions to 'single' (one entry for the total net wages) or 'split' (one entry for each employee), and if your main current account is not nominal code 1200 then edit the bankNominal appropriately. If you do not want the script to create bank transactions then set it to banktransactions = 'none', in which case the bankNominal setting is ignored.

Finally set periodType to 'W' if you run your payrolls weekly/fortnightly/four-weekly, or 'M' if monthly.

On the MyPAYE side you need to:

• Go to "Employer" -> "Integration"
• Tick "Enable XML Web Service Access to MyPAYE", click "update"
• Copy the "XML Web Access ID" as the linkId setting in config.groovy
• Copy the "XML Web Access Password" as the linkPwd

Also set the taxYear setting appropritately. The final key parameter payrollId is not something that can be found in the MyPAYE web interface, we must use the ListPayrolls.groovy script to find it. Save the current version of config.groovy, then open a terminal/command prompt and run

groovy ListPayrolls.groovy

This will print a list of your MyPAYE payrolls (there is probably only one of them) along with the payroll ID for each - copy the relevant ID to the payrollId setting in config.groovy and save it again. You are now ready to process your payrolls.

Nominal codes

The ProcessPayroll.groovy script has a few hard-coded nominal codes which need to match the ones you are using in MyPAYE for certain things to be picked up correctly. In MyPAYE go to "Employer" -> "Nominal Codes" and make sure you have the following settings:

• Default payroll cost 7000
• Employer's NIC cost 7006
• Employer pension contribution cost 7007
• PAYE due & NIC due both 2210
• Default net due 2220
• Pension payments due 2230

If you use different codes for any of these in your QuickFile you will need to edit ProcessPayroll.groovy appropriately, and change them in MyPAYE to match.

Regular usage

With everything set up, regular usage is very simple - on each pay day, after running your payroll, open a command line in the appropriate folder and run

groovy ProcessPayroll.groovy 1

The number argument at the end should be the tax week or month number of the payroll run you want to import into QuickFile. The script will pull the payroll data from MyPAYE and construct a journal for QuickFile. It will print the journal details for you to double-check, if you're happy with it then enter y and the journal (and optionally the bank transactions) will be pushed into QuickFile.

If you run the payroll before payday then note that while it is possible to create journals in QuickFile that are dated in the future it is not possible to create future-dated bank transactions - if you have bankTransactions = 'none' then you can run the ProcessPayroll script at any time after running the payroll in MyPAYE and it will create a forward-dated journal for the pay date, but if the script is creating transactions then it can only be run on or after the actual pay date.

config.groovy

quickfile {
  apiKey = '...'
  appId = '...'
  accountNumber = '...'
  bankTransactions = 'single' // or 'split' or 'none'
  // nominal for your current account, if you want the script
  // to create bank transactions as well as a journal. Ignored
  // if bankTransactions = 'none'
  bankNominal = 1200
  periodType = 'W' // or 'M'
}

mypaye {
  linkId = '...'
  linkPwd = '...'
  
  // This is the internal ID of the payroll in MyPAYE, not the human-readable name.
  // Fill in the rest of this file, then run groovy ListPayrolls.groovy to find the
  // correct ID, and copy it into here.  The value is base64 encoded so be sure to
  // include the trailing '==' if there is one.
  payrollId = '...'
  
  taxYear = "2019/20"
  // mapping of MyPAYE employee ID to employee name, if you want
  // it to appear as "Joe wages" rather than "J C Bloggs wages".
  // If there is no setting for a particular payroll ID then we
  // just use the full employee name.
  employees {
    JB0001 {
      name = "Joe"
    }
  }
}

ListPayrolls.groovy

/*
 * Helper script to determine the "payrollId" parameter - fill in the rest of
 * config.groovy first, then run this script to list your payrolls and copy
 * the relevant ID into the config for use by ProcessPayroll.groovy.
 */
@GrabResolver(name='secure-central', root='https://repo1.maven.org/maven2')
@Grab(group='org.codehaus.groovy.modules.http-builder', module='http-builder', version='0.7.1')
import groovyx.net.http.*
import static groovyx.net.http.ContentType.*

println "Loading config"
def config = new ConfigSlurper().parse(new URL("file:config.groovy"))

// REST client for calling MyPAYE
def mpEndpoint = "https://www.mypaye.co.uk/Secure/AcctLink/"
def httpMP = new RESTClient(mpEndpoint)

def payrolls = httpMP.post(path:"GetPayrollList.asp", contentType:XML, requestContentType:XML, headers:['Content-Type':'application/x-www-form-urlencoded'], body:{
  MyPAYELink {
    Login {
      LinkID(config.mypaye.linkId)
      LinkPwd(config.mypaye.linkPwd)
    }
    Function('GetPayrollList')
  }
})

println String.format('%-20s  %s', 'Payroll name', 'Payroll ID')
payrolls.data.Payroll.each { payroll ->
  println String.format('%-20s  %s', payroll.PayrollName.text(), payroll.PayrollID.text())
}

ProcessPayroll.groovy

/*
 * Main script to process a payroll run and create a journal and (optional)
 * bank payment transactions in QuickFile.  Expects one parameter for the
 * week or month number to process, the rest of the parameters are set in
 * config.groovy.
 */
@GrabResolver(name='secure-central', root='https://repo1.maven.org/maven2')
@Grab(group='org.codehaus.groovy.modules.http-builder', module='http-builder', version='0.7.1')
import groovyx.net.http.*
import static groovyx.net.http.ContentType.*

if(args.size() < 1) {
  println "Usage: groovy ProcessPayroll.groovy <week/month number>"
  System.exit(1)
}

// this is called weekNumber but will work equally well as the month number for
// a monthly payroll
def weekNumber = args[0]

println "Loading config"
def config = new ConfigSlurper().parse(new URL("file:config.groovy"))

def periodName = "${config.quickfile.periodType}${weekNumber}"

// REST client for calling the QuickFile API
def httpQF = new RESTClient("https://api.quickfile.co.uk/xml")
// start with the current time as the initial submission number
def subno = System.currentTimeMillis()
// helper closure for MarkupBuilder to create the header for a QuickFile API
// call with the right auth digest.  Each time this is used it will increment
// the subno so we never send two requests with the same number (this is
// guaranteed to work if you don't run this script or any other that uses the
// same algorithm twice within the same few milliseconds...)
def header = {
  def digest = java.security.MessageDigest.getInstance("MD5")
  digest.update((config.quickfile.accountNumber + config.quickfile.apiKey + subno).getBytes('UTF-8'))
  def md5Val = digest.digest().encodeHex()

  MessageType('Request')
  SubmissionNumber(subno++)
  Authentication {
    AccNumber(config.quickfile.accountNumber)
    MD5Value(md5Val)
    ApplicationID(config.quickfile.appId)
  }
}

// REST client for calling MyPAYE
def mpEndpoint = "https://www.mypaye.co.uk/Secure/AcctLink/"
def httpMP = new RESTClient(mpEndpoint)

println "Calling ${mpEndpoint}GetPaySummary.asp"
// GetPaySummary returns most of the data we need apart from employment
// allowance and pension contributions.  Note that the MyPAYE api is brain
// dead, it requires you to post XML but only works if the Content-Type is set
// to form-urlencoded...
def paySummary = httpMP.post(path:"GetPaySummary.asp", contentType:XML, requestContentType:XML, headers:['Content-Type':'application/x-www-form-urlencoded'], body:{
  MyPAYELink {
    Login {
      LinkID(config.mypaye.linkId)
      LinkPwd(config.mypaye.linkPwd)
    }
    Function('GetPaySummary')
    PayrollID(config.mypaye.payrollId)
    TaxYear(config.mypaye.taxYear)
    TaxPeriod(weekNumber)
  }
})

println "Calling ${mpEndpoint}GetNominalData.asp"
// GetNominalData is supposed to be the complete journal, but I've never been
// able to make sense of how they represent things so I ignore their journal
// and build my own from the pay summary.  However there are a few key things I
// need that are missing from the pay sumary, mainly the pension contributions
// and the employment allowance.
def nominals = httpMP.post(path:"GetNominalData.asp", contentType:XML, requestContentType:XML, headers:['Content-Type':'application/x-www-form-urlencoded'], body:{
  MyPAYELink {
    Login {
      LinkID(config.mypaye.linkId)
      LinkPwd(config.mypaye.linkPwd)
    }
    Function('GetNominalData')
    PayrollID(config.mypaye.payrollId)
    TaxYear(config.mypaye.taxYear)
    TaxPeriod(weekNumber)
  }
})

// extract total numbers from pay summary
def grossPay = paySummary.data.TotalEarnings.text()
def payeTax = paySummary.data.TotalPAYE.text()
def employeeNIC = paySummary.data.TotalEmployeeNIC.text()
def employerNIC = paySummary.data.TotalEmployerNIC.text()
def totalNet = paySummary.data.TotalNetPay.text()

def payDate = paySummary.data.Employee[0].PayDate.text()

// individual employee net pay
def employeePay = []
paySummary.data.Employee.each { emp ->
  def empName = emp.EmployeeName.text()
  if(config.mypaye.employees."${emp.PayrollRef.text()}") {
    empName = config.mypaye.employees."${emp.PayrollRef.text()}".name
  }
  employeePay << [name:empName, amount:emp.NetPay.text()]
}

// check for employment allowance in nominal report
def employmentAllowance = nominals.data.NominalRecords.NominalRecord.find { nom ->
  nom.Description.text().contains('Employment Allowance')
}?.CreditValue.text() ?: '0'

// employer pension contributions
def employerPensions = nominals.data.NominalRecords.NominalRecord.findAll { nom ->
  nom.NominalCode.text() == '7007'
}.sum { nom ->
  (nom.DebitValue.text() ?: '0').toDouble() -
    (nom.CreditValue.text() ?: '0').toDouble()
}

// total pension liability, made up of employer contributions plus employee
// deductions
def totalPensions = nominals.data.NominalRecords.NominalRecord.findAll { nom ->
  nom.NominalCode.text() == '2230'
}.sum { nom ->
  (nom.DebitValue.text() ?: '0').toDouble() -
    (nom.CreditValue.text() ?: '0').toDouble()
}

// start building the journal
def lines = [
  [code:7000, desc:"Gross wages ${periodName}", debit:grossPay]
]
// Employer NI is debit 7006, credit 2210
if(employerNIC.toDouble() > 0) {
  lines << [code:7006, desc:"Employer's NICs ${periodName}", debit:employerNIC]
  lines << [code:2210, desc:"Employer's NICs due ${periodName}", credit:employerNIC]
}
// Employment allowance is the reverse
if(employmentAllowance.toDouble() > 0) {
  lines << [code:2210, desc:"Employer's NICs offset by EA ${periodName}", debit:employmentAllowance]
  lines << [code:7006, desc:"Employer's NICs reduced by EA ${periodName}", credit:employmentAllowance]
}

// deal with pension contributions
if(employerPensions > 0) {
  lines << [code:7007, desc:"Employer pension contributions ${periodName}", debit:String.format('%.2f', employerPensions)]
}
if(employerPensions < 0) {
  lines << [code:7007, desc:"Employer pension contributions refunded ${periodName}", credit:String.format('%.2f', -employerPensions)]
}
if(totalPensions > 0) {
  lines << [code:2230, desc:"Pension contributions refunded ${periodName}", debit:String.format('%.2f', totalPensions)]
}
if(totalPensions < 0) {
  lines << [code:2230, desc:"Pension contributions ${periodName}", credit:String.format('%.2f', -totalPensions)]
}

// Income tax
if(payeTax.toDouble() > 0) {
  lines << [code:2210, desc:"PAYE tax deducted ${periodName}", credit:payeTax]
} else if(payeTax.toDouble() < 0) {
  lines << [code:2210, desc:"PAYE tax refunded ${periodName}", debit:(payeTax - '-')]
}

// Employee NI deductions
if(employeeNIC.toDouble() > 0) {
  lines << [code:2210, desc:"Employee NICs ${periodName}", credit:employeeNIC]
}

def bankTransactions = []
if(totalNet.toDouble() > 0) {
  lines << [code:2220, desc:"Net wages ${periodName}", credit:totalNet]
  if(config.quickfile.bankTransactions == 'single') {
    // create a single outgoing bank transaction for all net wages
    bankTransactions << [code:config.quickfile.bankNominal, desc:"Staff wages ${periodName}", amount:totalNet]
  }
}

if(config.quickfile.bankTransactions == 'split') {
  // create one outgoing bank transaction for each employee
  employeePay.each { emp ->
    if(emp.amount.toDouble() > 0) {
      bankTransactions << [code:config.quickfile.bankNominal, desc:"${emp.name} wages ${periodName}", amount:emp.amount]
    }
  }
}

// else if config.quickfile.bankTransactions == 'none' then don't create any
// txns, assume they'll come in on a feed

// idiot check
def totalCredits = lines.collect { it.credit ? it.credit.replace('.', '').toInteger() : 0 }.sum().div(100)
def totalDebits = lines.collect { it.debit ? it.debit.replace('.', '').toInteger() : 0 }.sum().div(100)

println "Journal to create: Payroll ${periodName}, date ${payDate}"
println ""
println String.format('     %-50s  %15s  %15s', 'Description', 'Debit', 'Credit')
println ""
lines.each { line ->
  println String.format('%4d %-50s  %15s  %15s', line.code, line.desc, line.debit ?: '', line.credit ?: '')
}
println ""
println String.format('     %-50s  %15.2f  %15.2f', 'Totals', totalDebits, totalCredits)

if(bankTransactions) {
  println ""
  println "Bank transactions"
  println ""
  bankTransactions.each { txn ->
    println String.format(' %-50s  %15s', txn.desc, txn.amount)
  }
}

println ""

def check = System.console().readLine("OK? ")
if(check.toLowerCase() == "n") {
  System.exit(0)
}

// create the journal
def createResponse = httpQF.post(contentType:XML, requestContentType:XML, body:{
  mkp.declareNamespace('':'http://www.QuickFile.co.uk') 
  mkp.declareNamespace('xsi':'http://www.w3.org/1999/XMLSchema-instance') 
  Journal_Create('xsi:schemaLocation':'http://www.QuickFile.co.uk http://www.quickfile.co.uk/WebServices/API/Schemas/invoices/Journal_Create.xsd') {
    Header(header)
    Body {
      JournalDate(payDate)
      JournalName("Payroll ${periodName}")
      for(line in lines) {
        JournalLine {
          NominalCode(line.code)
          ItemDescription(line.desc)
          if(line.debit) {
            ItemDebitAccount(line.debit)
          } else if(line.credit) {
            ItemCreditAccount(line.credit)
          }
        }
      }
    }
  }
})
if(createResponse.data.Error.size()) {
  println "Error creating journal"
  createResponse.data.Error.each {
    println it.text()
  }
  System.exit(0)
} else {
  println "Journal created successfully"
}

if(bankTransactions) {
  println "Creating bank transactions"
  def txnsResponse = httpQF.post(contentType:XML, requestContentType:XML, body:{
    mkp.declareNamespace('':'http://www.QuickFile.co.uk') 
    mkp.declareNamespace('xsi':'http://www.w3.org/1999/XMLSchema-instance') 
    Bank_CreateTransaction('xsi:schemaLocation':'http://www.QuickFile.co.uk http://www.quickfile.co.uk/WebServices/API/Schemas/invoices/Bank_CreateTransaction.xsd') {
      Header(header)
      Body {
        for(txn in bankTransactions) {
          Transaction {
            BankNominalCode(txn.code)
            Date(payDate)
            Reference(txn.desc)
            // add leading minus to make it "money out"
            Amount("-${txn.amount}")
          }
        }
      }
    }
  })

  if(txnsResponse.data.Error.size()) {
    println "Error creating transactions"
    txnsResponse.data.Error.each {
      println it.text()
    }
    System.exit(0)
  } else {
    println "Transactions created successfully"
  }
}