Open Database Connectivity (OBDC) in Excel

     As promised the following is the information on Open Database Connectivity (OBDC) function in Excel.  As you recall from my last blog we covered how Excel can connect to external data to create reports that can be refreshed or updated with current information.  ODBC complaint databases can be connected to Excel using the Data Connection Wizard or Microsoft Query.
     Microsoft Access can be used to extract data stored in external databases and then the data is transferred to Excel to create reports.  But if your accounting software supports ODBC, then Excel can access your historical data in your general ledger directly without re-keying any data or going
through the typical import-export processes used by most accountants.  The connectivity between your accounting software and Excel requires that you obtain and install the drivers for the software you use and create the interface between Excel and your accounting software.   Once the connect is made then you can import the data into Excel and refresh when the data changes.
    So your probably wondering what's the benefits of this after all most accounting software has report writing capabilities.  The benefit is in the ability to refresh your report with current information with the click of a few buttons.  Dashboard and other reports you create in Excel can be updated automatically.  A lot of internal reports are prepared by updating worksheets of information in Excel.  The information is then pulled to other templates and charts to summarize it.  In a lot of cases the worksheets are updated manually each month, week, etc..  With the ODBC function the information can be refreshed whenever the data changes at the source instead of being manually entered.
     With this connectivity function in Excel it may make sense to use Excel for more of your financial reports.   The advantage to using Excel's reporting tools is that running and modifying reports from enterprise software application or report development software program usually requires that the application be installed.  This can add a considerable burden to support, training and software licensing cost.  In contrast, most companies already have Excel installed and users are often familiar with the basics of how the program works.   Excel reports are also much easier to modify than competing report development software programs and enterprise reporting tools.