Задача «Анализ продаж ценных бумаг»

Условие задачи:

Создать в СУБД Access базу данных для учета реализации ценных бумаг. БД обеспечивает:

1. Ведение нормативно–справочной информации (справочники видов ценных бумаг и эмитентов).

2. Учет сведений о котировке курсов ценных бумаг, объемах предложения и спроса.

Требуется:

1. Данные БД Access передать для анализа в Microsoft Excel.

2. Вычислить итоговые показатели:

   1. Всего предложений каждого эмитента.

   2. Всего предложений каждого вида ЦБ.

   3. Общая стоимость предложений по каждому виду ЦБ – рассчитывается как сумма произведений номинала ЦБ на эмиссию для всех ЦБ одного вида.

   4. Общая стоимость предложений ЦБ эмитента – рассчитывается как сумма произведений номинала ЦБ на эмиссию по всем ЦБ одного эмитента.

   5. Общая стоимость спроса по каждому виду ЦБ – рассчитывается как сумма произведений номинала ЦБ на спрос и на курс ЦБ одного вида.

   6. Общая стоимость спроса на ЦБ эмитента – рассчитывается как сумма произведений номинала ЦБ на спрос и на курс по всем ЦБ одного эмитента.

   7. Общий средний курс ЦБ каждого вида по всем эмитентам.

   8. Общий средний курс всех ЦБ каждого эмитента.

   9. Общая стоимость предложений ЦБ в целом за период (месяц) – рассчитывается как сумма произведений номинала ЦБ на эмиссию по всем видам ЦБ всех эмитентов.

   10. Общая стоимость спроса на ЦБ в целом за период (месяц) – рассчитывается как сумма произведений номинала ЦБ на спрос и курс по всем видам ЦБ всех эмитентов.

3. Графически интерпретировать спрос и предложение по видам ЦБ.

4. Выполнить статистическую обработку данных о котировках ЦБ.

Информационная технология:

1. Создать базу данных СУБД Access – команда меню ФайлСоздать, указать имя и местоположение новой базы данных, например, ЦБ.mdb.

2. Информационно-логическая модель предметной области (ИЛМ) представлена на рис. 29, состав реквизитов информационных объектов (ИО) – в табл. 30, все связи ИО – один ко многим.

Рисунок 29

Таблица 30

 Условные обозначения форматов данных: Т - текстовый (символьный) тип, n - длина текста; D- дата, время; N - числовой тип (допускается формат чисел с фиксированной десятичной точкой).

3. Создать таблицы, имена и структура записей которых соответствуют информационным объектам ИЛМ.

 Таблица Дата не создается.

4. Создать схему данных – команда меню СервисСхема данных – рис. 30.

Рисунок 30

5. Подготовить экранную форму для ввода данных в таблицу ЦБ – команда меню ВставкаФорма, выбрать Автоформа:Ленточная, указать таблицу ЦБ. Автоматически создается форма. Сохранить форму под именем ЦБ.

6. Перейти на вкладку Формы, открыть форму ЦБ, ввести исходные данные – рис. 31.

Рисунок 31

7. Подготовить экранную форму для ввода данных в таблицу Эмитент – команда меню ВставкаФорма, выбрать Автоформа:Ленточная, указать таблицу Эмитент. Автоматически создается форма. Сохранить форму под именем Эмитент.

8. Перейти на вкладку Формы, открыть форму Эмитент, ввести исходные данные – рис. 32.

Рисунок 32

9. Подготовить экранную форму для ввода данных в таблицу Котировка – команда меню ВставкаФорма, выбрать Мастер форм, указать таблицу Котировка:

• Выбрать все поля из таблицы Котировка.

• Выбрать поле «Наименование вида ЦБ» из таблицы ЦБ.

• Выбрать поле «Наименование эмитента» из таблицы Эмитент.

• Вид представления данных – по таблице Котировка.

• Внешний вид формы – Ленточный.

• Сохранить форму под именем Котировка.

10. Перейти на вкладку Формы. Открыть форму Котировка в режиме конструктора – кнопка Конструктор – рис. 33.

11. Изменить местоположение и размерные характеристики отдельных полей. Задать свойства полей «Наименование вида ЦБ» и «Наименование эмитента»: Доступ – Нет, Блокировка – Да.

Рисунок 33

12. Ввести данные в таблицу Котировка с помощью экранной формы – табл. 31.

Таблица 31

13. Выполнить экспорт таблиц ЦБ, Эмитент, Котировка в Microsoft Excel с помощью команды меню СервисСвязи с OfficeАнализ в MS Excel. Автоматически создается рабочая книга Microsoft Excel, содержащая 1 лист. Имя файла рабочей книги совпадает с именем таблицы (запроса, формы, отчета).

