Close

How to embed data into Excel Sheet

To embed data into Microsoft Excel, we can use a feature called Web Queries. You can configure Excel to refresh the data in the background every few minutes, manually, everytime the worksheet opens etc., This is a step-by-step tutorial to embed data from our Monthly Series into Excel. We will have a screencast demo soon.

• In your worksheet, ensure that you have the months for which you want data, in a column.
  
  Note: If you type Jan 2005 in a cell, Excel automatically
  formats it as a date and stores it as 1/1/2005
• Download this Web Query File for CME Butter Price ( US )
• In Excel, choose Data > Import External Data > Import Data from the menu
• Pick the .iqy file you just saved.
• You will prompted to select a cell into which the imported data will go
• When prompted for the start and end dates, pick the cell containing the start month and then the end month

You can configure how often data is refreshed, whether to preserve cell formatting, maintain cell widths etc., by picking the menu option Data > Import External Data > Data Parameters. If you check the option for refreshing data on opening the Excel sheet, you will be prompted while Excel starts if you wanted to get data from the web. You may also enable the External Data Toolbox from the View menu to help identify if your data is dynamic.

If you are using Office for Mac, remember to Clear the checkbox for 1904 Date System in Excel Preferences > Calculation. For more details, see this Microsoft Knowledge Base article.