Financial Information Package
Posted on February 22, 2019Developed entirely in Microsoft Excel, the Financial Information Package (FIP) extracts transaction data and monthly totals from Yardi Enterprise‘s Microsoft SQL Server database for comprehensive data analysis.
FIP was designed to streamline monthly financial reporting. It overcomes the limitations of Yardi Enterprise to offer the ability to construct dashboards and generate interactive, collapsable financial statements and worksheets for reporting any level of detail.
Computes balances and net changes for any general ledger account or group of accounts, for any period or range of periods, and for any property or property list using easy Excel functions.
YardiBeginBalance(Property, AccountCode, Period)YardiNetChange(Property, AccountCode, BeginPeriod, EndPeriod)YardiBudgetBeginBalance(Property, AccountCode, Period)YardiBudgetNetChange(Property, AccountCode, BeginPeriod, EndPeriod)
Provides a consistent style to all financial reporting. Rid yourself of the “ransom note” style that occurs naturally when compiling multiple spreadsheets developed independently. Colors, margins, fonts, and scaling are consistent across all worksheets.
FIP is entirely maintenance-free, and has been in production since 2010 with enhancements utilitizing the included features being the only changes. When introduced, production time for monthly financial reporting went from two weeks to one hour, and appearances improved significantly.
OutUvIt – Merge Quickbooks and Quickbooks Online Transaction Exports into a Useful Table
Posted on February 22, 2019Quickbooks and Quickbooks Online both offer the option to export reports to Microsoft Excel. You can choose a range of dates, specify every column in sight, then it all magically appears in Excel all nicely formatted and ready to print. What could be better?
Well, I’m not sure why anyone would export to Excel just to print, when they can already print directly from Quickbooks. Also, don’t try exporting too many records or it won’t complete.
Despite all appearances that Quickbooks makes it very easy to export data, Intuit has always managed to cripple the process just enough to make for a frustrating and often fruitless experience for all but the most basic exports. It has always seemed to me like their goal is for your data to be trapped within Quickbooks, so that they don’t have to fear you switching so easily to another competing (and hopefully more open) product.
When my years of frustration finally got the best of me, I decided that a solution must be developed. OutUvIt is in the form of an Excel addin that prompts for the selection of one or many Quickbooks exports, strips from them the garbage-that-Intuit-includes-to-make-it-look-useful-but-actually-render-it-nearly-useless (headers/page breaks/blank columns/total rows), and merges them into a very useful Excel table (ListObject in VBA) that can be easily sorted, filtered, totaled, or imported into a database. OutUvIt can even identify and remedy transactions that are duplicated across multiple exports.
For those cases where it is necessary to repeatedly import from Quickbooks, OutUvIt allows filename masks pointing to the Quickbooks export files to be saved to allow automation of the import process from a growing selection of files within a specific folder.
Arrivederci, Intuit.