TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON 34 Using data sources

 

Using data sources

You have been asked to create a price list, in Canadian dollars, for some of your company’s products. You will need to update it regularly using the current exchange rate. You first need to import your price list, then create a web query to load and maintain the current exchange rate.

Note: The will need the following activities to complete this exercise:

• https://github.com/alcadelina/Excel-
  
  

1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.

https://github.com/alcadelina/Excel-
2. Click Data → From Text/CSV:

3. The Import Data dialog box will open. Navigate to your activity files and select Exercise: Using Data Sources_text, then click the Import button:

4. The Get & Transform Data dialog box will open, displaying the data in the source file. If the selection in the Delimiter drop-down list reads --Custom--, and there is an extra header, click to select the Tab option in the Delimiter drop-down list:

5. With your data properly aligned in the list box, click the Load button:

6. A new worksheet is created and the data from your text file is loaded as a table. The Queries & Connections task pane opens on the right of the screen:

7. Click on the Sheet1 tab to make it the active worksheet. Click to select cell A1, then press Ctrl + C to copy the URL of the exchange rate site:

8. Now click Data → From Web:

9. The From Web dialog box will open. Click to select the URL field, then press Ctrl + V, to paste the URL. Click OK:

10. The Navigator dialog box will open. Click to select Table 0 in the list box on the left, confirm the data is present in the Table View window on the right, then click the Load button:

11. A new worksheet is created and the data from the website is loaded as a table. The query appears under the Exercise: Using Data Sources

12. Hover your cursor over the web query in the Queries & Connections task pane, then click Edit in the table dialog box that appears:

13. The Power Query Editor will launch. Click the filter button to the right of the header of the Country-Currency column, then click to select the Text Filters field and begin typing “Canada”. When Canada-Dollar is the only selected item, click OK:

14. Click the Close and Load command in the Close group of the Home tab:

15. You will see that the Web Query table now only displays the Canada-Dollar row of data from the data set:

16. Click the Sheet2 tab to make it the active sheet, then click to select cell C2. Type the formula “=[@[US Dollars]]*VLOOKUP("Canada-Dollar",Sheet3!A:B,2,FALSE)”, then press the Enter key:

17. The table containing the price list will auto complete the formula for every row in the column:

18. To refresh the data from the website, right-click on the query in the Queries and Connections task pane, then select Refresh:

19. You can now save your changes as Activity 6-2 Complete and close Microsoft Excel 365 to complete the activity.

20. Now, you can check out an example of a completed document in the link below:

https://github.com/alcadelina/Excel-