R Language – Dataexponent.com

Cedarstone to Aplos

The process of moving accounting data thru the LittleGreenLight and Cedarstone Reporting System to Aplos is pretty amazing. These back-office partners consolidate all the donations from multiple credit card companies, multiple ACH sources, multiple EFT sources, and old fashion checks. Additionally, one-off donations from other sources come in on a rare, but regular basis.

The big trick is to process the input donation information into standard donation “sets” that include the donation, the donation processing fee, and the credit card fee (if the donation was made by credit card). This means that each donation set will be made of either two or three journal entries, each of which is made of four components (as opposed to two components for double-entry booking).

These journal entries are in a standardized format so that all sorts of quality checks can be automated down the line. For example, duplicate journal entries can be detected (which was an Aplos user interface problem for about a year) and accidentally changed or deleted journal entries can be detected. Of course, there are all sorts of other accounting errors that can crop up but having the ability to check for a consistent input is important.

From all the input sources, and considering the standardized format, the two Aplos donation input files are created. The Aplos Journal Import file and the Aplos Input Contribution files are in totally different formats and imported into Aplos in totally different ways.

The Shiny R-Language applications are starting to look alike but the internals are highly conserved so that is not a surprise.

In older posts I had links to try the different Shiny R-Language software out. Those applications were simple file converters. It’s probably not a good idea to do that anymore.

Aplos Details

After over a year of development and iterating with everyone it is time to share the Shiny R-Language application that makes Aplos useful to everyone, no matter how accounting savvy they are.

Aplos is an amazing tool but it is complicated. Just moving everyone up to the complexities of Fund Accounting is a huge jump. Getting everyone to understand the complex, yet limited, Aplos reports was too much. So, a simplified, integrated tool was needed.

The “Aplos Details” tool does a couple things; the most important is that it reduces the fund accounting world to checkbook-like report – a simple list of transactions by date with a running balance. What a difference this has made!

The next most important thing this tool does is give all the users access to their contacts. Aplos exposes all contacts to all accounts to everyone all the time. This is obviously not proper, and perhaps not legal. Therefore, the “Aplos Details” tool is vital for users anywhere data sharing laws or standards are in force. Without the tool there is no way that users could get access to their donor information.

Finally, the tool combines contact and transaction information to produce an old-style report that shows donor giving information over time to allow everyone to see how a donor has given in the past. This report has been part of typical donor management tools for decades. It’s a bit of a mystery why Aplos doesn’t include this as a standard report .

Additionally, the “Aplos Details” tool has some administrative features built in that allows us administrators to manage donation “sets” rather than individual Aplos transactions. This is really helpful when notes have to be added to the record or donation sets moved between funds. Further, managing donation sets allows the tool to run several quality assurance tests on the financial database ensuring simple mistakes are not made accidentally.

Behind the scenes this application uses just about everything we can get out of Aplos. API’s are interrogated, standard Aplos reports are digested, uploaded, downloaded, and converted to and from XLSX files, and our accounting information is combined in useful ways that Aplos never envisioned.

Aplos Fund Accounting Review

The last couple months have been focused on building tools to make the Aplos Fund Accounting software do a couple basic things that it is simply not designed to do.

It’s funny, three simple expectations that have been around since the inception of computerized accounting are glossed over in the design of the Aplos system. It’s not that these three things are “broken” or “not implemented well”. They are not even conceptually part of the Aplos world. Really, it’s a basic attitude thing.

Aplos is a much better solution for our growing administrative function than what we had before so there is no serious talk of going back. Aplos is good stuff, but as an older engineer, I’m rather surprised by how many of the hard-won lessons of the past have simply been ignored by the young ones building the Aplos system. Also, I’m surprised that such a progressive shop can actually believe they have accounting figured out for the entire world for all time. That a bit scary as I write that down…

Getting your accounting data out of the system. Aplos is like the famous Signetics 25120 WOM (Write-Only-Memory) and it’s technical predecessor, the Umac 606 Phantasatron. While these two humorously fictional devices are truly classic engineering insider jokes the humor is lost in this case. No matter what the marketing folks, tech support team, or the developers themselves say it just isn’t possible to get all the data back. If your accounting system workflow is exactly the same as the way the Aplos designers imagine in their perfect world for everyone in the entire universe it might be possible to get your data out of the system, but I’m not sure because we’re not even close to that work flow conceptualization. In any case, it’s not the raw data. This is most true of the non-accounting data.
The only report output from the Aplos system is what the designers a priory provided. There is no report building capability. In a way it’s related to the first issue. Reports that have been around since the beginning of time are not available, just the new ones the clever Aplos developers have come up with that are not very helpful to our clients.
I know from working with the Aplos APIs that full date and time is recorded behind the scenes but in the Aplos world everything happens in one time zone – the time zone of the data-entering computer. We’re a world-spanning organization and when a person enters data on the other side of the International Date Line it’s tomorrow’s entry even if we are working simultaneously. I almost laughed when a senior Aplos staff explained we were not technically collaborating in real time; my partner was working in my future.

Welcome to the 80’s!

