You are the assistant publicist for a best-selling international author who wrote an adventure…
You are the assistant publicist for a best-selling international author who wrote an adventure novel about an improbable around-the-world trip. To promote her new novel, she will leave on a 20-cities-in-30-days book tour that matches the travels her characters had in the book. Her publisher sent a tab-delimited file containing her itinerary. You need to create an Excel workbook that displays current exchange rates for the cities on the tour. The author needs to refer to this worksheet during the trip, so she needs to be able to update the lookup table whenever she can get an Internet connection.
Open the tab-delimited file e09b1tour.txt as a new Excel workbook. Do not create a connection to the original text file. Save the workbook as e09b1tour_LastnameFirstname in Excel Workbook format. Insert a column to the left of the Currency column, and convert the City and Country data into two columns. Adjust column widths, and rename the worksheet Itinerary & Currency. Insert a new worksheet named Currency Lookup, and use the Existing Connections dialog box to create a Web query to the MSN MoneyCentral Investor Currency Rates to create a table of currencies.
On the Itinerary & Currency worksheet, add columns that will calculate the equivalent of $10, $50, and $100 (U.S. dollars) in the local currencies for each stop. Use a lookup function to get the current exchange rate for that country from the Web query table on the Currency Lookup worksheet. Nest the CONCATENATE function inside the lookup function to concatenate the currency label with in US Dollars to find exact matches with labels in the Currency Lookup table, and calculate the U.S. dollar equivalents. Use Format Painter to copy formats from the column labels in the imported Currency Lookup worksheet to the column and row labels in the Itinerary & Currency worksheet. Format the values in both worksheets. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet. Save and close the workbook, and submit based on your instructor’s directions.
