I am wishing to develop a suite of scripts to automate the production of Accounts in the format required by the clients regulator, as well as the preparation of iXBRL files for HMRC. For this, I will need API access to extract the trial balance. Later on, as development progresses, I may need to access individual transactions in the nominal ledger.
First things first, please advise on the correct API call to extract the trial balance in respect of a period selected by the operator at run time.
We don’t have an endpoint to pull data from the Trial Balance report at this time. However, I will raise this internally as a suggested enhancement for the API.
Hi @QFMathew , thank you indeed for your very prompt reply.
It would be very useful to have such and endpoint developed, I’m surprised that it is not already there. If accepted as a development, could you give me an idea as to the duration before it is available.
There is no panic here, this is bookmarked for me as a future development for my client, but I will make it as general purpose as possible. I plan to use a local configuration file, probably an excel workbook, to contain the mapping, pagenation and tagging rules. This is how I have got it running at the moment using the TB’s exported from Quickfile in CSV format. It would be so much cleaner though just to spin up the process and just select the year end date and the location of the mapping rules.
The endpoint, from my perspective, could be a clone of the TB. That is to say, to ask the To and From Dates and then return Account Code, Description, Opening Balance Debits and Credits, Net Movement Debits and Credits and then Closing Balance Debits and Credits. Or if simpler, just a net signed amount for Opening, Net and Closing with the usual convention of Credits signed as -ve.
Does this sound feasible to you. I’d really appreciate your feedback.
As soon as anything is confirmed, we’ll let you know. However, we can’t make any guarantees unfortunately.
You could, in theory, use the balance sheet report which would give you the balances at a certain point of time. This would be two calls, but it would give you the balances at your start date, balances at the end date, and then you would have to calculate the changes in between. But that essentially makes up the trial balance report.
I agree, anything to make life easier for anyone is always a win As I said, we can’t promise or guarantee that will this be implemented, but it’s been fed back to the development team, so all I can suggest is watch this space for now.
@QFMathew The Report_BalanceSheet and Report_ProfitAndLoss endpoints are totally fine for what I need to do. After a bit of faffing (I forgot how to correctly compose the authentication key) I have developed class objects to make the necessary API Calls, to return the Previous and Current Balance Sheet values from the From and To dates and to extract the P&L Movements. The rest is at my end, a Mapping File to present the data into the correct headings on the financial statements, and then all the iXBRL stuff - although I need to have a discussion with HMRC on submission protocols. I’m expecting that to be the hardest part of the whole excersise.
Anyway, I digress, as always the advice that you have given has been spot on. Many thanks and best wishes to you for Christmas and the New Year
Well I have made it work, but pulling down the BalanceSheets and P&L reports in this way creates some interesting ‘fudge’ when reporting on the reserves side of the Balance Sheet. This is because the Year End Journals will have been processed in Quickfile whereas when bare Trial Balances are extracted, then they have not. The process requires less fudge when dealing with bare Trial Balances, but from the operators perspective, the program pulling the data through the API is clearly more convenient and less prone to errors.
To cut to the chase - I am keeping my fingers crossed that the Gods of Quickfile will decide that having an API to retrieve a trial balance, using the same parameters and providing same output as the csv, is something that they want to do.
With many thanks and best wishes for Christmas and the New Year
As I said, we can’t promise or guarantee that will this be implemented, but it’s been fed back to the development team, so all I can suggest is watch this space for now.