Lab 4. 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/CenyAkcji.txt). The following assumptions have to be taken into account:
• The presentation sheet is separate from the raw data sheet.
• Form controls (spin button and list box) are used to select a company for presentation.
• 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 as the chart title.
• The layout of the worksheet should be such that all elements (chart, form controls, elements from tasks 2 &
3) are visible on the screen without the need to scroll the sheet. All auxiliary elements should be hidden.
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 terms of growth rates). In each case the sheet should present the names of 3 companies (along with the correlation coefficients) exhibiting the strongest/weakest correlation.
