Інформаційна технологія виконання бізнес-аналізу фінансових угод із цінними паперами

Предметною областю даної задачі є діяльність фондової біржі, на якій здійснюються торги з цінних паперів. Для організації цієї роботи створюється облікова база даних регулярно здійснюваних фінансових договорів. При цьому потрібно:

1) здійснювати регулярний облік відомостей про квотування курсів цінних паперів, обсягах пропозиції та попиту;

2) складати відомості про квотування цінних паперів, про співвід­ношення попиту та пропозиції з видів цінних паперів, емітентів у динаміці (за обліковими періодами);

3) давати розгорнуту статистику попиту та пропозиції у вартісному та об'ємному вираженні;

4) прогнозувати попит на певні види цінних паперів різних емітентів.
Для оптимальної організації інформації на персональному ком­
п'ютері варто провести інформаційний аналіз предметної області і по­
будувати інформаційно-логічну модель (ІЛМ). Процес побудови ЦІМ

Зацеркляний М. М., Мельников О. ф.

ІНФОРМАЦІЙНІ СИСТЕМИ І ТЕХНОЛОГИ У ФІНАНСОВО-КРЕДИТНИХ УСТАНОВАХ

предметної області починається з визначення складу реквізитів, які цир­кулюють у досліджуваній предметній області. Потім необхідно виявити функціонально-залежні реквізити і об'єднати їх у групи, чи інформацій­ні об'єкти (10), давши попередньо кожній групі ім'я.

Результати проведеного інформаційно-логічного аналізу для фі­нансових договорів із цінними паперами на фондовій біржі зведені в табл. 3.1, де визначені інформаційні об'єкти, їхні імена, реквізитний склад і типи даних (формати). В таблиці використовуються умовні по­значення таких форматів:

• С — символьний тип;

• D — дата, час;

• N — числовий тип (допускається формат чисел із фіксованою де­сятковою точкою).

У базі даних фіксуються відомості про продажі цінних паперів на­прикінці операційного дня, всі відомості за минулу добу формуються на початок наступного дня. Підсумки проводяться за видами цінних папе­рів, емітентами і датами.

Бізнес-аналіз фінансових договорів із цінними паперами включає такі етапи:

• визначення структури робочої книги (складу і призначення окре­
мих аркушів);

• проектування структури даних (макета розміщення інформації) • для окремих аркушів робочої книги;

• підготовки даних (введення первинної облікової інформації);

• контроль достовірності введення початкових даних;

• виконання розрахунків із використанням різних інформаційних
технологій.

Для реалізації перших двох етапів необхідно виконати такі кроки:

1) створити нову робочу книгу — команда Файл/Створити, шаблон — Книга; зберегти її під ім'ям Цінні папери — команда Файл/Зберегти як; вибрати диск, папку, задати параметри — па­роль на відкривання книги;

2) перейменувати аркуш Листі у аркуш Довідники— команда го­ловного меню Формат/ Листі Переименовать (або за допомогою контекстного меню);

Розділ З МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

Таблиця 3.1

Реквізитний склад інформаційних об'єктів

Зацеркпяний М. М., Мельников О. Ф.

ІНФОРМАЦІЙНІ СИСТЕМИ І ТЕХНОЛОГИ У ФІНАНСОВО-КРЕДИТНИХ УСТАНОВАХ

3) для збереження нормативно-довідкової інформації на аркуші До­відники необхідно створити три таблиці:

• довідник видів цінних паперів;

• довідник емітентів;

• підсумки за період обліку.

Довідник видів цінних паперів має такий вигляд:

Для заповнення Довідника видів цінних паперів треба ввести коди видів і найменування цінних паперів, наприклад:

• А — акції;

• О — облігації;

• В — векселі,

а інші графи не заповнювати.

