IN: How to Convert QuickBooks GSTR1 Reports to GSTR1 Government Utility format?

GSTR1 is a detailed return that has to be filed every month for the outward supplies made in the concerned tax period.

This article will provide detailed steps on how to pull data from QuickBooks to file GSTR1 via the government excel utility.

Step 1: Export the reports listed below from the reports section of QuickBooks in excel format.

a)      GSTR1 - 4. B2B Invoices (New)

b)      GSTR1 - 5. B2C Large Inter-State Invoices (New)

c)      GSTR1 - 6. Exports (New)

d)      GSTR1 - 7. B2C Other Invoices (New)

e)      GSTR1 - 8. Nil rated, Exempted and Non-GST outward supplies (New)

f)       GSTR1 - 12. HSN-wise summary of outward supplies (New)

Step 2: Download the excel macro from the link at the bottom of this article.

Step 3: Open the excel macro file and navigate to the first sheet “RunMacro”.

Step 4: Click on the button “Convert GSTR1 – 4. B2B Invoices” to populate sheet “b2b” of excel macros.

Step 5: Follow the further steps in the sheet to populate the remaining sections. One can click on the “Reset” button to delete all the data from the sheet.

Step 6: After all the sections have been populated in the excel macros, there are some changes which user will have to do manually.

Below are the list of changes required.

Note: Currently section 9B, 10 and 11 are not available in QuickBooks.

Section 4- b2b:

a)      Manual inputs will have to be provided for the mandatory columns Reverse charge and Invoice Type and non-mandatory column E-Commerce GSTIN and Cess for each row.

b)      If any data is missing for other mandatory field i.e. GSTIN/UIN of Recipient, Invoice Number, Invoice date, Invoice value, Place of Supply, Rate and Taxable value then those have to be manually filled by the user.

Note:

a)      Currently line items with 0% GST or 0% IGST rates in invoices and sales receipt are not included in this section and will have to be manually added.

b)      If any of the mandatory line items are missing then delete the entire row of the transaction.

Section 5- b2cl:

a)      Manual inputs will have to be provided for the non-mandatory columns E-Commerce GSTIN and Cess for each row.

b)      If any data is missing for other mandatory field i.e. Invoice Number, Invoice date, Invoice value, Place of Supply, Rate and Taxable value then those have to be manually filled by the user.

c)      Invoice Value, which the sum of the amounts of line items of an invoice in QuickBooks, should be greater than 2.5 lakhs for all the row items in excel macros.

d)      Invoice value has to be same for any row items if they have the same invoice number.

If Invoice value is found to be less than 2.5 lakhs for any row item then the customer needs to check that invoice in QuickBooks and paste the correct invoice value in the excel macros for all the row items concerned with that invoice.

Note:

a)      Currently line items with 0% GST or 0% IGST rates in invoices and sales receipt are not included in this section and will have to be manually added.

b)      If any of the mandatory line items are missing then delete the entire row of the transaction.

Section 6- exp:

a)      Manual inputs will have to be provided for the mandatory columns Port code and Export Type.

b)      If supplies made to SEZ unit/SEZ Developer/Deemed exports are included in this section then those invoices and sales receipts will have to be removed from this section and pasted in Section 4- b2b and place of supply to be chosen as “97-Other Territory”. (Customer GSTIN will have to be manually fetched by the user for each invoice/sales receipt).

c)      If any data is missing for other mandatory field i.e. Invoice Number, Invoice date, Invoice value, Shipping Bill Number, Shipping Bill Date, Rate and Taxable value then those have to be manually filled by the user.

Note:

a)      If any of the mandatory line items are missing then delete the entire row of the transaction.

Section 7- b2cs:

a)      Manual inputs will have to be provided for the mandatory column Type and non-mandatory columns E-Commerce GSTIN (mandatory only if Type is chosen as “E”) and Cess Amount for each row.

b)      If any data is missing for other mandatory field i.e. Place of Supply, Rate and Taxable value then those have to be manually filled by the user.

Note:

a)      Currently line items with 0% GST or 0% IGST rates in invoices and sales receipt are not included in this section and will have to be manually added.

b)      If any of the mandatory line items are missing then delete the entire row of the transaction.

