Custom Reports/Full Transaction Data Extract
What I'd love to see is a simple way to export all of my transaction data. It should include every transaction recorded for event registrations, memberships, renewals, donations, refunds, etc. Then I can create my own custom reports with another program (Excel, Access) and all I would need to do is update my report data source with a new daily or weekly export from WA. Alternatively, the ability to custom design my own reports within Wild Apricot would be nice too. For example, using a drag-and-drop interface I could create my own pivot table reports like in Excel.
Started analysis of Financial exports improvements. This will help to resolve problems mentioned in this and number of other related wishlist threads.
We will post all updates in this thread as we have them.
Feel free to add your comments and thoughts
@Charles, thanks for sharing. This makes total sense.
Oleg, Product Designer @ Payments Team
Charles Pena commented
Thanks for the reply. My comments are related to improving the process of event registration exports. My request is mostly centered on building a specific export template for non-supper users to use rather than build their own export grouping each time. We have 3 specific spreadsheets that we export event data by participant;
1. Required airline passenger ticketing information. Format is set by the airline, but most are the same and we capture all the required information.
2. Financial Income transmittal's, by participants to send by spreadsheet to the Treasurer for recording. Very simple: name, amount & item description.
3. Roommate and condo lists, plus descriptions of none standard items purchased.
All of this information can currently be created into a spreadsheet through the export system but it requires a bit of work by me to choose fields to export and then to format the spreadsheet. After creating the process, need the ability to copy this template for later use by less experienced user. So in nutshell, want the ability to create custom exports and name these like you do with custom searches.
@Charles, thanks for your comment and sorry for late reply - I missed it initially.
Could you please elaborate your comment? What do you want to get from this report? Do you want to filter only event's financial data? Or do you want to improve event registrants export?
@Tabatha, thanks for sharing, appreciate this.
Oleg, Product Designer @ Payments Team
I would like to have the ability to export the contacts/members full details also. I use the financials export report to send out membership packets after renewal. As of right now I have to look up each transactions address after I download all the financials.
Charles Pena commented
It is unclear if this improvement is just meant for the overall financial report system or also for specific event financial reporting too. I know you often combine items. We don't use the overall financial reporting system but do extract data by event.
On the data by event financial reports we would like to select by item which items to export, not the whole data stream. Ideally we would like to assign the column order they displayed.
For easy of use, we need to be able to save that custom report export option, so everybody is do it the same and not having to recreate it every time they use it.
Mary Simpson commented
Definitely need reports by invoice type; i.e. for membership, for event, for guest lunch etc. Would be great if we could set up our own Invoice types (again membership, event, guest lunch), then report on those.
I agree. Our club uses a cash accounting system and the existing WA reports do not help us a lot. The Income report looked good, but it shows the date of the invoice NOT the date of payment - which is what we need in a cash accounting system. The Payments report does list the transactions by payment date - but only by tender type NOT what the payment was for. The Payments & Refunds report LOOKS like it would be the answer, but when we download it to Excel it doesn't show what the payments were for. We need to know membership or event etc. and, if an event, which event - so we can work out how much profit (or not) that particular event made. Either include more detail in the downloaded spreadsheet or add a checkbox to the Income report to choose between accrual (invoice date) or cash (payment date). Or both!
Ian Beecher-Jones commented
Not sure where this topic is in the Roadmap, but a full extraction of financial transactions would be great.
The Export from the payments and refunds report is almost there, but it just lacks one vital piece of information - what the transaction is for. On the report on the screen it shows whether the payment was for an event or membership, but it doesn't appear in the xls file during export process - so near yet so far.
Please please can you add the extra column for the export routine - you have it in the report so hopefully it won't take much to do.
I completely relate to your post. I spend hours and hours each month trying to reconcile and get the information we need to complete our monthly financials! It simply isn't available in WA.
I complete agree! The information that is available does not include all the transaction details. We have the same problem in our accounting outside of Wild Apricot. We track each transaction by Name, Company, type (specific event or membership level) and amount. Currently, Wild Apricot only provides a report that says "Origin" but it does not provide the actual event or membership level paid for. This requires us to manually extract the information from the system.
We REALLY need this information for our accounting records and reporting. It takes hours to compile everything that could be solved in a report exported from Wild Apricot.
This request sounds a lot like something I've been trying to get, and no I don't think the export of all payments and invoices does it.
We evaluate the profitability of each of our events -- the export from Finances does not carry the specific event name. I would want to sort by event name.
We reconcile the financials for each event separately so that the Treasurer knows how much revenue is in checks and Paypal for that event, and to be sure member accounts are correct. WA Registration by Type gives us total Revenue for that event, but that doesn't tell us all we need to know.
Currently I export attendee data for each event separately, delete all but relevant financial info, sort it so the event manager can verify the correct amount they have in paper checks so I can be sure our member account balances are correct. Our event managers often settle a member credit for payment incorrectly, but actually record a new payment (using a tender type for member credits thinking that does it). There is no easy way to find these errors (which must be corrected since members get credits they are not supposed to have).
For event reconciliations, I then add back in any cancelation charges for late cancel fees for that event. Finally I add in Paypal fees for all the Online transactions for the event (something I would not expect WA reports to do), but need to sort the transactions/event by payment type to allocate the PP fees to that specific event. Without this information, we don't have complete data for pricing our events for next season.
It is very cumbersome and I spend a lot of time trying to verify member credits are correct, (and helping event managers over the phone settle credits to try to prevent these errors) since the audit trail is not clear for this, especially when a trip leader makes errors on settling credits which is a common occurrence.
We are probably going to dedicate a board member to act as "assistant treasurer" to focus specifically on the new WA billing issues next year to help with all this additional work.
This (export of events with financial data) is pretty specific so I would suggest creating a thread dedicated to this to see if it can get support from other users.
Kyle - now that we have new Finances module you can export all payments and all invoices. Does this achieve what you requested?
I've mentioned this on another post:
but the ability to export a list of events with their financial data would be most helpful.
it basically would allow us to have a quick look at the revenue across different events (we categorise our events using the tags in WA, but there's no way to get those lists downloaded into an exportable format!).
This makes it easy to see if, for example you had a seminar series, whether it was profitable, well attended etc., over time (years, seasons) and / or against other events.
I would appreciate examples of reports you envision you would create in this way.