Файл: Создание информационнопрограммной системы для анализа финансового состояния банка.pdf

41
составляющих, тогда действие кнопки распространится на все из них.
Если составляющее является ссылкой на показатель, то использование кнопок не имеет эффекта – берется результат показателя.
• Для удаления составляющих предусмотрена нижняя кнопка справа от списка -
«Удалить». Для удаления нескольких составляющих необходимо выделить их перед нажатием кнопки.
Каждое составляющее имеет свой автоматически определяемый номер, для того, чтобы на него можно было однозначно сослаться в формуле. Ссылка в формуле строится как английская буква «C» и номер составляющего. Например, формула «C1-C2» означает, что показатель считается как разность первого составляющего и второго.
В формуле могут быть использованы любые функции, поддерживаемые Microsoft
Excel.
Для редактирования формулы предназначено текстовое поле в нижнем правом углу окна редактора.
Редактор показателей реализован в виде элемента управления ActiveX.
3.7. Построение запроса и представление результатов
Окна запроса дают информацию об ограничениях, накладываемых на результат запроса. Эта информация напрямую преобразуется в формат языка SQL.
Файл с данными по балансовым счетам, из которого производится выборка, имеет следующую структуру:
• Дата получения баланса (data)
• Номер филиала (Onum)
• Балансовый счет второго порядка (Bal)
• Тип счета (сейчас используются только балансовые) (Typ)
• Код валюты (CodV)
Если валюта – не рубли, то следующие поля содержат информацию о сумме в пересчете на рубли.
• Обороты по активу с начала года в тысячах (Deb)
• Обороты по пассиву с начала года в тысячах (Kre)
• Активный остаток в тысячах (RestA)
• Пассивный остаток в тысячах (RestP)

42
• Обороты по активу с начала года в рублях и копейках (Od)
• Обороты по пассиву с начала года в рублях и копейках (Ok)
• Активный остаток в рублях и копейках (Sa)
• Пассивный остаток в рублях и копейках (Sp)
Если валюта – рубли, то следующие поля – нулевые.
• Обороты по активу с начала года в валюте (Vdeb)
• Обороты по пассиву с начала года в валюте (Vkre)
• Активный остаток в валюте (VRestA)
• Пассивный остаток в валюте (VrestP)
Как видно из структуры таблицы, естественным образом формируются ограничения на даты, филиалы, валюты и счета второго порядка. Этого достаточно для формирования запросов по счетам второго порядка.
3.7.1. Формирование листа счетов
Запрос к базе формируется тривиально, поскольку все ограничения - даты, филиал, валюта и счета второго порядка находят однозначное соответствие в виде атрибута таблицы баланса.
Пример запроса по филиалу №8 данных по счетам 20202 и 42301 за время с 1 по 4 февраля 2000 года:
Select data, Onum, bal, sum(Deb), sum(Kre), sum(RestA) , sum (RestP) from bal1000
WHERE Onum = 8 And Data>= CAST ('01.02.2000' AS DATE MASK [dd.mm.yyyy]) AND
Data<= CAST ('04.02.2000' AS DATE MASK [dd.mm.yyyy]) AND bal in ('20202','42301') group by data,bal,onum order by data,bal,onum.
Результат запроса выводится на лист Excel следующим образом:
Дата
Филиал
Счет
Обороты (А)
Обороты (П)
Остаток (А)
Остаток (П)
Data Onum Bal Deb
Kre
RestA
RestP
Каждая колонка листа однозначно соответствует полю запроса, поэтому алгоритм заполнения прост. В случае запроса по валютам лист расширяется еще на 5 колонок – на код валюты и остатки/обороты в валюте, но это не усложняет задачу.
В случае использования суммирования по счетам в запросе, данные по остаткам и оборотам группируются только по дате и филиалу с использованием операции

43
суммирования, а колонка «Счет» заполняется именем, которое указывается в окне запроса по счетам.
3.7.2. Формирование листа составляющих
В случае запроса показателей тривиальная схема формирования запроса не проходит – по выбранному показателю необходимо восстановить список счетов второго порядка, его формирующих. Для этого по показателю необходимо извлечь все его составляющие, а затем уже для них извлечь счета второго порядка, которые и будут участвовать в запросе. При этом необходимо учесть, что в качестве составляющего может присутствовать и показатель, то есть, его тоже придется разворачивать.
Для решения этой задачи был разработан следующий алгоритм (приведен схематично, конкретную реализацию можно посмотреть на прилагаемой дискете с исходным текстом программы):
Имеется массив показателей. Npok – их изначальное количество. Это числоо показателей в запросе. iPok – номер текущего (обрабатываемого) показателя iPok=0

