The data format that comes out of our connector can be seen here:
Quickbooks Online Extracted Data Format.xlsx
If you can not find a table that you need, please contact us. We are open to customizing our code to include more tables for you.
You will get access to this data through a database (Azure SQL Server or Big Query). This could be a database owned by you or Vidi Corp can host the data for you. For more information about installing the Power BI connector, please visit this link.
This table returns the profit and loss statement. The data is broken down by day rather than aggregated for a time period. The statement can be broken down to transaction level and is available on both cash and accrual basis.
No need to reconstruct this statement with formulas!
| Column Name | Description |
| Date_value | Date of transaction |
| Amount_value | Amount of the transaction in your default QBO currency |
| Balance_value | Cumulative amount of the balance on the specific account |
| Memo/Description_value | Description of the transaction |
| Split_value | Description of the transaction |
| Transaction Type_id | Id of transaction type |
| Transaction Type_value | Transaction Type Description |
| lv0_value | The first layer in the hierarchy of your P&L statement.The values will be the same as they are in your Quickbooks. Usually values are “Ordinary Income/Expenses” and “Other Income/Expenses” |
| lv1_value | The second layer of your P&L statement.The values will be the same as they are in your Quickbooks. Usually values are “Income” ,”Expenses” ,“Other Income”, “Other Income” |
| lv2_value | The second layer of your P&L statement.The values will be the same as they are in your Quickbooks |
| lv3_value | The second layer of your P&L statement.The values will be the same as they are in your Quickbooks |
| accounting_method | Cash vs Accrual |
| client_id | Name of your quickbooks account or accounts |
If your P&L statement has more than 3 layers of hierarchy, those will be automatically captured by the connector.
CONTACT US TO GET THIS SOLUTION
Same as P&L statement, this table returns the data broken down by day. It also groups individual transactions to balance sheet accounts mirroring the hierarchy of accounts in your Quickbooks Online. The data is available both on cash and accrual basis.
| Column Name | Description |
| Date_value | Date of transaction |
| Amount_value | Amount of the transaction in your default QBO currency |
| Balance_value | Cumulative amount of the balance on the specific account |
| Memo/Description_value | Description of the transaction |
| Debit_value | Debit balance of a particular transaction |
| Credit_value | Credit balance of a particular transaction |
| Split_value | Description of the transaction |
| Transaction Type_id | Id of transaction type |
| Transaction Type_value | Transaction Type Description |
| lv0_value | The first layer in the hierarchy of your Balance Sheet statement.The values will be the same as they are in your Quickbooks. Usually values are “Assets” and “Liabilities and Equity” |
| lv1_value | The second layer of your Balance Sheet statement.The values will be the same as they are in your Quickbooks. Usually values are “Assets” ,”Liabilities” ,“Equity” |
| lv2_value | The second layer of your Balance Sheet statement.The values will be the same as they are in your Quickbooks |
| lv3_value | The second layer of your Balance Sheet statement.The values will be the same as they are in your Quickbooks |
| accounting_method | Cash vs Accrual |
| client_id | Name of your quickbooks account or accounts |
This table returns cash flow activity by account. Please note that it is possible to add “Class” column to this table upon request.
| Column Name | Description |
| r_date | Date of the report extract |
| Total | Amount of the transaction in your default QBO currency |
| lv0_value | The first layer in the hierarchy of your Cash Flow statement. Usually the values are : Financing activities, Investing activities, Operating activities |
| lv1_value | The second layer of your Cash Flow statement. |
| lv2_value | The second layer of your Cash Flow statement.The values will be the same as they are in your Quickbooks |
| client_id | Name of your quickbooks account or accounts |
This table shows how much particular customers owe you and how long the payment was outstanding for.
| Column Name | Description |
| report_date | Date of the report extract |
| Cust_name | ID of a customer |
| cust_name_value | Name of a customer |
| attr_value | Shows how long the payment is outstanding for. The possible values are “Current”, “1-30 days”, “31-60 days”, “61-90 days”, “90+ days” |
| txtn_type | The type of a transaction. It could be “Deposit”, “Invoice”, “Expense”, “Journal Entry”, “Payment” |
| Subt_home_amount_value | The amount outstanding |
| client_id | Name of your quickbooks account or accounts |
CONTACT US TO SEE OUR QUICKBOOKS CONNECTOR IN ACTION
If you have multiple budgets in your company, we would be able to extract data from all of them. Our connector can extract the budget table below. This table can be joined with “Budget Detail” using ID.
| Column Name | Description |
| Budget_entry_type | Monthly/yearly/etc |
| End_date | The end date of the budget |
| Start_date | The start date of the budget |
| Budget_type | Profit and Loss/ Balance Sheet/etc |
| Active | True/False |
| ID | Budget ID |
| Client ID | Name of your Quickbooks Account |
Budget detail contains the actual budget numbers. It can be joined with
| Column Name | Description |
| Budget_date | Date of the budgeted amount |
| AccountRef_name | Name of account for which you have the budget |
| AccountRef_value | Account ID |
| Budget_id | The id of the budget |
| Amount | The budgeted amount |
| client_id | Name of your quickbooks account or accounts |
This table groups sales by customer so that you can analyse how much each customer spent with you.
| Column Name | Description |
| Accounting_method | Cash or Accrual |
| Value | Amount of the transaction in your default QBO currency |
| Customer_id | ID of a customer |
| Customer Name | Name of a customer |
| r_date | Date of the transaction |
This table shows the chart of accounts including the five basic account types: asset, liability, income, expense, and equity. This table shows the account balances as of the last data refresh.
| Column Name | Description |
| AccountSubType | Account IDs are grouped into sub types |
| AccountType | Account types including Assets, Liabilities, Equity, Expenses, etc. This is usually the first layer of the hierarchy of your financial statements. |
| Active | True/False flag for whether an account is currently used. |
| Classification | This is the classification of accounts into the 5 basic account types like asset, liability, income, expense, and equity. The classification is blank for non-posting accounts. |
| CurrencyRef_name | Currency name e.g. United States Dollar |
| CurrencyRef_value | Short currency name e.g. USD |
| CurrentBalance | The balance of an account as of now |
| CurrentBalanceWithSubAccounts | Specifies the cumulative balance amount for the current Account and all its sub-accounts. |
| Description | Description of your accounts |
| FullyQualifiedName | The fully qualified name prepends the topmost parent, followed by each subaccount separated by colons and takes the form of Parent:Account1:SubAccount1:SubAccount2. This is limited to 5 levels. |
| Id | ID of the account |
| MetaData_CreateTime | The time that an account was first created for your company |
| MetaData_LastUpdatedTime | The time when an account was last updated for your company |
| Name | Name of the account |
| ParentRef_value | ID of the parent account |
| SubAccount | True/False flag to show whether an account is a sub account |
| SyncToken | Version number of the object |
| client_id | The name of the Quickbooks Online account |
| domain | The value is always “QBO” |
| AcctNum | The 5 digit number of an account |
This table contains all the information you have about customers inside of your Quickbooks Online account.
| Column Name | Description |
| Active | True/False |
| AlternatePhone_FreeFormNumber | The phone number of your customers |
| Balance | Specifies the open balance amount or the amount unpaid by the customer. |
| BalanceWithJobs | Cumulative open balance amount for the Customer (or Job) and all its sub-jobs |
| BillAddr_City | The city inside of the billing address of a customer |
| BillAddr_Country | The country inside of the billing address of a customer |
| BillAddr_CountrySubDivisionCode | The country subdivision code from the billing address of a customer. This could be the 2 letter code referring to a US state. |
| BillAddr_Id | ID of a billing address |
| BillAddr_Line1 | The first line of the billing address |
| BillAddr_Line2 | The second line of the billing address |
| BillAddr_Line3 | The third line of the billing address |
| BillAddr_Line4 | The fourth line of the billing address |
| BillAddr_PostalCode | The postal code of the billing address |
| BillWithParent | True/False for whether a customer is billed with their parent |
| CompanyName | Name of your customer |
| CurrencyRef_name | Name of the currency in which they are billed e.g. United States Dollar |
| CurrencyRef_value | Abbreviation of the currency in which they are billed e.g. USD |
| DefaultTaxCodeRef_value | Tax code set by the system |
| DisplayName | The name of your customer displayed in QBO |
| FamilyName | The family name of your customers |
| Fax_FreeFormNumber | Fax number |
| FullyQualifiedName | Takes the form of Customer:Job:Sub-job. System generated. |
| GivenName | First name of your customer |
| Id | ID of your customer |
| Job | Defines whether the object is a parent customer or nested sub-customer/job. |
| MetaData_CreateTime | The time your customer was created |
| MetaData_LastUpdatedTime | The time your customer was last updated |
| MiddleName | Middle name of your customer |
| Mobile_FreeFormNumber | Mobile number of your customer |
| Notes | Any notes you left about your customer |
| PaymentMethodRef_value | Payment method of your customer |
| PreferredDeliveryMethod | Preferred Delivery Method of your customer |
| PrimaryEmailAddr_Address | Primary email address of your customer |
| WebAddr_URI | Website of your customer |
| client_id | The name of your QBO account |
| domain | The value is always “QBO” |
| ParentRef_value | ID of the parent customer |
This table analyses the invoices that you prepared in Quickbooks online. Those are the invoices you would send to your customers rather than the invoices that you receive.
| Column Name | Description |
| AllowIPNPayment | True/False |
| AllowOnlineACHPayment | True/False |
| AllowOnlineCreditCardPayment | True/False |
| AllowOnlinePayment | True/False |
| ApplyTaxAfterDiscount | If false or null, calculate the sales tax first, and then apply the discount. If true, subtract the discount first and then calculate the sales tax. |
| Balance | The current outstanding balance per invoice. If 0, the invoice is fully paid |
| BillAddr_City | City of the billing address |
| BillAddr_Country | Country of the billing address |
| BillAddr_CountrySubDivisionCode | 2 letter code of the sub division like a US state of the billing address |
| BillAddr_Id | ID of the billing address |
| BillAddr_Line1 | First line of the billing address |
| BillAddr_Line2 | Second line of the billing address |
| BillAddr_Line3 | Third line of the billing address |
| BillAddr_Line4 | Fourth line of the billing address |
| BillAddr_PostalCode | The postal code |
| BillEmail_Address | The email address of the billing address |
| CurrencyRef_name | The currency name e.g. United State Dollars |
| CurrencyRef_value | The currency code e.g. USD |
| CustomerMemo_value | The memo to a customer sent on an invoice |
| CustomerRef_name | Reference to a customer or job by name |
| CustomerRef_value | Reference to a customer or job by ID |
| DeliveryInfo_DeliveryErrorType | If there was an error with delivering the invoice over email, this column will tell you the error message |
| DeliveryInfo_DeliveryTime | Time of invoice delivery |
| DeliveryInfo_DeliveryType | Type of invoice delivery |
| DocNumber | The doc number of the invoice |
| DueDate | The due date of the invoice |
| EInvoiceStatus | Sent/Paid/Viewed/etc |
| EmailStatus | Sent/NotSent |
| Id | ID of the invoice |
| MetaData_CreateTime | Time when invoice was first created |
| MetaData_LastUpdatedTime | Time when the invoice was last updated |
| PrintStatus | NeedToPrint/etc |
| PrivateNote | User entered, organization-private note about the transaction. This note does not appear on the invoice to the customer |
| SalesTermRef_name | E.g. Net30, Net7, etc |
| SalesTermRef_value | Id of the sales term |
| ShipAddr_City | Shipping address city |
| ShipAddr_Country | Shipping address country |
| ShipAddr_CountrySubDivisionCode | Shipping address state |
| ShipAddr_Id | Shipping address id |
| ShipAddr_Line1 | First line of the shipping address |
| ShipAddr_Line2 | Second line of the shipping address |
| ShipAddr_Line3 | Third line of the shipping address |
| ShipAddr_Line4 | Fourth line of the shipping address |
| ShipAddr_Line5 | Fifth line of the shipping address |
| ShipAddr_PostalCode | Postal code of the shipping address |
| ShipDate | Shipping date |
| TotalAmt | Amount of the invoice |
| TxnDate | This date provides information for taxes charged on the transaction as a whole |
| TxnTaxDetail_TotalTax | Total amount of tax on a transaction |
| TxnTaxDetail_TxnTaxCodeRef_name | Tax code name |
| TxnTaxDetail_TxnTaxCodeRef_value | Tax code id |
| client_id | The name of your QBO account |
Some customers add custom fields to their invoices. If you added custom fields to invoices, you will find them in this table
| Column Name | Description |
| DefinitionId | Unique identifier of the CustomFieldDefinition |
| Invoice_id | The ID of the invoice |
| Name | The name of the custom field |
| Type | Data type which is usually text |
| client_id | The name of your QBO account |
| StringValue | The value of an invoice custom field |
If you haven’t found the table or a column that you need for your project, please contact us. We are open to customising our software based on the specific requests of our clients.