Варто забезпечити перерахування показників (сумарний об 'єм про­позицій, сумарний попит, середній курс, середнє значення номіналу) кож­ного виду цінного папера (ЦП) у режимі оперативного введення обліко­вої інформації про квотування цінних паперів і їх реалізації.

Показники з видів цінних паперів розраховуються за весь період обліку.

Довідник емітентів має такий вигляд:

У Довідник емітентів варто ввести відомості про емітенти цінних паперів (код і найменування), наприклад:

ПІ — AT «Сигнал»;

П2 — ПО «Вимпел»; • ПЗ — Банк «Сигма», а інші графи не заповнювати.

Розділ З МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

Необхідно розраховувати сумарний об'єм пропозицій та попиту у вар­тісному вираженні за весь обліковий період і занести в таблицю Підсумки за період обліку (спочатку варто заповнити тільки шапку таблиці).

Таблиця Підсумки за період обліку має такий вигляд:

Попередньо підраховується сумарний обсяг пропозицій та попиту цінних паперів у вартісному вираженні за кожний день.

Усі показники розглянутих таблиць розраховуються за допомогою вбудованих функцій роботи з базами даних, спеціальних підсумкових функцій Excel і апарата зведених таблиць.

Для організації перевірок достовірності введення оперативної ін­формації доцільно підготувати іменовані блоки комірок, які містять коди видів цінних паперів і коди емітентів. Для цього варто виділити (зафар­бувати) блок комірок із кодами і виконати команду меню Вставляння! їм 'я! Привласнити. У нашому прикладі необхідно створити такі імено­вані блоки комірок:

• ЦП — для комірок стовпчика Код у Довіднику видів цінних
паперів;

• Емітент — для комірок стовпчика Код у Довіднику емітентів;
4) перейменувати Лист2 Excel у аркуш Квотування ЦП.

На аркуші Квотування ЦП буде здійснюватися оперативний об­лік операцій із цінними паперами. Спочатку створені довідники нор­мативно-довідкової інформації (аркуш Довідники) у частині кодів і найменувань варто скопіювати на аркуш Квотування ЦП за допомогою команди Виправлення! Спеціальне вставляння! Встановити зв 'язок. При цьому блоки комірок будуть синхронізовані з початковими даними ар­куша Довідники, які використовуються для забезпечення достовірності введення кодів видів цінних паперів і емітентів.

Облікова інформація зберігається у вигляді бази даних ЦП такої структури (табл. 3.2).

Таблиця 3.2

База даних ЦП

Початкові дані вводяться в стовпчики A-G, стовпчики Н, І містять формули виду:

Вартість пропозиції = Емісія ЦП * Номінал ЦП,

Вартість попиту = Попит ЦП * Номінал ЦП * Курс ЦП.

Формули розрахунку вводяться в комірки стовпчиків Н і І першого рядка таблиці.

На аркуші Квотування для стовпчика Код виду ЦП вибір значень здій­снюється зі списку їх кодів у Довіднику видів цінних паперів — блок ЦП.

У комірці стовпчика Код виду ЦП у першому рядкові таблиці виконується команда Данії Перевірка, забезпечивши її наступне налаш­тування.

На вкладці Параметри вказується:

