Data-Flow

Welcome to our "BK-Data Flow" subdomain.
This one page website details the data flow in our Bookkeeping Template.
This will be one of the few ZAP related websites that are not filled with more than what you need to know.

This website is for those who want to understand the flow of the data in our Bookkeeping Template in order to get an initial understanding of how a Spreadsheet can be used as a database with unlimited Journals, a General Journal, and an auto-populating General Ledger, Income Statement and Balance Sheet simultaneously.

It's so damn straight forward with only two complex UNION calls and a handful of VIEWS, this should have been published by a clever CPA, the Associations of CPAs, and/or others in the Accounting Industry Decades Ago.

Tax / Financial Accounting starts in "Bookkeeping Software"

Manually download checking, savings and credit card transactions from your bank in CSV, xlsx formats or other delimited formats
Copy and paste that data into our "raw transaction worksheets" where custom mapping has been setup for each account to filter out and organize the data columns needed for your journals just to the right of the data you pasted in.
Copy and paste the auto-filtered and auto-formatted data to the right of your raw data into your Journals for Categorization
Categorization of tranactions in a spreadsheet is far, far faster than anything you've ever done in Quickbooks or another database system. An alternative for memorized transactions exists along with other rapid techniques.

From the Journals, the categorized data automatically flows into the General Ledger, Income Statmeent and Balance Sheet reports with no further work or submit buttons required. Those are all just "views" of the primary data. That's why there is never any lost or corrupt data. This is where the magic happens and you will see how it all happens below.

For Statement Reconciliation and Crosshcheck, a separate worksheet dedicated to statement header informaton is used. Those data tablse are setup to monitor and compare out to the journals in real time at all times and color coding tells you if anything is out of balance at all times.

Pay your income taxes quarterly or annually using this software as your guide with a level of "audit ready confidence" not found in any other digital solution.

Web Page Outline

1) Data Flow from Banks to your Raw Txns Worksheets

2) Data Flow from a Raw Txn Worksheet to an Asset/Liability Journal

Data Flow between Chart of Accounts and Journals for Transaction Categorization Drop Downs

4) Data Flow From Asset/Liability Journals to the TxnAggregator Worksheet

NOTE: The TxnAggregator Worksheet is for prepping single line journals for double entry records. The General Journal data does NOT pass through the TxnAggregator because it is done in double entry format in this system.

5) Data Flow From 1) the TxnAggregator & 2) the General Journal to the General Ledger

6) Data Flow From the General Ledger to the Income Statement

6) Data Flow From the General Ledger to the Balance Sheet

7) Data Cross Check with the Statements Worksheet

8) The Chart of Accounts - For Reference

Summary

1) Data Flow from Banks to your Raw Txns Worksheets

You will go to a "Raw Txns Worksheet" for an account you are seeking to download data for.
You will create a download set header record that specifies the start and end date of the download data you are going to retrieve. (Circle 1 below)

1) On a separate monitor to keep things easy, log into your online banking console for the given account and download data with the desired date range as a csv, xls or some other delimited format.

1. 1. The file you download will be discarded.
  2. It can be stored in downloads or junk.
  3. It is a temporary file

2) Open up that temporary file, highlight those transactions (excluding any records for opening balance) , use ctrl-c to copy them.

3) Then you use Ctrl-shift-v to paste them into the "raw txns worksheet" without overwriting cell formatting so as not to futz with the code that does the row coloring ( Circle 2)

1. Each Bank Account will have different columns and different data formats
2. This data table requires a custom setup on each raw txns worksheet . Sample data from the institution is used to create matching columns

4) As soon as you paste items into the worksheet (Circle 2), a portion of that data is mirrored to the formatted columns on the far right and a txnID is assigned to each record. (Circle 3)

NOTE: In this example the raw data on the left and the mirrored data on the right is in the same order. Only the txnID on the far right is added. It is NOT typically like this. Often times far more data is downloaded and pasted on the left, out of order, but only the Post Date, Payor/Payee and Amount is mirrored to the right.