14. Закрыть СУБД Access.

15. Перейти в Microsoft Excel. Переместить листы рабочих книг Эмитент.xls и Котировка.xls в книгу ЦБ.xls – команда меню ПравкаПереместить/скопировать лист.

16. Построить сводную таблицу на основе списка (базы данных Excel), содержащего ячейки таблицы Котировка:

• Установить курсор в таблицу на листе Котировка, команда меню ДанныеСводная таблица.

• Макет таблицы: Строка – Дата, Код эмитента, Столбец – Код вида ЦБ, Данные:

• Номинал, Операция – Кол–во значений, Имя поля – Всего ЦБ

• Курс, Операция – Среднее, Имя поля – Ср. курс ЦБ.

• Разместить сводную таблицу на новом листе.

17. Перейти на лист сводной таблицы. Таблица содержит итоговые показатели 2.a и 2.b, 2.g и 2.h (см. п. 2 задания).

18. Установить курсор в сводную таблицу и выполнить команду контекстного меню ФормулыВычисляемое поле. Добавить вычисляемые поля:

• Имя поля – Предл, Формула: =Номинал*Эмиссия. Нажать кнопку Добавить. Это поле выполняет расчет показателей 2.c, 2,d;

• Имя поля – Спр, Формула: =Номинал*Спрос*Курс. Нажать кнопку Добавить. Это поле выполняет расчет показателей 2.e, 2.f.

• Закрыть диалоговое окно Вычисляемое поле.

19. Выполнить группирование значений поля Дата в сводной таблице. Для этого установить курсор на значение поля Дата, выполнить команду меню ДанныеГруппа и структураГруппировать. Указать интервал группировки – месяцы.

20. Перетащить групповое поле в область Страницы сводной таблицы.

21. Выделить групповое поле в области страницы, выполнить команду контекстного меню Параметры поля. Изменить название поля – Месяц. В результате выполняется расчет показателей 2.i и 2.j за каждый месяц и в целом за все периоды.

Результирующая сводная таблица – рис. 34.

22. Установить курсор в область сводной таблицы, выполнить команду контекстного меню Сводная диаграмма. Для графического анализа данных можно:

• Изменить тип диаграммы – команда меню ДиаграммаТип диаграммы.

• Переместить компоненты сводной диаграммы.

• Просматривать данные определенного периода (месяца) или всех периодов.

• Просматривать данные одного, нескольких или всех видов ЦБ.

• Просматривать данные одного, нескольких или всех эмитентов и т.п.

 Можно изменить состав области Данные – удалить ненужные. Одновременно редактируется сводная таблица.

Рисунок 34

23. Подготовка данных для описательной статистики:

• Вставить новый лист – команда меню ВставкаЛист.

• Переименовать новый лист – команда меню ФорматЛистПереименовать, лист Акция.

• Выполнить фильтрацию данных таблицы Котировка – команда меню ДанныеФильтрАвтофильтр. Условие: Код вида ЦБ равен А.

• Результат фильтрации выделить, скопировать – команда меню ПравкаКопировать.

• Перейти на лист Акция.

• Вставить буфер обмена.

• Вставить новый лист – команда меню ВставкаЛист.

• Переименовать новый лист – команда меню ФорматЛистПереименовать, лист Вексель.

• Выполнить фильтрацию данных таблицы Котировка – команда меню ДанныеФильтрАвтофильтр. Условие: Код вида ЦБ равен В.

• Результат фильтрации выделить, скопировать – команда меню ПравкаКопировать.

• Перейти на лист Вексель.

• Вставить буфер обмена.

• Вставить новый лист – команда меню ВставкаЛист.

• Переименовать новый лист – команда меню ФорматЛистПереименовать, лист Облигация.

• Выполнить фильтрацию данных таблицы Котировка – команда меню ДанныеФильтрАвтофильтр. Условие: Код вида ЦБ равен О.

• Результат фильтрации выделить, скопировать – команда меню ПравкаКопировать.

• Перейти на лист Облигация.

• Вставить буфер обмена.

24. Сформировать описательную статистику:

• Перейти на лист Акция. Выполнить команду меню СервисАнализ данных.

• Выбрать Описательную статистику.

• Входной интервал – блок ячеек столбца Курс.

• Выходной интервал – ячейка текущего листа.

• Выбрать флажок Итоговая статистика.

• Уровень надежность – 67%.

• К–ый наименьший – 2.

• К–наибольший – 2.

• Нажать кнопку ОК.

25. Повторить п. 24 для листа Вексель.

26. Повторить п. 24 для листа Облигация.