- •Использование электронных таблиц excel для решения задач финансового менеджмента
- •Информационно-логическая модель предметной области
- •Справочник эмитентов
- •Итоги по датам продажи
- •Лист Котировка цб
- •Формирование данных базы данных
- •Импорт данных
- •Использование экранных форм ввода
- •Ввод данных в таблицу
- •Формирование итоговой информации по базе данных
- •Сортировка записей базы данных
- •Промежуточные итоги по базе данных
- •Создание сводных таблиц
- •1 Этап.
- •2 Этап.
- •Стоимость предложения – Стоимость спроса
- •3 Этап.
- •4 Этап.
- •Анализ данных
- •1 Этап.
- •2 Этап.
- •3 Этап.
- •Фильтрация записей базы данных
- •Автфильтрация
- •Расширенный фильтр
- •Встроенные функции работы с базой данных excel
- •Итоговые показатели по видам цб
- •Итоговые показатели по эмитентам
- •Итоги по датам продажи
- •Экспорт списка (базы данных) excel
- •Лист Статистика
- •Описательная статистика для массива значений показателя
- •Листы Диаграммы
- •Структура продаж по видам цб.
- •Соотношение спроса и предложения по видам цб
Использование электронных таблиц excel для решения задач финансового менеджмента
Рассматривается предметная область - фондовая биржа, на которой осуществляется продажа ценных бумаг. Создается база данных для учета финансовых сделок, горизонт наблюдения - год.
В среде электронных таблиц EXCEL обеспечить:
Создание и ведение нормативно-справочной информации для учета продаж ценных бумаг;
Регулярный учет сведений о котировке курсов ценных бумаг, объемах предложения и спроса;
Составление отчетов о соотношении спроса и предложения по видам ценных бумаг, эмитентам по учетным периодам - месяцам;
Ведение статистики спроса и предложения ценных бумаг в стоимостном и объемном выражении;
Прогнозирование спроса на определенные виды ценных бумаг различных эмитентов.
Информационно-логическая модель предметной области
Виды ценных бумаг
Эмитенты
Учетные периоды
Котировка ценных бумаг
Рис. 1
Связи информационных объектов (ИО) - один ко многим, реквизитный состав ИО приведен в таблице 1.
Таблица 1
Наименование ИО |
Реквизиты |
Формат |
Виды ценных бумаг (ЦБ) |
Код вида ЦБ Наименование вида ЦБ Суммарный объем предложений (в стоимостном выражении) Суммарный объем спроса (в стоимостном выражении) Средний курс ЦБ определенного вида Средний номинал ЦБ |
Т1 Т25 N N N N |
Эмитенты |
Код эмитента Наименование эмитента Суммарный объем предложений (в стоимостном выражении) Суммарный объем продаж (в стоимостном выражении) |
Т1 Т25 N N |
Учетные периоды |
Код периода (день, месяц) Суммарный объем предложений (в стоимостном выражении) Суммарный объем спроса (в стоимостном выражении) |
D N N |
Котировка ЦБ |
Код периода (дата) Код вида ЦБ Код эмитента Номинал ЦБ Эмиссия ЦБ (единиц) Спрос ЦБ (единиц) Курс ЦБ |
D Т1 Т1 N N N N |
Примечание. Использованы условные обозначения форматов:
Т - текстовый (символьный) тип, n - длина текста;
D - дата, время;
N - числовой тип (допускается формат чисел с фиксированной десятичной точкой).
В базе данных фиксируются сведения о продажах ценных бумаг в конце операционного дня, все сводки за прошедшие сутки формируются на начало следующего дня. Итоги по видам ценных бумаг, эмитентам и дате
Последовательность выполнения лабораторной работы
Определение состава и назначения листов рабочей книги;
Проектирование структуры данных (макета размещения информации) отдельных листов рабочей книги;
Выбор информационных технологий EXCEL для решения задачи;
Рабочая книга EXCEL может включать 1-255 листов, каждый из которых содержит табличные структуры данных. Согласно приведенной схеме ИЛМ ПО (рис. 1) создается набор листов рабочей книги:
Справочники, содержит справочники (коды и наименования) видов ценных бумаг и эмитентов, список учетных дат работы биржи, а также итоговые данные технико-экономические показатели по видам ценных бумаг, эмитентам и датам продажи (см. структуру ИО Виды ЦБ, Эмитенты, Периоды);
Котировка ЦБ, содержит базу данных оперативного учета предложений и спроса на ценные бумаги эмитентов;
Итоги, содержит промежуточные итоги по группировочным полям базы данных;
Своды, содержит своды по данным оперативного учета котировки ЦБ;
Анализ, содержит результаты дополнительного анализа продаж ЦБ;
Критерии, имеет вспомогательный характер для автоматизации обработки данных по котировке ценных бумаг;
Статистика, содержит сведения статистического характера по котировкам ЦБ и эмитентам;
Диаграммы, иллюстрирует состояние спроса и предложения для различных видов ценных бумах.
Машинная реализация в среде EXCEL
Рабочая книга Ценные бумаги.xls
Создать новую рабочую книгу - команда меню ФАЙЛ, Создать, шаблон - Книга,
Сохранить созданную рабочую книгу под именем Ценные бумаги - команда ФАЙЛ, Сохранить как, выбрать диск, папку, задать параметры - пароль на открытие книги.
Лист Справочники
Переименовать лист Лист1 в лист Справочники - команда меню ФОРМАТ, Лист, Переименовать.
Для нормативно-справочной информации ИО Виды ЦБ, Эмитенты, Периоды учета на листе Справочники создаются таблицы.
Справочник видов ценных бумаг
Виды ценных бумаг |
Суммарный объем |
Среднее значение |
|||
Код ЦБ |
Наименование ЦБ |
Предложения |
Спроса |
Курса |
Номинала |
|
|
|
|
|
|
|
|
|
|
|
|
Рис. 2
В столбцы Код ЦБ и Наименование ввести коды и наименования ценных бумаг (для учебного примера - ограничиться: А - Акция; О - Облигация; В - Вексель).Остальные столбцы таблицы НЕ ЗАПОЛНЯТЬ.
Создать именованный блок Вид_ЦБ для ячеек, содержащих коды видов ЦБ.
Блок - прямоугольная область ячеек, расположенных на одном и том же листе или различных листах рабочей книги. Имя блока - непрерывная последовательность символов, соответствующая следующим ограничениям:
· Первый символ обязательно буква или символ подчеркивания.
· Имена не имеют сходства со ссылками на ячейки.
· Пробел запрещен (можно символ подчеркивания, точки)..
· Максимальная длина имени - 255 символов.
· Строчные и прописные буквы в именах блоков не различаются.
Блок ячеек одного листа может включать не обязательно смежные ячейки. Для выделения нескольких областей ячеек листа одного блока используется клавиша <Ctrl>. Microsoft Excel 97 позволяет также создавать "объемные" блоки, содержащие ячейки различных листов одной рабочей книги, но имеющих одинаковую конфигурацию.
1 способ создания именованного блока ячеек одного листа:
Выполнить команду меню Вставка, Имя, Присвоить;
Ввести имя создаваемого блока ячеек в строке Имя,
Установить курсор в строку Формула, нажать кнопку и закрасить диапазон ячеек, содержащий только значения кодов ЦБ;
Нажать кнопку ОК.
2 способ создания именованного блока ячеек одного листа:
Закрасить диапазон ячеек, содержащий название столбца и значения кодов ЦБ;
Выполнить команду меню Вставка, Имя, Создать;
Указать, что имя блока находится в строке выше;
Нажать кнопку ОК.
3 способ создания именованного блока ячеек разных листов:
Выполнить команду меню Вставка, Имя, Присвоить;
Ввести имя создаваемого блока ячеек в строке Имя,
Перейти в строку Формула, нажать кнопку обзора, выделить группу смежных листов, содержащих объемный блок - при нажатой клавише <Shift> выделить первый и последний листы;
Закрасить нужный блок ячеек на первом листе;
Нажать кнопку ОК.
Наличие имен блоков позволяет упростить вид формул, сделать их стабильными независимо от фактического диапазона ячеек. Имена блоков обеспечивают также быстрый переход в рабочей книге в требуемое место - команда меню Правка, Перейти (иначе - через список имен - слева от строки формул).
EXCEL 97 обеспечивает новый режим - гиперссылки на любые фрагменты рабочей книги, файлы допустимых форматов, ресурсы Internet - команда Вставка, Гиперссылка. При этом происходит автоматический переход к указанному фрагменту или осуществляется вызов файла-источника.
Пересчет показателей по видам ЦБ должен осуществляться динамически в процессе оперативного ввода учетной информации о котировках ценных бумаг. Это возможно за счет применения встроенных функций работы с базами данных EXCEL (см. далее).
Суммарный объем предложения в стоимостном выражении по виду ЦБ равен сумме предложений по всем эмитентам за весь период наблюдения.
Суммарный объем спроса в стоимостном выражении по виду ЦБ равен сумме спроса за весь период наблюдения.