• Тип даних — Список;
Джерело — ЦП (ім'я блоку).

На вкладці Повідомлення для введення вказати:

• Заголовок — Вид ЦП;

• Повідомлення —А — акція, В — вексель, О — облігація (і т. д.). На вкладці Повідомлення про помилку вказати:

Вид — Зупинка; Заголовок — Помилка;

• Повідомлення — Вибір тільки з довідника.

Для стовпчика Код періоду вказується перевірка на діапазон зна­чень дат (01.01.98-31.12.98).

Для стовпчика Код емітента вказується вибір значень зі списку ко­дів емітентів, які містяться в Довіднику емітентів, блок Емітент, для стовпчика Номінал — перевірка на діапазон чисел, для стовпчика Курс ЦП— перевірка на діапазон чисел.

Розділ З МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

Для поширення умов перевірки введення даних (стовпчики В, С, D, G) і розрахункових формул (стовпчики Я, І) на наступні рядки потрібно:

• створити іменований блок комірок, що включає заголовки стовп­чиків і два порожні рядки (у першому рядку визначені умови пе­ревірок і задані формули обчислення показників). Для цього ви­діляються комірки зазначеного діапазону і виконується команда головного меню Вставляння/ їм 'яі Привласнити і вводиться ім'я блоку — База даних;

• перевірити виділення блоку комірок — відкрити список імен (лі­воруч від рядка формул), вибрати ім'я База даних чи виконати ко­манду Виправлення/ Перейти;

• для вставляння нового рядка й одночасної розтяжки блоку висо­тою варто встановити курсор у нижній рядок блоку і виконати ко­манду головного меню Вставляння! Рядки. При цьому відбудеться автоматичне перенесення умов перевірки даних для стовпчиків В, С, D, G у новий рядок. Формули обчислення вартісних показників можна вставити шляхом протягання формули попереднього рядка при натиснутій лівій кнопці мишки.

Існують такі варіанти введення даних у таблицю, подану у вигляді списку чи бази даних EXCEL:

• ручне введення. Дані вводяться рядками, при порушенні умов пе­ревірки виводиться повідомлення, введення блокується;

• введення за допомогою екранної форми. Курсор встановлюється в область таблиці і виконується команда Данії Форма. За допомо­гою кнопок Додати, Вилучити, Повернути здійснюється управ­ління введенням даних. Даний спосіб дозволяє автоматично роз­ширювати блок рядків списку, якщо він мав стандартне ім'я База даних. Крім того, якщо список містить обчислювальні поля, ав­томатично копіюються формули в нові записи. Форма дозволяє також виконувати пошук записів за умовами.

Для формування основних підсумків створюється копія аркуша Квотування командою Виправлення/ Перемістити!Копіювати і пере­йменовується створений аркуш у Підсумки.

Зацеркляний М. М., Мельников О. Ф.

ІНФОРМАЦІЙНІ СИСТЕМИ І ТЕХНОЛОГІЇ У ФІНАНСОВО-КРЕДИТНИХ УСТАНОВАХ

Таблиця 3.3

Зміст бази даних Цінні Папери для введення (приклад)

Розділ З МОДЕЛІ і МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

Сортування даних виконується командою Данії Сортування, за ключі сортування використовуються:

• код виду цінного папера (ЦП);

• код емітента;

• номінал.

Підводяться проміжні і загальні підсумки за полями:

• вартість пропозиції;

• вартість попиту.

Двічі виконується команда головного меню Данії Підсумки:

1) за поля групування вказується Код виду ЦП, операція — Сума, до­даються підсумки за полями: Вартість пропозиції і Вартість по­питу, підсумки розміщуються під даними, попередні підсумки не вилучаються;

2) за поля групування вказується Код емітента, операція — Сума, додаються підсумки за полями Вартість пропозиції і Вартість попиту, підсумки розміщуються під даними, попередні підсумки не вилучаються.

Для аналізу попиту та пропозиції за емітентами і видами цінних па­перів, необхідно сформувати зведену таблицю Excel, як показано ниж­че— табл. З.4.

Таблиця 3.4

Зведена таблиця бази даних Цінні Папери, згрупована за емі­тентами і видами ЦП

Зацеркляний М. М., Мельников О. Ф.

ІНФОРМАЦІЙНІ СИСТЕМИ І ТЕХНОЛОГІЇ У ФІНАНСОВО-КРЕДИТНИХ УСТАНОВАХ

Закінчення табл. 3.4

Для побудови зведеної таблиці в Excel необхідно виконати такі етапи. 1.

• встановити курсор в область початювої таблиці — бази даних ЦП;

• виконати команду головного меню Данії Зведена таблиця;

• вказати тип джерела даних для побудови зведеної табли­ці — база даних {список) Excel;

J^lfc» Розділ З

ШШ$ МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

• розташувати поля бази даних ЦП в областях макета зведеної
таблиці таким чином:

• Рядок — Код виду ЦП;

• Стовпчик — Код емітента;

• Область Дані повинна містити такі підсумкові поля:

• Вартість пропозиції (перейменувати поле в Пропозиції з ЦП, операція — Сума);

• Вартість попиту (перейменувати поле в Попит з ЦП, операція — Сума);

• Закінчити побудову зведеної таблиці і розташувати її на но­вому аркуші;

