Editing Microsoft Excel (section)

=== Using external data ===
Excel users can access external [[Computer file|data sources]] via Microsoft Office features such as (for example) {{code|.odc}} connections built with the Office Data Connection file format. Excel files themselves may be updated using a Microsoft supplied [[ODBC]] driver.

Excel can accept data in real-time through several programming interfaces, which allow it to communicate with many data sources such as Bloomberg and Reuters (through addins such as [[Power Plus Pro]]).

* DDE: "Dynamic Data Exchange" uses the message passing mechanism in Windows to allow data to flow between Excel and other applications. Although it is easy for users to create such links, programming such links reliably is so difficult that Microsoft, the creators of the system, officially refer to it as "the protocol from hell".<ref name="Newcomer">{{Cite web|url=https://www.drdobbs.com/architecture-and-design/faking-dde-with-private-servers/184409151?pgno=3|title=Faking DDE with Private Servers|first=Joseph M.|last=Newcomer|website=Dr. Dobb's|access-date=February 17, 2023|archive-date=February 25, 2023|archive-url=https://web.archive.org/web/20230225011410/https://www.drdobbs.com/architecture-and-design/faking-dde-with-private-servers/184409151?pgno=3|url-status=live}}</ref> In spite of its many issues DDE remains the most common way for data to reach traders in financial markets.
* Network DDE Extended the protocol to allow spreadsheets on different computers to exchange data. Starting with Windows Vista, Microsoft no longer supports the facility.<ref>{{Cite web|url=https://learn.microsoft.com/en-us/windows/win32/ipc/about-network-dde|title=About Network DDE – Win32 apps|website=learn.microsoft.com|date=January 7, 2021|access-date=February 17, 2023|archive-date=February 17, 2023|archive-url=https://web.archive.org/web/20230217170348/https://learn.microsoft.com/en-us/windows/win32/ipc/about-network-dde|url-status=live}}</ref>
* Real Time Data: RTD although in many ways technically superior to DDE, has been slow to gain acceptance, since it requires non-trivial programming skills, and when first released was neither adequately documented nor supported by the major data vendors.<ref>{{Cite web|url=https://learn.microsoft.com/en-us/office/troubleshoot/excel/set-up-realtimedata-function|title=How to set up and use the RTD function in Excel – Office|website=learn.microsoft.com|date=May 5, 2022|access-date=February 17, 2023|archive-date=February 17, 2023|archive-url=https://web.archive.org/web/20230217170345/https://learn.microsoft.com/en-us/office/troubleshoot/excel/set-up-realtimedata-function|url-status=live}}</ref><ref>
{{cite book|last=DeMarco|first=Jim|title=Pro Excel 2007 VBA|year=2008|publisher=Apress|location=Berkeley, CA|isbn=978-1-59059-957-0|url=https://books.google.com/books?id=gtPQMxO8XuoC&pg=PA225|page=225|quote=External data is accessed through a connection file, such as an Office Data Connection (ODC) file (.odc)}}
</ref>

Alternatively, [[Microsoft Query by Example|Microsoft Query]] provides [[Open Database Connectivity|ODBC-based]] browsing within Microsoft Excel.<ref>
{{cite book|author1=Bullen, Stephen|author2=Bovey, Rob|author3=Green, John|name-list-style=amp|title=Professional Excel Development|year=2009|publisher=Addison-Wesley|location=Upper Saddle River, NJ|isbn=978-0-321-50879-9|url=https://books.google.com/books?id=VnegO0pMYlIC&pg=PA665|page=665|edition=2nd|quote=To create a robust solution, we always have to include some VBA code ...}}
</ref><ref>{{cite web|url=http://proc.isecon.org/2000/602/ISECON.2000.Wehrs.pdf|archive-url=https://web.archive.org/web/20100821074925/http://proc.isecon.org/2000/602/ISECON.2000.Wehrs.pdf|url-status=dead|archive-date=August 21, 2010|title=An Applied DSS Course Using Excel and VBA: IS and/or MS?|first=Wehrs|last=William|year=2000|work=The Proceedings of ISECON (Information System Educator Conference)|page=4|access-date=February 5, 2010|quote=Microsoft Query is a data retrieval tool (i.e. ODBC browser) that can be employed within Excel 97. It allows a user to create and save queries on external relational databases for which an ODBC driver is available.}}
</ref><ref>[http://office.microsoft.com/en-us/excel/HA100996641033.aspx Use Microsoft Query to retrieve external data] {{webarchive|url=https://web.archive.org/web/20100312111200/http://office.microsoft.com/en-us/excel/ha100996641033.aspx |date=March 12, 2010 }}</ref>