1. The automatic mirroring is controlled by the dark red cells (Circle 4) . Those cells require an initial configuration. It takes a minute or so to do that once you know how to do it.

The Data is now formatted, indexed and ready to be pasted to the corresponding Journal.
This entire process described above takes about 30-60 seconds once mastered.

2) Data Flow from a Raw Txn Worksheet to an Asset/Liability Journal

Highlight the first row of the formatted data on the right hand side, select ctrl-shift-arrownDown and you will have that entire data set highlighted. Select Ctrl -C to copy
1. That "beige" colored line denotes the start of a new download set and it comes in handy . That formatting function to do that is a little sensative. you must paste data in and move it around on this page with "paste special - values only " or you will futzed up that colored line formatting system (and then you can learn how to fix it!)

Next, click on the Journal Worksheet for the desired account. (Journal shown below)

Sort by column H . (Circle1)
1. This is for quality control.
Put cursor in the first open row below the existing data (Circle 2 - A268 in this case)
Ctrl-Shift-V to paste the data into the Journal without overriding the drop down formatting on the Categorization and ZID Colum (hidden in this screen shot) .
1. Columns A, B, C, and H will be pasted into the Journal .
2. Columns I and J will auto-populate
When you paste in data, the txn count should pickup where the previous left off (Circle 1 - lower arrow)

Data Flow between Chart of Accounts and Journals for Transaction Categorization Drop Downs

Categorizing Txns in a Spreadsheet has huge advantages as compared to a Database.

Categories (aka accounts) can be selected from a drop down tied diredctly to the Chart of Accounts, as expected.

BUT, you can also sort txns by Payee to group items together with previously categorized txns giving it a built in memorized txns function and then drag down the prior categorization to several or dozens of transactions instantly (<< this is the key to the speed)

We also have a system for suggestion categories, but it is only kind of useful given the other functionality.

You will see details on all this in videos. This site is about data flow and believe it or not, at this point in time everything reflects on the Income Statement as soon as a category is assigned, and same with incremental part of the Balance Sheet. That absolute part of the Balance Sheet got the data as soon as it hit the worksheet uncateogorized.

4) Data Flow From Asset/Liability Journals to the TxnAggregator Worksheet

The TxnAggregator worksheet is what we call a "silent worksheet". It's called silent because it does not require any user input and it is not expected to be viewed by the user as part of their regular interaction with the file.

The grey part of this worksheet (circle 2) is being created by a UNION style database call in Circle 1. The data then autopopulates section 3 of the worksheet via the dark red cells in that section much like what transpired on the raw transactions worksheet you learned about above.

In this process, the data on the right is "prepped" to become double entry data. The information for both records is currently contained in a single line of data at this point. Another query that will be made against this data in a moment and it will project each row into two rows as needed for a General Ledger.

In Database Programming lingo, this Data in the grey would be called a VIEW and it was created by the UNION call at Circle 1).

In Database Programming lingo, this Data in the purple would be called a VIEW of a VIEW, and it was created by the dark red cells in the purple section (it's not a single clean queary to create the table as is the case with the grey side).

The UNION call is programmed to receive all the data from each Journal Worksheet and aggregate them into this single data table format. The code for that UNION call is shared below just so you can sense a level of transparency in this system.

IMPORTANT NOTES:

By default the file is setup for 2 asset and 2 liabilities journals. it will follow worksheet name changes on those.
if you add more journals to the worksheet, you need to update this UNION call to include them in this step of the process
Updating this UNION query is as simply as copying a line of code and pasting it back in, then making a reference change to the new sheet name. It takes about 15 seconds.

OTHER IMPORTANT NOTES:

As soon as you pasted the data into the Journal from the RawTxns worksheet, it was mirrored to this transaction aggregator worksheet instnatly and before you categorized anything.
We actually duplicated more data in the purple part of this txn Aggregator Worksheet and this data conversion process than was needed. We did that to make teaching this easier. The "sign flipped" column is an important column to understand. It is driven by the relationship between the Acct1 and Acct2 as shown as a single notation in column P and this is the alternative solution for positive and negative values that replaced the debits and credits process.

NOTE: The TxnAggregator Worksheet is for prepping single line journals for double entry records. The General Journal data does NOT pass through the TxnAggregator because it is done in double entry format in this system.

The left side of the diagram shows One Line Journal Entry handling.

The right side shows General Journal Handling.

All records end up as Double Entry in the General Ledger for reporting purposes.

Below is an image of the General Journal, just for reference.

Notice that columns E and F are hidden in this view.
Notice the button to turn debits and credits on and off

With E and F expanded, and with Debits/Credits on, you can see teh allocation of the positive and negative entries for reference and crosscheck.

5) Data Flow From 1) the TxnAggregator & 2) the General Journal to the General Ledger