• Перейменувати аркуш у Зведені таблиці.

2. Встановити курсор в область зведеної таблиці, натиснути праву кнопку мишки і виконати команду Формули/ Обчислювальне поле. Вказати ім'я нового обчислювального поля —Дефіцит/Надлишок, формула розрахунку: Вартість пропозиції— Вартість попиту. Розмістити обчислювальне поле у макеті зведеної таблиці з ім'ям — Різниця, операція — Сума.

3. Додати в зведену таблицю нові поля в область Дані: Вартість пропозиції — з ім'ям Структура пропозиції за емітентами, опе­рація — Сума, додаткові обчислення — Частка від суми рядка; Вартість попиту — з ім'ям Структура попиту на ЦП емітентів, операція — Сума, додаткові обчислення — Частка від суми ряд­ка; Вартість пропозиції— з ім'ям Структура пропозицій емітен­та, операція — Сума, додаткові обчислення — Частка від суми стовпчика.

4. Змінити параметри зведеної таблиці за допомогою команди кон­текстного меню Параметри:

• Загальна сума по стовпчиках; Загальна сума по рядках;

• Автоформат; Зберігати форматування; Зберігати дані з таблицею;

• Поновити при відкриванні.

Зацеркляний М. М., Мельников О, Ф.

ІНФОРМАЦІЙНІ СИСТЕМИ /ТЕХНОЛОГИУФІНАНСОВО-КРЕДИТНИХ УСТАНОВАХ

Таблиця 3.5

Для готової зведеної таблиці потрібно виконати команду контек­стного меню Формули/ Вивести формули (курсор знаходиться всередині області даних зведеної таблиці), проаналізувати результат формування обчислювального поля.

Підготуємо зведену таблицю для аналізу попиту та емісії ЦП у вза­ємозв'язку з номіналом і курсами ЦП різних видів за обліковими міся­цями (табл. 3.5).

Технологія побудови зведеної таблиці включає такі етапи:

1. Розмістити в макеті зведеної таблиці поля початкової таблиці та­
ким чином:

• Рядок — Номінал, Дата;

• Стовпчик — Курс ЦП;

• Дані — Емісія ЦП, Попит на ЦП.

Для поля Емісія ЦП вказати ім'я — Випуск, операція — Сума; для поля Попит на ЦП— ім'я Попит, операція — Сума.

2. Закінчити побудову зведеної таблиці, розмістивши її на аркуші
Зведені таблиці.

• встановити курсор у зведену таблицю, виконати групування
поля Дата — крок групування —Місяці;

• встановити курсор у зведену таблицю, виконати групування поля Номінал — крок групування — 2000;

• встановити курсор у зведену таблицю, виконати групування поля Курс ЦП— крок групування — 0,05.

3. Перемістити в макеті поле Дата в область Сторінки. В результаті будується зведена таблиця для аналізу попиту та емісії ЦП. Для фільтрації записів бази даних ЦП можна використовувати два способи:

• вибір даних списку засобами автофільтра;

• з використанням розширеного фільтра.

Для використання автофільтру курсор встановлюється у список і виконується команда Данії Фільтрі Автофільтр. Використовуючи різні критерії відбору рядків на рівні одного чи декількох полів, здійснюється фільтрація записів. Відфільтровані записи можна:

• вилучити;

• копіювати в інше місце;

• використовувати для корегування значень окремих полів. Виконується відбір записів бази даних ЦП відповідно до таких умов:

• відомості про реалізацію акцій і облігацій;

• відомості про ЦП зазначеного емітента;

• відомості про ЦП, курс квотування яких знаходиться в діапа­зоні 0,95-1,05;

• відомості про продаж із найменшим курсом квотування ЦП;

• відомості про продаж акцій і облігацій із найбільшим курсом квотування ЦП тощо.

Розширений фільтр списку (бази даних) Excel вимагає створення області критеріїв, де вказуються умови фільтрації записів списку.

Для задачі бізнес-аналізу фінансових договорів із ЦП доцільно сформувати область критеріїв на новому робочому аркуші EXCEL і дати йому ім'я Критерії. На цьому аркуші треба підготувати область критері­їв для поля Код виду ЦП.

