Built-In Office Data Connections (ODC) files in Microsoft Excel 2007

     One of the many Data Tools in Microsoft Excel is the Office Data Connection (ODC) designed to facilitate the re-use of external data connections.   Using this tool one can easily create financial reports that can be updated/refreshed with the click of a few buttons.  Excel has three built-in ODC files.  All three are web queries: 1) MSN Currency Rates, 2) MSN Major Indices, and 3) MSN Stock quotes.   To set-up these built-in ODC files in Excel 2007 open a blank workbook and simply click Data, Get External Data,
 Existing Connections then pick from the menu the built-in ODC file you want to create.  The report will be created automatically except for MSN Stock Quotes which requires that one enters the ticker symbols for the stocks you wish to track.

     So now that you've created and saved your ODC file the biggest benefit is being able to update it.  To refresh the file open the ODC file you created and click the following commands; Data, Refresh All then Refresh.  This series of commands will update your workbook to the most current quotes contained on the web for the stocks, indices or currency rates.  I found this to be a handy tool for tracking, at a glance, my personal stock portfolio and what's happening in the stock market.   The currency report I can see as being useful for someone interested in foreign currency trading or someone who needs daily currency rates for purpose of accounting for FX valuations.

     The ability to connect to external data in Excel saves time because one does not have to recreate a new workbook each time the information needs to be reported.  In my next blog I will cover the subject of ODBC and MS Query.   Excel has the ability to extract data directly from ODBC compliant databases.   Following the process similar to the steps above one can create reports in Excel from information extracted from one's accounting software like Quickbooks, Great Plains Dynamics, SAP, etc. that can be refreshed much the same way the built-in ODC reports covered here.  Look for this information soon.

Best regards,

Andrew Jordan, CPA
248-514-6213