Below is a screenshot of the General Ledger. In general you should see both parts of matching transactions on top of each other. That may not be the case for transfers between asset and liabilty accounts, but both parts would need to be there for the Balance Sheet to show in Balance.

The General Ledger is "just" another VIEW created by a complex "UNION" call at Circle 1.

Formula found in Circle 1 above is shown below...

This UNION call done in spreadsheet lingo has 3 components.

It gets the records from the General Journal (A), and then it reads the txn Aggregator twice, first grabbing the record as it was entered in the Journal with the journal account name, and then grabbing generally the same info but the selected category for the transaction and the second amount that may or may not have the switched sign value. (B and C)

NOTE:

The trick here is excluding the second half of transactions for transfers between asset and liability accounts where they both have journals. That is built into the UNION Query criteria and it uses a flag that was set in the txn Aggregator for such purpose.

6) Data Flow From the General Ledger to the Income Statement

Our Income Statement is "special". It does Income Statement functionality as expected, but it also converts to a Cash Flow report and a full txn report with the toggle of an option. This helps a LOT when teaching ppl about the differences in the data that makes up each report. It also has a summary cash delta (cash flow) report that shows at all times. Without this summary report, it's often very confusing for small busienss owners to look at income statements, because things look good, but they have no cash in their account and the forget their owner draws aren't included in an income statement.

To Drill down into the report, there is a section on the same worksheet that allows that.

BUT WHERE DOES THE DATA FOR THE REPORT COME FROM?

You should know by now. It's just another VIEW of the very original Journal data after it was reflected through the txnAggregator and the General Journal into the General Ledger, and then a subset of that data was filtered out based on date and account type to create the Income Statement.

On the far, far right hand side of the Income Statement worksheet, there is a hidden section called "General Ledger Subset"

The start and end date (and optional ZID) for the report is used to create a filtered view of the General Ledger on this worksheet. That data is then summed by account for the Income Statement and it can all be reviewed on a single worksheet.

6) Data Flow From the General Ledger to the Balance Sheet

This is where things get even more interesting. We love this new Balance Sheet in so many ways it's simply to hard to convey.

Unfortunately, we had to appropriately tell everyone the Balance Sheet actually has to have a start date and end date to be generated.

It's a shame that Millions if not Billions were incorrectly taught otherwise.
The Value of every major Accounting Text book should drop significantly from this point on along with a lot of Software as a Service Ventures.

In addition to adding the start date field on the Balance Sheet, we brought Income and Expenses up to the Balance Sheet (as opposed to Net Income) to reduce confusion and to show how the start date was relevant.

We also published all equity accounts on the Balance Sheet instead of using a Change in Equity total. This helps out with Transparency too.

Finally we added a 6th Account Type. We refer to that as "ZAP Special".

Suspended expense accounts (aka Suspense Accounts) are gnarly.
Nobody can see when they are in play on a Balance Sheet.
Any and all Suspense Accounts are now treated as expense accounts as prior, but the account type itself in our system is no longer an expense account.
It's acct type 6 (ZAP Special) and that shows up on the Balance Sheet now.
In this example the value in that represents a starting balance entry. We can use this to explain how money is fabricated out of thin air. Without this on the Balance Sheet it's difficult to understand that concept. With it, it's not.

