Quickbooks Profit & Loss API - How Can I Capture Specific Data

[Robert_Payton]

Goal: Create an email for management to show them Total Income and Total Expenses for each department.

My Steps I need help with Step 2
Step 1) Run the Quickbooks Profit & Loss
Step 2) Extract the Summary Total Income & Summary of Total Expense for each department. (example: Total Tech Income and Total Tech Expenses)
Step 3) Utilize that to Create a Daily Email to management showing them the Total Income - Total Expense for each department.

Step 1
Running the Profit & Lost API
Summary: I am currently querying the data based on start_date, end_date and summarize_column_by = Total. *would love to know if there is a better way to get what I need.
Here is what I get for the output, and this is an example of the Tech Income I need.

Step 2:
What is the best next step? I would like to establish the following Variable and if I can learn how to do it for Tech Income, I should be able to figure out the rest (maybe).

I have attempted a lot and after several hours, threw in the towel and need advice from some of the greats in this community.

[Robert_Payton]

I think I made some progress but still need to figure out a little more. Getting it to output to XML makes the data better to work with.

Here is the xml Data

I have gone as far as doing a Text Parser (replace) and used Regex to find and replace all with a comma.

Here is the output

Any suggestion on get this data to export as CC Discount Forfeit, 817.04 or ideally, get them to output as a bundle.

[Robert_Payton]

Here is what I figured out so far. Need to know if there is a better way to do this to save on operations

1. Quickbooks - Running the P&L API * I had to eliminate using summarize_column_by = Total because I couldn’t get the expenses by doing that.

2. Transform to JSON - My first time using JSON

3. Convert JSON to XML - Because it makes more sense to me.

4. Text Parser - Extract everything between

   (.*?)
   
   

5. Markdown - Eliminate HTML
   

6. Text Parser - Add a newline between the text and the number.
   

7. Text Parser - (.)\n(.)
   

[Michaela]

I just want to quickly say congrats on getting this up and running.

@Robert_Payton

Need to know if there is a better way to do this to save on operations

Let’s see if someone from the community has some scenario optimization tips to share.