Создание приложений Excel на языке VBA

Размещено на http://www.allbest.ru/

Курсовая работа

по дисциплине «Программирование»

Создание приложений Excel на языке VBA

Вологда 2013



Введение

Microsoft Excel относится к типу программ, которые называются табличными процессорами или электронными таблицами.

Microsoft Excel имеет встроенный язык программирования - Visual Basic for Аpplications (VBA). Этот язык позволяет создавать приложения, выполняемые в среде Microsoft Office. Это могут быть разнообразные аналитические программы, финансовые системы, программы учета кадров, системы автоматического создания официальных писем/документов с помощью библиотеки готовых шаблонов и т.п. При этом интерфейс создаваемой программы может быть совершенно непохожим на интерфейс того приложения, в котором она написана.

VBA сочетает в себе практически неограниченные возможности с простотой изучения и использования. Теперь большинство функций можно описать, не прибегая к программированию. Пожалуй, самое главное достоинство VBA в том, что этот язык является единым для всех офисных приложений Microsoft и поэтому позволяет связывать их между собой

VBA - `объектно - ориентированный язык программирования высокого уровня, являющийся одним из диалектов языка программирования Visual Basic.

Программа Excel, рабочая книга, рабочие листы, ячейки - примеры объектов. Каждый объект имеет методы и свойства. Например, свойств “visible” программы Excel позволяет отображать или делать не видимым окно программы Excel. Метод “Quit” позволяет завершить работу с программой Excel. Кроме того, с объектами могут происходить различные события. Открытие программы, открытие и закрытие рабочей книги - примеры событий. Суть программирования на VBA заключается в том, чтобы написать отклик на событие.

Несомненным достоинством VBA является возможность его совместного использования с другими приложениями и языками программирования. Совместное использование средств интеграции приложений, Visual Basic или других языков программирования в VBA позволяет создавать полностью автоматизированные системы. Процесс разработки таких систем занимает на порядок меньше времени, чем при помощи традиционных языков программирования. Использование средств интеграции приложений, их интерфейс и компоненты позволяют разрабатывать на VBA решения, которые не возможно было бы ранее создать в силу ограниченности как финансовых, та и человеческих ресурсов, а также времени.

Очень удобно, что VBA применяет технологию визуального программирования, то есть конструирование рабочей поверхности приложения и элементов его управления непосредственно на экране, а также запись всей программы или ее частей при помощи специальной программы MacroRecorder.

программирование еxcel доходность



1. Постановка задачи

Создадим приложение Excel на языке VBA ,с помощью которого можно рассчитать внутреннюю норму доходности.

Внутренняя норма доходности (англ. internal rate of return, общепринятое сокращение -- IRR (ВНД)) -- это процентная ставка, при которой чистая приведённая стоимость (чистый дисконтированный доход - NPV) равна 0. NPV рассчитывается на основании потока платежей, дисконтированного к сегодняшнему дню.

Таким образом, для потока платежей CF, где CF_t -- платёж через лет () и начальной инвестиции в размере IC=? CF_o внутренняя норма доходности рассчитывается из уравнения:

Или

Если все CF_t положительны, то уравнение имеет единственный корень, больший ?1. Если в уравнении все платежи и свободный член положительны, то уравнение имеет положительный корень, то есть внутренняя норма доходности положительна.

При принятии инвестиционных решений ВНД используется для расчёта ставки альтернативных вложений. При выборе из нескольких проектов с разными ВНД, выбирается проект с максимальным значением ВНД. Данный критерий не используется, если денежные потоки несколько раз за рассматриваемый период меняют знак. Экономический смысл этого параметра заключается в том, что он определяет верхнюю границу доходности инвестиционного проекта, и, соответственно, максимальные удельные затраты по нему: если IRR проекта больше стоимости инвестируемого капитала, то проект следует принимать к рассмотрению, в противном случае -- отклонять.

Внутренней доходностью для финансовых инструментов называют процентную ставку, при которой дисконтированная стоимость будущего потока платежей по данному финансовому инструменту совпадает с его рыночной ценой. Определённая таким образом внутренняя доходность равна внутренней норме доходности инвестиции в данный финансовый момент времени.

Связь способа расчёта внутренней нормы доходности с показателем дисконтированной стоимости состоит в том, что показатель внутренней нормы доходности отражает тот уровень ставки дисконтирования, при которой дисконтированная стоимость полученных доходов за вычетом суммы первоначальной инвестиции (чистая дисконтированная стоимость) будет равна нулю. Чем выше должна быть ставка дисконтирования для приведения чистой дисконтированной стоимости полученных доходов к нулю, тем предпочтительнее данная инвестиция.

Показатель внутренней нормы доходности основывается на допущении, что денежные потоки, полученные до погашения инвестиции (истечения срока её действия), будут реинвестированы по ставке, равной внутренней норме доходности, и что она останется неизменной.

Инвестиция считается приемлемой, если внутренняя норма доходности выше, чем минимальный приемлемый показатель окупаемости инвестиций в финансовый инструмент. Такие инвестиции представляют интерес для инвестора.

Показатель внутренней нормы доходности для облигаций называется доходностью к погашению. Доходность к погашению облигации может отличаться от купонной ставки, если облигация продаётся по цене, отличающейся от номинальной стоимости.

2. Математические методы решения задачи

Для нахождения внутренней нормы доходности используются численные методы решения нелинейных уравнений. В программе используется метод половинного деления. Рассмотрим его содержание.

Перед применением метода для поиска корней функции необходимо отделить корни, то есть определить границы, внутри которых находятся корни.

Рассмотрим уравнение

Будем считать, что корень t функции f(x) отделён на отрезке [a;b]. Задача заключается в том, чтобы найти и уточнить этот корень методом половинного деления. Другими словами, требуется найти приближённое значение корня с заданной точностью .

Пусть функция f непрерывна на отрезке [a;b], и - единственный корень уравнения

Поделим отрезок [a;b] пополам. Получим точку и два отрезка [a;c] и [c;b].

Если , то корень t найден (t=c).

Если нет, то из двух полученных отрезков [a;c] и [c;b] надо выбрать один [a₁;b₁] такой, что , то есть

Новый отрезок [a₁;b₁] делим пополам. Получаем середину этого отрезка так далее.

Для того, чтобы найти приближённое значение корня с точностью до , необходимо остановить процесс половинного деления на таком шаге n, на котором и вычислить Тогда можно взять

Описание алгоритма программы с описанием функций составных частей и связи между ними

Перед запуском программы в ячейки рабочего листа «Данные» необходимо ввести размеры платежей и число платежей. Программа запускается с помощью программы run. Для запуска программы необходимо открыть окно «Макросы». Окно программы показано на рисунке. В нем пользователь должен ввести значения левой и правой границ корня и указать точность вычислений. Рассчитанная норма доходности записывается в одну из ячеек рабочего листа. При необходимости можно сохранить найденное число.

Программа может работать в различных версиях Excel: Excel 97, Excel 2002 и Excel 2013 под управлением операционной системы Windows. Приведем листинг программы.

3. Листинг программы

Private Sub Workbook_Open()

run

End Sub

Sub run()

Load UserForm1

UserForm1.Show

End Sub

Option Base 1

Private Sub CommandButton1_Click()

Unload Me

End Sub

Private Sub CommandButton3_Click()

Dim x(100) As Double

Dim n As Integer

Dim p As Double

Dim i As Integer

Dim epsilon As Double

Dim y1 As Double

Dim y2 As Double

Dim a As Double

Dim b As Double

Dim c As Double

Dim s As Double

Dim s1 As Double

If IsEmpty(Worksheets("Данные").Range("f2")) = True Or _

IsNumeric(Worksheets("Данные").Range("f2").Value) = False Then

MsgBox " Не введено число платежей или не числовое данное", vbInformation, "Ошибка"

Unload Me

Else

n = Worksheets("Данные").Range("f2").Value

End If

If IsEmpty(Worksheets("Данные").Range("g1")) = True Or _

IsNumeric(Worksheets("Данные").Range("g1").Value) = False Then

MsgBox "Не введен объем инвестиций или не числовое данное", vbInformation, "Ошибка"

Unload Me

Else

p = Worksheets("Данные").Range("g1").Value

End If

If IsNumeric(TextBox1.Value) = False Then

MsgBox "Ошибка при вводе данных", vbInformation, "Ошибка"

TextBox1.SetFocus

Exit Sub

Else

y1 = CDbl(TextBox1.Value)

End If

If IsNumeric(TextBox2.Value) = False Then

MsgBox "Ошибка при вводе данных", vbInformation, "Ошибка"

TextBox2.SetFocus

Exit Sub

Else

y2 = CDbl(TextBox2.Value)

End If

If y1 >= y2 Then

MsgBox "Левая граница больше или равна правой. Проверьте данные", vbInformation, "Ошибка"

TextBox1.SetFocus

Exit Sub

End If

If IsNumeric(TextBox3.Value) = False Then

MsgBox "Ошибка при вводе данных", vbInformation, "Ошибка"

TextBox3.SetFocus

Exit Sub

Else

epsilon = CDbl(TextBox3.Value)

If epsilon <= 0 Then

MsgBox "Ошибка при вводе данных. Точность вычислений не может быть отрицательной", vbInformation, "Ошибка"

TextBox3.SetFocus

Exit Sub

End If

End If

'Формирование одномерного массива

i = 2

While i - 1 <= n

If IsEmpty(Worksheets("Данные").Cells(i, 1)) = True Or _

IsNumeric(Worksheets("Данные").Cells(i, 1).Value) = False Then

MsgBox "Или пустая ячейка или не числовые данные", vbInformation, "Ошибка"

Worksheets("Данные").Cells(i, 1).Select

Unload Me

Exit Sub

Else

x(i - 1) = Worksheets("Данные").Cells(i, 1).Value

End If

i = i + 1

Wend

Нахождение внутренней нормы доходности методом половинного деления

a = y1

b = y2

While Abs(b - a) >= epsilon

c = (a + b) / 2

s = -p

For i = 1 To n

s = s + x(i) / ((1 + c) ^ i)

Next i

s1 = -p

For i = 1 To n

s1 = s1 + x(i) / ((1 + b) ^ i)

Next i

If s * s1 < 0 Then

a = c

Else

b = c

End If

Wend

MsgBox "Внутренняя норма доходности с точностью " + Trim(CStr(epsilon)) + " равна: " + CStr(c), vbInformation, "Решение"

Worksheets("Данные").Range("g8").Value = c

End Sub

Private Sub UserForm_Initialize()

Me.Caption = "Расчет внутренней нормы доходности»

TextBox1.SetFocus

End Sub



4. Результаты решения

Рассмотрим пример. Количество платежей равно 5. Объем начальных инвестиций равен 50. Размеры платежей указаны в таблице.

Платежи
-10
5
20
40
40

Найдем внутреннюю норму доходности t с точностью 0,001. Уравнение для нахождения внутренней нормы доходности имеет следующий вид:

Предварительно было установлено, что корень уравнения находится на отрезке [0; 1].

В результате решения значение внутренней нормы доходности равно 0,155 или 15,5% с точностью 0,001. Значение левой части уравнения приближенно равно ?0,046.



Литература

1. Уокенбах, Д.. Профессиональное программирование на VBA в Excel 2002. М.: Издательский дом "Вильямс", 2003.

2. Гарнаев А. Ю. Excel, VBA, Internet в экономике и финансах. СПб: БХВ - Петербург, 2003.

3. Гарнаев А. Ю. Самоучитель VBA - СПб.: БХВ - Санкт - Петербург, 2000.

4. Румянцева Е. Л., Трояновский В. М. Microsoft Excel как инструмент разработки информационных систем // Мир ПК, май, 2004.

5. Брусов П. Н. Финансовая математика. - М.: КНОРУС, 2010.

Размещено на Allbest.ru