Цикл, пока iPok< Npok
iPok= iPok+1
Взять из базы список составляющих для текущего показателя;
Цикл по всем составляющим
Если составляющее – объект из плана счетов (счет/раздел)
То выбрать счета второго порядка, его составляющие; добавить их в строку запроса (те, которых там еще нет);
Иначе (если это - показатель)
Если такого показателя в массиве еще нет
То
добавить показатель в массив;
nPok=nPok+1 – увеличить число показателей;
Иначе сослаться на уже имеющийся;
Конец Если
Конец Если
Конец цикла
Конец цикла

44
Таким образом формируется список счетов второго порядка, необходимый для формирования запроса. После обработки запроса можно провести заполнение листа счетов тривиальным образом. Но нам необходимо сформировать из этой информации составляющие, чтобы потом посчитать по формуле значение показателя.
Значение показателя должно однозначно определяться датой и филиалом. В случае раскрытия по валютам – еще и кодом валюты, но этот случай ввиду его аналогичности подробно рассматриваться не будет.
Исходя из вышесказанного, решено организовать лист составляющих (обозначим их «С») по следующей схеме (пример для двух показателей – «П»):
Дата
Филиал
П1 П2 С1 от П1 …
СN1 от П1
С1 от П2 … СN2 от П2
СN1 – последнее составляющее П1. СN2 – последнее составляющее П2.
В случае, если в качестве составляющего в П1 входил некоторый показатель П3 данные будут расположены следующим образом:
… П1 П2 С1 от П1 … Сi от П1 = П3 … СN2 от П2
С1 от П3 … до CN3
То есть, каждый показатель имеет номер «первой колонки», с которой на листе идут подряд его составляющие. Этот номер рассчитывается соответствующей процедурой. При «валютном» запросе число колонок на составляющие увеличивается вдвое – отдельно формируются валютные и рублевые составляющие. На значение показателя тоже отводится две колонки – на валюту и на рублевый эквивалент.
После такого разложения в каждой строке содержится вся информация, необходимая для расчета показателей. Для формирования составляющих в процессе анализа запроса необходимо для каждой его записи смотреть – не является ли полученный счет частью некоторого составляющего, если да – увеличивать это составляющее на соответствующее число (определяемое необходимой частью информации о счете для составляющего – пассив/актив, остаток/обороты).
Ввиду того, что запрос отсортирован по датам, смена даты при просмотре является сигналом для начала построения следующей строки листа составляющих.

45
Так получаются составляющие. Для получения значений показателей необходимо в соответствующие им на листе ячейки подставить формулы, полученные из базы, где ссылки на составляющие по номерам будут заменяться ссылками на ячейки листа, где эти составляющие расположены. Поскольку составляющие на листе распределены подряд и известен номер «первой колонки» для показателя - задача о преобразовании формул решается тривиально.
3.7.3. Формирование листа показателей
Лист показателей содержит итоговую информацию о результатах запроса в виде значений показателей. Значения показателей в каждой строке берутся из соответствующей строки листа составляющих.
Лист показателей выглядит следующим образом:
Дата
Филиал
П1
П2
П3 … ПN
В случае запроса валютной части показателей он будет выглядеть так:
Дата
Филиал
Код
Валюты
П1
П1 Val … ПN
ПN Val
3.7.4. Формирование листа баланса
Задача о запросе всего баланса рассматривается отдельно, поскольку требует представления результата в особом виде:
Период 1
Период 2…
Входящий остаток
Обороты за период
Исходящий остаток
Среднее хронологи- ческое за период
Обороты за период
…
Счет
А
П
А
П
А
П
А
П
А
П
А
П