c)      Credit note and/or refund receipt raised against any invoice or sales receipt in the current filing period will have to be manually subtracted from the transaction.

Section 8- exemp:

a)      No changes required in this section.

Note: Credit note and/or refund receipt raised against any invoice or sales receipt in the current filing period will have to be manually subtracted from the transaction.

Section 9B- cdnr and cdnur

a)      Section will have to be manually filled by the users.

Section 11- at and atadj

a)      Section will have to be manually filled by the users

Section 12- hsn

a)      Manual inputs will have to be provided for the mandatory column UQC and non-mandatory columns Description, State/UT Tax Amount and Cess Amount for each row.

b)      If any data is missing for other mandatory columns i.e. HSN, Total Quantity, Total Value and Taxable Value and non-mandatory columns Integrated Tax Amount and Central Tax Amount then those have to be manually filled by the user.

Note:

a)      If any of the mandatory line items are missing then delete the entire row of the transaction.

b)      Credit note and/or refund receipt raised against any invoice or sales receipt in the current filing period will have to be manually subtracted from the transaction.

Section 13- docs

a)      Manual inputs will have to be provided for the non-mandatory column Cancelled if any invoices or sales receipt has been marked as void in QuickBooks.

Step 7: Navigate to “gst.gov.in” from your web browser, click on Downloads section>Offline Tools>Return Offline Tools>Download to download zip folder which has the government utility software.

Step 8: Install the government utility on your computer and run the software.

Step 9: Click on “NEW” under Upload new invoice/other data for return.

Step 10: Fill in all the 6 mandatory fields as stated below:

a)      GST Statement/Returns: GSTR1

b)      GSTIN of Supplier: Company GSTIN.

c)      Financial Year: 2017-18

d)      Tax Period: July

e)      Aggregate Turnover in the preceding Financial Year: Company turnover for FY17. (How to calculate turnover has been given in this http://intuit.me/gstr1).

f)      Aggregate Turnover- April to June, 2017: Company turnover for April 2017 to June 2017 quarter. (How to calculate turnover has been given in this http://intuit.me/gstr1).

Step 11: Click on PROCEED>IMPORT FILES>IMPORT EXCEL (A popup will open up).

Step 12: In the popup navigate to the folder which has the excel macros and select “All Files” from the dropdown in front of the File name option. Select the excel macros file and click “Open”.

Step 13: On the warning popup select “YES”.

Step 14: A success message will be displayed for the successful upload or a warning message will be displayed with the errors in the sheet with a partial or no upload.

Step 15: Once the transactions are successfully uploaded click on VIEW SUMMARY>GENERATE FILE to generate a JSON file to be uploaded on the government website “gst.gov.in”.


NOTE : This is an attempt as a regular QBO user to make life of the fellow users a little easy. If you have any feedback/suggestion/review about the solution feel free to answer/comment on this post.

GSTR1Macro_V5_Mac.xlsm
GSTR1Macro_V4.xlsm

No answers have been posted

More Actions

People come to QuickBooks Learn & Support for help and answers—we want to let them know that we're here to listen and share our knowledge. We do that with the style and format of our responses. Here are five guidelines:

  1. Keep it conversational. When answering questions, write like you speak. Imagine you're explaining something to a trusted friend, using simple, everyday language. Avoid jargon and technical terms when possible. When no other word will do, explain technical terms in plain English.
  2. Be clear and state the answer right up front. Ask yourself what specific information the person really needs and then provide it. Stick to the topic and avoid unnecessary details. Break information down into a numbered or bulleted list and highlight the most important details in bold.
  3. Be concise. Aim for no more than two short sentences in a paragraph, and try to keep paragraphs to two lines. A wall of text can look intimidating and many won't read it, so break it up. It's okay to link to other resources for more details, but avoid giving answers that contain little more than a link.
  4. Be a good listener. When people post very general questions, take a second to try to understand what they're really looking for. Then, provide a response that guides them to the best possible outcome.
  5. Be encouraging and positive. Look for ways to eliminate uncertainty by anticipating people's concerns. Make it apparent that we really like helping them achieve positive outcomes.

Select a file to attach:

Qb community
Looking for advice from other business owners?

Visit our QuickBooks Community site.