Lab 5. Presenting data from a large dataset Issues
• Using form controls.
• Functions (among other): INDEX, MATCH, LARGE, SMALL, CORREL, ABS.
Task 1
You should develop a workbook to present stock prices (the database includes prices of 247 companies’ stocks – available at http://www.inforum.uni.lodz.pl/dat/Arkusze/StockPrices.txt). The following assumptions have to be taken into account:
• The presentation sheet is separate from the raw data sheet.
• Form controls are used to select a company for presentation (spin button and/or list box).
• Stock prices of a selected company (only!) are presented in the form of a chart. The name (3-letter code) of the selected company should be shown directly above (or next to) the graph.
• The layout of the worksheet should be such that all elements (chart, form controls, numbers etc) are visible on the screen (without the need to scroll the sheet).
Task 2
Add two option buttons to switch between the levels (in PLN) and the rates of growth (percentage changes to previous period) in the chart.
Task 3
Identify companies whose price stocks have the strongest and the weakest (in absolute terms) correlation with the selected company’s stock price. In each case the sheet should present the names of 3 companies (along with the correlation coefficients) exhibiting the strongest/weakest correlation.