10*

Зацеркляний М. М., Мельников О. Ф.

ІНФОРМАЦІЙНІ СИСТЕМИ І ТЕХНОЛОГІЇ У ФІНАНСОВО-КРЕДИТНИХ УСТАНОВАХ

&%S

Для розрахунку значень показника Сумарний обсяг пропозиції за видами ЦП можна скористатися критерієм, який містить точні значення поля Код виду ЦП. Для цього встановлюється курсор в комірку результа­ту на аркуші Довідники і вводиться формула, наприклад, для акцій:

де Базаданных — ім'я блоку бази даних; 8 — порядковий номер поля, для якого обчислюється сума значень; Критерії\А\А1 — діапазон ко­мірок аркуша Критерії, що охоплює назви і значення полів бази даних для фільтрації записів (у даному випадку — для акцій).

Сумарний об 'єм пропозицій ЦП (на прикладі акцій) можна розра­хувати і за допомогою формули, що вводиться в комірки відповідного стовпчика аркуша Довідники таблиці Довідник видів цінних паперів:

Функція умовного підсумовування СУММЕСЛИ включає такі ар­гументи:

початковий діапазон комірок {Код виду ЦП), що беруть участь у

порівнянні з критерієм;

критерій (A3) — у формі числа, виразу чи тексту, що визначає

умову; • інтервал комірок сумування {Стоимость предложения).

Для використання цієї функції доцільно заздалегідь створити імена блоків для полів бази даних {Код виду ЦП, Стоимость пред­ложения).

Аналогічно можна розрахувати показник Сумарний об 'єм попиту ЦП за допомогою функції умовного підсумовування СУММЕСЛИ, ви­значивши ім'я блоку Стоимость предложения.

Розрахунок значень показників Середнє значення курсу здійснюєть­ся за допомогою функції баз даних — ДСРЗНАЧ:

=ДСРЗНАЧ {База данных;!^к,Критерии!А1. А2). де 7 — порядковий номер поля, за яким обчислюється середнє значення. 148

Розділ З МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

Функція СЧЕТЕСЛИ обчислює кількість комірок у зазначеному ді­апазоні, які відповідають заданому критерію (у даному випадку значен­ню в комірці A3). За допомогою функцій ДСРЗНАЧ і СЧЕТЕСЛИ можна розрахувати показник Середнє значення номіналу ЦП, попередньо ство­ривши блок НоміналЦП.

Будується область критеріїв на аркуші Критерії за полем Код емі­тента:

На основі функцій EXCEL і побудованої області критеріїв можна виконати такі розрахунки:

• розрахувати Сумарний об 'єм пропозицій ЦП за емітентами з ви­
користанням функцій БД СУММ і СУММЕСЛИ;

• розрахувати Сумарний об 'єм попиту на ЦП емітентів із викорис­
танням функції БДСУММ і СУММЕСЛИ, СЧЕТЕСЛИ.
Підсумки виводяться за календарними датами. Для заповнення ді­
апазону дат варто ввести початкову дату, зафарбувати блок комірок да­
ного стовпчика при натиснутій правій кнопці мишки. Виконати команду
контекстного меню Заповнити по днях.

Після створення діапазону дат можна виконати такі розрахунки: з використанням вбудованої функції СУММЕСЛИ обчислити Сумар­ний об 'єм пропозиції і Сумарний об 'єм попиту на кожну окрему дату (попередньо створивши іменований блок Дата для бази даних);

• сумарний об 'єм пропозиції і Сумарний об 'єм попиту на кожну
окрему дату можна обчислити за допомогою зведеної таблиці, ви­
користовуючи наступний макет розташування даних:

• Рядок—Дата;

Дані — Вартість пропозиції, Вартість попиту.

Зацеркляний М. М., Мельников О. Ф.

ІНФОРМАЦІЙНІ СИСТЕМИ /ТЕХНОЛОГИУ ФІНАНСОВО-КРЕДИТНИХ УСТАНОВАХ ІК