Практична робота №7, Застосування інструментарію прикладного аналізу табличного процесора Excel

« Назад

Мета: Набути навичок реалізації алгоритмів засобами табличного процесору Excel для формування і управління інвестиційного портфелю.

ЗАВДАННЯ ТА ПОРЯДОК ВИКОНАННЯ РОБОТИ

1. За допомогою ТП Excel створити на робочому аркуші 1 таблицю для визначення дохідності казначейських облігацій (векселів):

В таблиці представлено інформацію по формулах та числові дані, які виводяться в комірках діапазону В3:В8.

2. Всі фактичні дані вводяться в діапазон комірок В3:В5. Це номінальна ціна і ціна продаж облігації інвестору, а також термін її погашення. По цім даним обраховується ефективна річна ставка доходу, дохід по методу банківського дисконтна і еквівалент доходу облігацій. Результат виводять в комірки діапазону В6:В8. До діапазону комірок В3:В4 присвоєний грошовий формат, а до діапазону комірок В6:В8 – відсотковий. Неважко помітити саме високе значення доходу дає ефективна річна ставка доходності.

Рис. 1 - Дохід казначейських облігацій

3. Визначені вище показники не є незалежними. Якщо період погашення облігації (векселя) фіксований, то за одним з трьох показників можна визначити решту. Створити на робочому аркуші 2 таблицю визначення доходності цінних паперів. Основна частина таблиці - сіра область з найменуванням полів: Період погашення (днів) - В5, Ефективна річна ставка - В7, Дохід банківського дисконтна - В9 і Еквівалентний дохід облігацій - В11. Відповідно в комірках зліва (А5, А7, А9, А1) виводяться значення. Для того щоб однозначно встановити взаємозв’язок між різними показниками дохідності, потрібно знати період погашення цінних паперів – він вводиться в комірці А5. На малюнку показаний фрагмент документу, де обраховують різні показники доходу. Встановити захист на зміни елементів таблиці, що знаходяться у сірій частині таблиці).

Рис. 2 – Дохідність цінних паперів

4. Основним функціональним елементом в документі є кнопка Заповнити в нижній частині сірої області. Створити кнопку за допомогою панелі елементів режиму Конструктор.

Рис. 3 – Елементи управління форми

5. Активізувати вікно редактора VBA. У редакторі VBA ввести код обробки подій для кнопки Заповнити:

Лістинг 1.1.Код процедури CommandButton1_Click()

Private Sub CommandButton1_Click()

Load UserForm1

UserForm1.Show

End Sub

Рис. 4 - Вікно редактора VBA

6. У вікні редактора VBA створити форму вікна Дохідність цінних паперів, яка містить дві кнопки, три перемикача і чотири текстових поля:

Рис. 5 – Форма у редакторі VBA

7. У вікні редактора VBA клацнути у формі кнопці по ОК ввести код обробки дій:

Лістинг 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

Рис. 6 - Код обробки дій у редакторі VBA

8.Кнопка Виконати під час активізації на робочому аркуші Excel буде відкривати діалогове вікно Дохідність цінних папері. Активним є лише поле біля установленого перемикача – воно виділено білим кольором. Неактивне поле має сірий колір. Ввести один з показників та період погашення і ОК. В результаті виконання макросу на робочому аркуші 1 в таблиці будуть виведені значення розрахунків відповідних показників. Клацання на кнопку Відмінити дозволяє повернутись до початкового виду документа, а нажавши кнопку ОК підтверджує виконання дій у діалоговому вікні. Дохідність цінних паперів.

Рис. 7 - Виконання макросу в Excel

9. Переконайтесь у роботі усіх режимів вікна форми. Зберегти результати на сервері.

10. Оформити звіт з практичної роботи і відповісти на контрольні питання.

Детальніше дивіться методичні рекомендації:

http://ua.kursoviks.com.ua/metodychni_vkazivky/article_post/1449