What is external data source?
External data is data that you link/import into excel from a source that resides outside excel. Examples of external include the following
Data stored in a Microsoft Access database. This could the information from a custom application i.e. Payroll, Point of Sale, Inventory, etc. Data from SQL Server or other database engines i.e. MySQL, Oracle, etc. – This could be information from a custom application From a web site/web service – this could be information from a Web services i.e. currency exchange rates from the internet, stock prices, etc. Text file i.e. CSV, tab separated, etc. – this could be information from a third party application that does not provide direct links. Such data could include bank payments exported to comma separated file CSV, etc. Other types i.e. HTML data, Windows Azure Market Place, etc.
Website(XML data) external data source example
In this example to import XML into Excel, we will assume we are trading the Euro currency and would like to get the exchange rates from the European Central Bank web service. The currency exchange rate API link is http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml
Open a new workbook Click on the DATA tab on the ribbon bar Click on “From Web” button You will get the following window
Enter http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml in the address Click on Go button, you will get the XML data preview Click on Import button when done
You will get the following options dialogue window
Click on OK button You will get the following Excel import XML data
How to Import XML to Excel
Let’s take another example on how to import XML file in Excel, this time you have local XML not in form of web link. You can download XML File below. Download the XML File Here is a step by step process on how to open XML file in Excel: Step 1) Create a new workbook in Excel
Open a new workbook Click on the DATA tab on the ribbon bar Click on “From Other Source”
Step 2) Select the XML as Data source
Then click on “From XML Data Import”
Step 3) Locate and select the XML file
Now select the XML File to Excel sheet
You will get the options dialogue window as above example
Click on OK button You will get the following data
Summary
Excel has powerful features that allow us to analyse numeric data and create visual reports such as charts. You can take advantage of external data importation to create your own custom reports that meet your business reporting requirements.
