VBA and Excel

EXCEL TOOLS 2

You’re to turn in a single workbook containing one worksheet for each of the following three problems.

1. Go to www.finance.yahoo.com. Enter symbol “stock” for to get Quotes. Select a stock option you’d prefer. Select Historical Prices and get monthly prices for the stock option selected from Sep 22, 2010 to the current date.

a. Use Yahoo’s DOWNLOAD command to download these data. Open the downloaded file with Excel. Save the file as an Excel file (not csv). Label the worksheet with the stock symbol and the word “download”. For instance, the tab might say “DOW – download”.

b. Format the stock data as a table (Winston 24). Choose a readable form for the table. Save the file.

2. In the file you just saved, insert a new worksheet. Decide on another stock for which you want data.

a. Use finance.yahoo.com and create a Web Query that IMPORTS the data for your second stock that has the same dates as for Problem 1. (Winston 34) Label the worksheet with the stock symbol and the word query. For instance, the tab might say “P&G – query”.

b. Make a table from these data. Save the file.

3. This part uses tab‐delimited file tools2HW.txt. You’re asked to import the file into a worksheet. (Winston 33).

a. Format the data as a table. Choose a readable form for the table (Winston 24).

b. Insert 2 columns after the Sales Rep column. Be sure to select an appropriate cell in the table, and use a table insert option (Winston 24). Use the Text to Columns (Winston 5) command on the Data tab to split the names into last, first, and initial. (Hint: use both comma and space as delimiters.) Put good headers on the 3 columns.

c. Use formatting to show the numbers in the Date column as dates (Winston 6). Change the formats of the last two columns to have a $‐sign, and a comma for thousands.

d. Now insert the following additional data into your table. Each row of data should be inserted so that we maintain the ordering by date. Also, you want to make sure that everything is in the correct column. Be sure to select an appropriate cell in the table, and use a table insert option (Winston 24). Then fill in the data.

14-Jan-12	5001	Rudolf DSL Modem	Fernandez, Carlos	7	181.10	976.40
1-Feb-12	4300	ChromoJet Inkjet Printer	Kelly, Misty	2	1,050.90	3,245.90

J. Hagstrom, Y. Chang, U. Ozdogru, UIC