Time to build our own software to get our data back.

Tool to Calculate the Aplos Journal Entries to Replicate the QuickBooks Credit Card Fees

The next step in the process is to add the credit card fees that need to be charged to each donation. These charges are accessible in the QuickBooks Journal Report.

The QuickBooks Journal Report is an MS Excel spreadsheet that is nice for human consumption. But, there does not seem to be any QuickBooks provision for getting a nice clean machine-readable format so we work with what we have.

So, the real work of this “module” is building a QuickBooks Journal Report decoder. The rest of this application has already been built and is simply being reused. In fact, the human interface is starting to become a bit standard…

The workflow is pretty much the same as well…

Download the QuickBooks Journal Report Report from QuickBooks Online (a horrible experience for all concerned!)
Upload this report into the “R” Language Shiny webpage.
Check the QuickBooks Journal Report for errors and process it to build the Aplos Journal file.
Download the Aplos Journal file.
Upload the Aplos Journal file into Aplos.

Here is the link to the “R” Language Shiny Webpage that runs the application. Feel free to give it a try – of course you will need data so send me a note if you are interested.

LGL Giving Report to Aplos Import Contributions Conversion Tool

Continuing to build up the accounting data set it is now time to start to move donation data to Aplos in real-time.

As a bit of an aside, we contract with a company to handle our front-end donation processing. They work with another company that has a very old-fashioned donation management system called LGL. While it may be old-fashioned it works and we don’t think we can change accounting and donation management at the same time. This isn’t going to be easy but we’re going to have to run with the existing donation management system and use automation to enter donation data into the Aplos system.

In the future I envision this to be the bread-and-butter application for our front-end contractor. This application starts the process of learning how to do this task well.

Download the “LGL Giving” Report from LGL.
Upload this report into the “R” Language Shiny webpage.
Process the report to build the Aplos Manual Contributions Input file.
Download the Aplos Manual Contributions Input file.
Upload the Contributions Input file into Aplos.

Here is the link to the “R” Language Shiny Webpage that runs the application. Feel free to give it a try – of course you will need data so send me a note if you are interested.

Tool to Calculate the Journal Entries Needed to Replicate the QuickBooks Checkbook Entries in the Aplos System.

The next big task is getting all the QuickBooks Checkbook entries entered into the Aplos system.

This is very similar to calculating the Cord Ministries Fees from the Aplos “Detailed Donations by Contact” Report except that this time the data source is the QuickBooks Checkbook output file.

Download the “Checkbook” Report from QuickBooks.
Upload this report into the “R” Language Shiny webpage.
Process the report to build the Aplos journal entries.
Download the Aplos journal entries file.
Upload the journal entries into Aplos.

Here is the link to the “R” Language Shiny Webpage that runs the application. Feel free to give it a try – of course you will need data so send me a note if you are interested.

Calculate Cord Ministries Fees from the Aplos “Detailed Donations by Contact” Report

I’m excited!

This is my first “R” Language task for the new Aplos Fund Accounting system.

The task is relatively simple:

Download the “Detailed Donations by Contact” Report from Aplos.
Upload this report into the “R” Language Shiny webpage.
Process the report to build the Aplos journal entries to Transfer Donations to Funds and/or to Transfer Fees to General Fund.
Download the Aplos journal entries file.
Upload the journal entries into Aplos.

Here is the link to the “R” Language Shiny Webpage that runs the application. Feel free to give it a try – you will need data so contact me if you are really serious.

Aplos: The New Cord Ministries Fund Accounting System

Cord Ministries has outgrown the QuickBooks Online system. There are four primary reasons we’ve come to this conclusion:

We are being pressed to move to a true “Fund Accounting” system, which no matter what QuickBooks says, they are not.
We need to have multiple people working on the accounts at the same time and this is expensive with QuickBooks.
The QuickBooks Online system is old technology that is limiting our work.
There is no integrated donor management system with QuickBooks.

These are pretty severe and non-negotiable reasons but we have a huge time, energy, training, and data investment in QuickBooks so we are hesitant to move to another system.

Aplos says they have everything we need and the look-and-feel of the demo and trial accounts is really nice. Continue reading “Aplos: The New Cord Ministries Fund Accounting System”

RStudio Write Up For Thailand Mapping Project

It’s a pretty big deal to get written up in a blog post!

RStudio, the company that publishes the tools to write software in the “R” computer language chose our little application to share with the “R” programming world.

The Thailand Mapping application worked very well and the Thai authorities were impressed – which was probably the most important part of the project.

“R” Language Software For Cord Ministries Donation Income Investigation

Check out why we wrote the software to investigate Cord Ministries donations.

Since a model is only as good as the data we chose to use the actual giving data for 2017 to investigate different methods for calculating the Cord Ministries administrative charges for our people and projects.

Looking at the actual 2017 giving data in multiple ways is important to help people that are not statisticians get a handle on the facts. Having lots of slider bars gives the investigator the ability to focus in on the data and models in an intuitive way. Continue reading ““R” Language Software For Cord Ministries Donation Income Investigation”