General Ledger Transactions Report

1

Dear Developer Team,
I would like to have a request for the developer team to de-merge the data to single cells of the above report for exporting to excel and make it more user friendly for the accountants/auditors to sort or filter after exporting the data.

For example:
Existing Transaction report exported to excel

image
image738Ă—101 8.75 KB

User Friendly report - Demerge the data to single cell for exporting to excel

image
image1244Ă—81 17 KB

All of us will really appreciate for splitting the data to single cell to enable the users to perform sorting, filtering and deleting opening balance or closing balance for generating the report for more than 1 account ledger, at least > 5k transactions per month/year depend on the company transactions volume.

If you can offer us the option to select from “Account 1” to “Account 99”, we will really love you so much. Then we will be able to export “Income Statement” and “Balance Sheet” ledgers separately.

image

Appreciate your consideration on the above request.

Thank you in advance.
Cindy

3 Likes
2

I vote for this. You can do something similar with custom reports but you cannot get opening and closing balances. BTW I think that this format was chosen by @lubos to have a more compact and better paged report.

Rather than this, since I cannot see how you can select “from” and “to” it would be better to have the possibility to multiple select accounts.

3

For a custom report this involves adding greater than / less that operators to the following list.
However Account code is an alphanumeric field so the results may confuse many users

COA Code range

4

In custom report, since he want to separate BS from P&L, he can select “General Ledger Account” “isProfitandLoss” True or False

5

No problem, as long as offer selection, I will be able to generate a few ledger rather than either one by one ledger or “none” for all ledgers.

6

HI Davide,
When I try the custom report, the data did not cater to my needs due to I need detail General Ledger info which custom report did not fulfil such requirements.

Most of the accounting software, provide the users to generate the account ledgers that require to printout, be it multiple selection or by range.

Single account Ledger can be exporting from the summary drill down.
New version report merge the “Transaction type” and “reference number” to single cell again. Hence, after exporting, I have to use excel formula to separate it, additional work for the monthly reporting and checking the missing data for errors. Please note that transaction type is static (meant for filtering) but the reference/invoice number is the unique number meant for tracing. Whenever, your team merged the data for reporting, I will have to demerge after exporting, double work during tight closing period.

Generally, I only have 5 to 7 days for month end closing. Moreover, I am closing for multiple companies with different currencies and thereafter need to consolidate the data to the consolidated accounts.I have already setup several formula in excel to allow the consolidation to be automatic for each month after refreshing the PL, BS and CF.

1 more additional request is please do not add the currency code to the original amount other than “Base Currency”. This is because user also require to sum up the original currency amount to cross check with the base currency amount, especially for GST reporting on quarterly basis.

For my case, I am reporting GST in SGD to the local registrar but the base amount is USD.
Thus, when I am so happy to see the new version finally populate the original amount to the ledger which I wanted to have it similar to other accounting software since 2018, it is the text value again. I am still required to segregate the data and convert it to number in the excel to fulfill my needs. This is not an easy task if the “LEN” is different for each row, manual adjustments are needed for the full “General Ledger Transactions Report”, very time consuming but it is needed to classify each transaction (row of data) to the correct account classification and to the correct corresponding date (Basic requirements for the GL when you are providing the data to the auditor. Otherwise, auditor can’t select random sampling for document vouching. That’s why please add the original amount to the above report, easier to trace the source document)

I really hope that when developer team is creating a report or ledger for exporting to excel, please do not merge the data for multiple fields.

Thank you for your kind consideration.
Cindy

7

Which data is missing since almost everything is exposed?

I’m not part of the development team. The only developer inside this forum is @lubos. Others are forum moderators or users.

I agree with you that for export it is better to have each field separate. From what I know, this is the scope of the new custom reporting tool. As said, it will permit to recreate and personalize all the current “fixed” reports. But it was introduced around one month ago and it’s still under development. So you will have to wait a little bit.

8

use

= NUMBERVALUE( )
9

Hi Patch,
Your recommended excel formula is only workable after I segregate the currency sign with space and number text, not workable for the entire “text” value as the “Amount” field exported to excel become “S$ 4.86”. Each time, I need to use “=mid(cell,4,15)” to segregate the data then convert it to number. Involve 2 formulas to convert into number.

Any other short cut method ?

10

hi cindy

i exported the general ledger transactions and am able to get the data into single cells by using excel add ins like kutools or asap utilities. have you tried these? works much easier.

11

Indeed ASAP utilities can be helpful.

Just did some tests.

Excel in Microsoft 365 (earlier Office 365) on a Mac computer:

  • paste the data you got (by clicking on [Copy to clipboard] in Manager) by using the normal paste-option in Excel. No need to adjust figures.

Excel in Microsoft 365 (earlier Office 365) on a Windows10 computer:

  • paste the data you got (by clicking on [Copy tot clipboard] in Manager) by using the option [Paste Special] in Excel.
    Then there is also no need to adjust figures.

Often the reason for figures to be shown as text lies in the fact that there is a dot used for decimals.
If that is the case you can also use [Replace] from the Excel-ribbon, to change the dot into a comma.

12

Assuming

  • amount in cell A1
  • a currency string prefix
  • Decimal separator “.”
  • Thousands separator “,”
  • For example “S$4.86” or “US$1,234.56”

use

=NUMBERVALUE(RIGHT(A1,LEN(A1)-SEARCH("$",A1,1)),".",",")

Or in LibreOffice you can first simply remove all characters which are not a digit or “.” using a regular expression such as

=NUMBERVALUE(REGEX(A1,"[^0123456789\.]","","g"),".")

or

=NUMBERVALUE(REGEX(A1,"[^\d\.]","","g"),".")

Note in practice you would write this formula once then move or copy it to other locations, that way your spreadsheet program will update the multiple A1 references for you, for example

  • have a copy of the above formula which applies to the cell below
  • copy it to the cell above a column you need the number value
  • move the formula to the column you want the result in
  • copy down
13

Hi Patch,
Thank you for the following formula.
=NUMBERVALUE(RIGHT(A1,LEN(A1)-SEARCH(“$”,A1,1)),“.”,“,”)

This formula is workable for my MSOffice and I add “IFERROR” formula to get rid of blank cell for the summation.

Thank you so much.

Best Regards
Cindy