46
Запрос формируется с указанием списка дат. Период 1 – это время от первой даты до второй, включая первую и исключая вторую. Например, если в запросе заданы даты 1 января 2000, 1 февраля 2000 и 1 марта 2000 то Период 1 будет рассматриваться как время с 1 по 31 января 2000 года. Период 2 определяется аналогично, в данном примере это время с 1 по 29 февраля 2000 года.
Под входящим остатком имеется ввиду остаток счета, который был до начала работы в первую дату периода. Здесь необходимо сказать, что файл данных баланса, с которым работает программа, имеет следующую организацию: запись содержит информацию об остатках, которые были после окончания работы в указанную дату. В бухгалтерском учете говорится, что это данные ЗА некоторое число. Входящая информация обозначается как данные НА некоторое число. Соответственно, если у записи дата 1 февраля, то там содержится информация ЗА 1 февраля. Чтобы узнать данные о входящих остатках – информацию НА 1 февраля, необходимо взять информацию ЗА предыдущий рабочий день – 31 января.
В исходном примере, для того, чтобы узнать входящий остаток для 1 января 2000 года необходимо извлечь данные за 31 декабря 1999 года. Учитывая то, что 31 декабря банк не работал, данные об остатках будут взяты за 30 декабря.
Исходящий остаток периода – это остаток на счете после окончания работы за последнюю дату периода. В данном примере – это данные, которые были сформированы
31 января 2000 года. Эти же данные будут являться входящим остатком на Период 2.
Видно, что дата 31 января представляет интерес и для Периода 1 как исходящая и для
Периода 2 как входящая, в то время как информация об остатках за дату 1 февраля оказывается для Периода 2 неиспользованной. Отсюда следует вывод, что необходимыми для расчета остатков являются не те даты, которые указаны в запросе (1 января, 1 февраля,
1 марта), а те, которые являются для них «входящими» (30 декабря, 31 января и 29 февраля). Поэтому 1 марта в вышеприведенном примере - это способ показать в запросе, что нужна информация «за февраль». Данные за 1 марта из базы извлекаться не будут.
Поскольку в базе информация по оборотам хранится как накапливаемое значение величины оборотов с начала года по дату записи, то графа «обороты за период» вычисляется как разность данных за две даты, которые являются «входящими» для дат- границ периода. Для того, чтобы в вышеприведенном примере посчитать обороты за февраль 2000 года, необходимо от величины оборотов НА 1 марта (это будут данные за 29 февраля) – исходящие данные периода, отнять данные НА 1 февраля (за 31 января) – входные данные периода. В случае определения оборотов за январь 2000 года необходимо

47
скорректировать то, что данные за 30 декабря содержат информацию по величине оборотов от начала предшествующего, 1999 года. Для корректности в качестве данных необходимо взять модуль разность величины этих оборотов с величиной накопленных оборотов «к концу 1999 года», что в данном примере будет равно нулю, так как 30 декабря – это и есть данные конца года.
Особый интерес в этой задаче представляет графа «Среднее хронологическое».
При его расчете пользуются следующей формулой:
d
ср.хр.
= ( d
вх
/2 + d
1
+ d
2
+ … + d
n-1
+ d
n
/2 ) / n-1 ,
где n – число дней в периоде, d
вх
– входные данные периода. d
i
– данные за i ый день.
При использовании этой формулы возникает следующая проблема: банк работает не каждый день. За выходные и праздничные дни, например – 1 января, данные в базе отсутствуют. Для выхода из этой ситуации берется информация за предыдущий рабочий день. Это означает, что в обычную рабочую неделю данные за пятницу будут также считаться данными за субботу и за воскресенье, поэтому войдут в сумму с коэффициентом умножения 3. Если заранее рассчитать, с какими коэффициентами войдут в сумму данные за каждую дату, то при анализе запроса по дате записи можно будет сразу узнать, с каким коэффициентом надо брать данные в ней.
Для расчета коэффициентов разработан следующий алгоритм (начало процедуры
DoSelectBalMn модуля BalMn):
Задан список дат запроса – границ периодов – DatalistTask.
Определить дату DataBegin, информация за которую будет использована как входные данные на первую дату из Datalist. Например, если первая дата – 1 января 2000 года, то для нее надо взять данные за 30 декабря 1999 года.
Определить аналогичным образом список дат DataList на основе исходного списка DatalistTask. Таким образом определятся даты с входной информацией для каждого периода. Кроме того, эти даты могут оказаться «разделяемыми».
Например, если запрошены периоды 1 января – 31 января и 1 февраля – 28 февраля, а 1 февраля выпадает на воскресенье, то пятница – 30 января будет «разделяемой» датой, поскольку данные для нее будут использованы для расчета среднего хронологического не только в первом периоде с коэффициентом умножения 2 (за 30 и 31 января), но и во втором – с коэффициентом 1 (за 1 февраля).
Здесь необходимо заметить, что в любом случае данные за 30 января будут использованы для второго периода как входные (d вх в формуле). Но вышеприведенном случае «разделения» они же используются и как d
1