But wait. There's a LOT More. And it gets better...

With our Balance Sheet your Trial Balance is always adjusting in real time with Journal Entry changes. No submit button or manual report refresh required.

If it's out of balance you can quickly toggle the end date back until you find the day it fell out of balance.
When the date is found, you can expand a section to the right and view just the txns on that date to find out exactly what is missing or out of balance.
It's typically a txn with no categorization or a transfer allocated as an expense, etc. You know what causes those, but you've never had system to find and repair them this fast and slick before.

Even further to the right you can drill down a little more into the G/L Data that makes up the Balance Sheet report. This operates a bit different than the Subset for the Income Statement but the concept is the same. You'll get into that later.

7) Data Cross Check with the Statements Worksheet

The Balance Sheet above must be used in tandem with the Statements Worksheet.

Poop in can be poop out if there is not another cross check on the data.

Our Statements Worksheet enables a user to enter statement Header Data, and then an aggregated reconciliation and real time monitoring of data in the Journals transpires automatically and at all times.

Two Green Columns is always the goal.

We do NOT have a flag on the Balance Sheet to indicate when the Statements worksheet is also in balance or one or more on that sheet are out of Balance.

There's a complex way to do that, but it requires a lot of custom configuration that could lead to false positives and false negatives if incorrect.
We've left this as a two part cross check on data such that it requires human eyes to confirm that both the B/S and the Statements worksheet are in Balance.

Details related to this interaction and the errors that can transpire are all documented in detail in the "Bookkeepers Secrets" book series along with some other interesting history.

8) The Chart of Accounts - For Reference

The Chart of Accounts is just a table of data you create. You control the naming and order. No accounts required and no names forced.

There are some configuration fields that are typically left hidden after setup. (B through G in this image)

Multiple Budgets can be configured on the far right...

and more. Our Transaction Generator is pretty bad ass too. You create master tranasctions with variable paramaters and hit a button and "poof" you get a years worth of simiulated data for exploration lickety split . Only the master transaaction list shown below. The record creation process is better seen in video.

Summary

There's more to this than what was just shared but you just saw every ounce of the data flow.

There is NO PLACE FOR ANY DATA TO GET LOST. IT'S ALL JUST A REFLECTION OF THE JOURNAL DATA TO START WITH.

The Txn Generator enables Life and Business Simulation.

There is an entire scripting component in Google Script (Java Script with Google Objects) that is NOT required for any of the core accounting shown, but is required for the Txn Generator.
That's how we'll teach the world a bit or two about technology and financial simulation too.

And there's other reporting tools and such, but all you should be saying to yourself about now is probably something like...

Holy Shit. Holy, Holy Shit. Oh Holy Shit.

The Process we promote for Bookkeeeping is called "5 Step Bookkeeping." You just saw the gist of it above and how it all works. The diagram below is for reference and should make more sense now than it would have at the top. It could probably get a little better but we're happy with it for now. Everything is a work in process.

If you are a CPA or if you are a Bookkeeper who wants to see what we are asking of CPA's, or if you're neither but interested in the same, see the "For CPAs" link for that on the main menu on our full website at https://www.zap-accounting-software.com .

This should have been produced by CPA's and the industry associations they support many years ago. They claim to have "R and D" budgets for Accounting. If so, no clue how this was missed.
It's only a few database UNION Calls and a few VIEWs of those.
It seems most were too busy making money to focus on a solution that might have helped the rest of us escape the Intuit Grasp.

No matter. It exists now.
Hopefully many are ready to help out with the Education and Distribution process moving forward.

Please visit us at https://www.zap-accounting-software.com and
start with our Orientation Process today.

Seeking ZAP Black Belts for Global Rollout

Page updated

Report abuse