Практична робота №8 Застосування інструментарію прикладного аналізу Excel
« Назад
Практична робота №8Тема: Застосування інструментарію прикладного аналізу табличного процесора Excel для оптимізації роботи на фондовому ринку. Мета: Набути навичок реалізації алгоритмів засобами табличного процесору Excel для формування і управління інвестиційного портфелю. Порядок виконання роботи1. За допомогою ТП Excel створити на робочому аркуші 1 таблицю 8.1 для визначення дохідності казначейських облігацій (векселів): Таблиця 8.1
В таблиці представлено інформацію по формулах та числові дані, які виводяться в комірках діапазону В3:В8. 2. Всі фактичні дані вводяться в діапазон комірок В3:В5. Це номінальна ціна і ціна продаж облігації інвестору, а також термін її погашення. По цім даним обраховується ефективна річна ставка доходу, дохід по методу банківського дисконтна і еквівалент доходу облігацій. Результат виводять в комірки діапазону В6:В8. До діапазону комірок В3:В4 присвоєний грошовий формат, а до діапазону комірок В6:В8 – відсотковий. Неважко помітити саме високе значення доходу дає ефективна річна ставка доходності (Рис. 8.1).
Рис. 8.1 – Визначення дохідності 3. Визначені вище показники не є незалежними. Якщо період погашення облігації (векселя) фіксований, то за одним з трьох показників можна визначити решту. Створити на робочому аркуші 2 таблицю визначення доходності цінних паперів. Основна частина таблиці - сіра область з найменуванням полів: Період погашення (днів) - В5, Ефективна річна ставка - В7, Дохід банківського дисконтна - В9 і Еквівалентний дохід облігацій - В11. Відповідно в комірках зліва (А5, А7, А9, А1) виводяться значення (рис.8.2.). Для того щоб однозначно встановити взаємозв’язок між різними показниками дохідності, потрібно знати період погашення цінних паперів – він вводиться в комірці А5. На малюнку показаний фрагмент документу, де обраховують різні показники доходу. Встановити захист на зміни елементів таблиці, що знаходяться у сірій частині таблиці).
Рис. 8.2 – Доходність цінних паперів 4. Основним функціональним елементом в документі є кнопка Заповнити в нижній частині сірої області. Створити кнопку за допомогою панелі елементів режиму Конструктор. (Рис 8.3.)
Рис. 8.3 – Створення кнопки за допомогою панелі елементів режиму Конструктор 5. Активізувати вікно редактора VBA. У редакторі VBA (рис.8.4.) ввести код обробки подій для кнопки Заповнити: Лістинг 1.1. Код процедури CommandButton1_Click() Private Sub CommandButton1_Click() Load UserForm1 UserForm1.Show End Sub
Рис. 8.4 - Активізувати вікно редактора VBA 6. У вікні редактора VBA створити форму вікна Дохідність цінних паперів, яка містить дві кнопки, три перемикача і чотири текстових поля (рис. 8.5.):
Рис. 8.5 - Створення форми Дохідність цінних паперів 7. У вікні редактора VBA клацнути у формі кнопці по ОК ввести код обробки дій (рис. 8.6.): Лістинг 1.2. Код процедури CommandButton1_Click() форми UserForm1 Private Sub CommandButton1_Click() Dim R, N As Single N = TextBox1.Value Range(“A5”) = N If OptionButton1.Value Then R = TextBox1.Value / 100 Range(“A7”) = R Range(“A9”) = 360 * (1 – 1 / (1 + R) ^ (N / 365)) / N Range(“A11”) = 365 * ((1 + R) ^ (N / 365) – 1) / N Else If OptionButton2.Value Then R = TextBox2.Value / 100 Range(“A7”) = (1 / (1 – N * R / 360) ^ (365 / N) – 1) Range(“A9”) = R Range(“A11”) = 365 * (1 / (1 – N * R / 360) – 1) / N Else R = TextBox3.Value / 100 Range(“A7”) = (1 + N * R / 365) ^ (365 / N) – 1 Range(“A9”) = 360 * (1 – 1 / (1 + N * R / 365)) / N Range(“A11”) = R End If End If UserForm1.Hide Unload UserForm1 End Sub Лістинг 1.3. Код процедури CommandButton2_Click() UserForm1 Private Sub CommandButton2_Click() UserForm1.Hide Unload UserForm1 End Sub Лістинг 1.4. Код процедури OptionButton1_Change() форми UserForm1 Private Sub OptionButton1_Change() TextBox1.Enabled = OptionButton1.Value If OptionButton1.Value Then TextBox1.BackColor = &H80000005 Else TextBox1.BackColor = &H80000004 End If End Sub Лістинг 1.5. Код процедури OptionButton2_Change() форми UserForm1 Private Sub OptionButton2_Change() TextBox2.Enabled = OptionButton2.Value If OptionButton2.Value Then TextBox2.BackColor = &H80000005 Else TextBox2.BackColor = &H80000004 End If End Sub Лістинг 1.6. Код процедури OptionButton3_Change() форми UserForm1 Private Sub OptionButton3_Change() TextBox3.Enabled = OptionButton3.Value If OptionButton3.Value Then TextBox3.BackColor = &H80000005 Else TextBox3.BackColor = &H80000004 End If End Sub UserForm1.Hide Unload UserForm1 End Sub
Рис. 8.6 - Вікно редактора VBA 8. Кнопка Виконати під час активізації на робочому аркуші Excel буде відкривати діалогове вікно Дохідність цінних папері. (Рис. 8.7.)Активним є лише поле біля установленого перемикача – воно виділено білим кольором. Неактивне поле має сірий колір. Ввести один з показників та період погашення і ОК. В результаті виконання макросу на робочому аркуші 1 в таблиці будуть виведені значення розрахунків відповідних показників. Клацання на кнопку Відмінити дозволяє повернутись до початкового виду документа, а нажавши кнопку ОК підтверджує виконання дій у діалоговому вікні. Дохідність цінних паперів.
Рис. 8.7 - Дохідність цінних паперів 9. Переконайтесь у роботі усіх режимів вікна форми. Зберегти результати на сервері. 10. Оформити звіт з практичної роботи і відповісти на контрольні питання. Контрольні питання1. Поясніть що таке Целевая ячейка? 2. Чому актуальна проблема формування портфелів цінних паперів? 3. Написати формули моделі для оптимізації портфелів. 4. Перерахувати вхідні дані, змінні і результуючі показники моделі. З повагою ІЦ "KURSOVIKS"! |
