Анализ эффективности вложений денежных средств в РКО

Call EndOf

End Sub

Sub ViewBirga()

Sheets("Биржа").Select

Call EndOf

End Sub

Sub ViewOst812()

Sheets("Остатки812").Select

Call EndOf

End Sub

Sub ViewOstBirga()

Sheets("ОстаткиБиржа").Select

Call EndOf

End Sub

Sub ViewProgram()

Sheets("Защита").Select

End Sub

'------------------------------ Печать Депозитария ---------------

Sub PrintDepo()

Dim BumNum; CliNum; i; j; k; a; n; Sign; s As Integer

Dim Flag As Boolean

Dim Code As Long

Dim Str As String

Dim DepoFil() As Integer

Dim Num As Integer

CurDate = Worksheets("Врем").Cells(1; 4)

Call FormBum

Sheets("Депо").Select

BumNum = Worksheets("Врем").Cells(1; 2)

Cells(3; 5) = Worksheets("Врем").Cells(1; 4)

Cells(3; 5).NumberFormat = "Д ММММ, ГГГГ"

Cells(3; 5).HorizontalAlignment = xlCenterAcrossSelection

Cells(3; 5).Font.Bold = True

Num = 9

For i = 1 To BumNum

Cells(6; i + 1) = Worksheets("Врем").Cells(i; 1)

Cells(6; i + 1).Font.Bold = True

Cells(6; i + 1).Interior.ColorIndex = 40

Cells(Num + 1; i + 1).Interior.ColorIndex = 15

Cells(Num + 1; i + 1) = ""

Cells(Num; i + 1).Interior.ColorIndex = 40

Cells(Num; i + 1) = ""

Cells(5; i + 1).Interior.ColorIndex = 40

Cells(Num; 1).Interior.ColorIndex = 40

Cells(Num; 1) = "Итого"

Cells(Num; 1).Font.Bold = True

Cells(Num; 1).Font.Italic = True

Cells(Num; 1).HorizontalAlignment = xlCenter

Cells(Num + 1; 1) = ""

Cells(Num + 1; 1).Interior.ColorIndex = 15

CliNum = Worksheets("Врем").Cells(1; 3)

ReDim DepoArray(CliNum; BumNum)

ReDim DepoFil(BumNum)

a = 2

While Worksheets("Сделки").Cells(a; 1) <> Empty

i = 1

While Worksheets("Клиенты").Cells(i + 1; 2) <> _

Worksheets("Сделки").Cells(a; 2)

If Worksheets("Клиенты").Cells(i + 1; 2) = Empty Then

MsgBox "Неверный номер клиента в Окне 'Сделки' строка: " + CStr(a)

Sheets("Сделки").Select

Cells(a; 2).Select

Exit Sub

End If

i = i + 1

Wend

k = 0

For j = 1 To BumNum

If Worksheets("Врем").Cells(j; 1) = Worksheets("Сделки").Cells(a;

3) Then

k = j

Exit For

End If

If k = 0 Then

a = a + 1

GoTo NNN

End If

If Not IsEmpty(Worksheets("Сделки").Cells(a; 4)) Then

Sign = 1

Else

Sign = -1

End If

If CurDate >= Worksheets("Сделки").Cells(a; 1) Then

If Worksheets("Сделки").Cells(a; 2) = FilialConst Then

DepoFil(k) = DepoFil(k) + Sign * Worksheets("Сделки").Cells(a; 6)

Else

DepoArray(i; k) = DepoArray(i; k) + Sign *

Worksheets("Сделки").Cells(a; 6)

End If

a = a + 1

NNN:

Wend

n = 7

For i = 1 To CliNum

Flag = False

For k = 1 To BumNum

If DepoArray(i; k) > 0 Then Flag = True

If Flag Then

Str = Format(Worksheets("Клиенты").Cells(i + 1; 2); "0000000000")

Str = Right(Str; 5)

Cells(n; 1).NumberFormat = "@"

Cells(n; 1).Font.Bold = True

Cells(n; 1).HorizontalAlignment = xlCenter

Cells(n; 1).Font.Italic = False

Cells(n; 1).Interior.ColorIndex = 2

Cells(n; 1) = Str

For k = 1 To BumNum

If DepoArray(i; k) <> 0 Then

Cells(n; k + 1) = DepoArray(i; k)

Else

Cells(n; k + 1) = ""

End If

Cells(n; k + 1).Font.Bold = False

Cells(n; k + 1).Font.Italic = False

Cells(n; k + 1).Interior.ColorIndex = 2

If n = 7 Then

n = n + 4

Else

n = n + 1

End If

'расчет по филиалу

Cells(8; 1) = "Филиал"

Cells(8; 1).Font.Bold = True

Cells(8; 1).HorizontalAlignment = xlCenter

Cells(8; 1).Font.Italic = False

Cells(8; 1).Interior.ColorIndex = 2

For k = 1 To BumNum

If DepoFil(k) <> 0 Then

Cells(8; k + 1) = DepoFil(k)

Else

Cells(8; k + 1) = ""

End If

Cells(8; k + 1).Font.Bold = False

Cells(8; k + 1).Font.Italic = False

Cells(8; k + 1).Interior.ColorIndex = 2

For i = 1 To BumNum

Cells(n; i + 1).Interior.ColorIndex = 40

s = 0

For k = 11 To n - 1

s = s + Cells(k; i + 1)

Cells(n; i + 1).Value = s

For i = 1 To BumNum

Cells(9; i + 1) = Cells(7; i + 1) + Cells(8; i + 1)

Cells(n; 1).Interior.ColorIndex = 40

Cells(n; 1) = "Итого 9998"

Cells(n; 1).Font.Bold = True

Cells(n; 1).Font.Italic = True

Range("A1:Z200").Borders(xlLeft).LineStyle = xlNone

Range("A1:Z200").Borders(xlRight).LineStyle = xlNone

Range("A1:Z200").Borders(xlTop).LineStyle = xlNone

Range("A1:Z200").Borders(xlBottom).LineStyle = xlNone

Range("A1:Z200").BorderAround LineStyle:=xlNone

Range(Cells(5; 1); Cells(n; BumNum + 1)).Borders(xlLeft).Weight =

xlThin

Range(Cells(5; 1); Cells(n; BumNum + 1)).Borders(xlRight).Weight =

xlThin

Range(Cells(5; 1); Cells(n; BumNum + 1)).Borders(xlTop).Weight =

xlThin

Range(Cells(5; 1); Cells(n; BumNum + 1)).Borders(xlBottom).Weight =

xlThin

Range(Cells(5; 1); Cells(n; BumNum + 1)).BorderAround

Weight:=xlMedium

Range(Cells(n + 1; 1); Cells(100; 30)).Delete shift:=xlToLeft

Range(Cells(1; BumNum + 2); Cells(100; 30)).Delete shift:=xlToLeft

If DialogPrint("Депо"; 1) Then Exit Sub

Call EditOstBirga(DilerConst)

End Sub

'-------------------------------- Печать Отчеты клиентам -----------

Sub PrintOtchClient()

Dim Sheet; Ost812 As Object

Dim i; j; d; a; Col; m; MM; NN; MMM; k; b; q As Long

Dim FlagBuy; FlagCell; FlagDeal; FlagDepo As Boolean

Dim CliNum As Long

Dim ComStr; StrComS As String

Dim BumNum; z; z1; Index As Integer

Dim s; sum; SumBuy; Ost; SumCom; ComBirga; ComDiler; ComSum As Double

Dim Com As Double

Dim OstIn; OstOut; OstBegin; OstEnd As Double

Dim RowNum As Long

Dim OstInDate; OstOutDate As String

Dim DoFlag As Boolean

Dim Auk As Boolean

Set Sheet = Worksheets("Сделки")

Sheet.Range("A2").Sort Key1:=Sheet.Range("A2"); Order1:=xlAscending;

Key2:=Sheet.Range("B2"); Order2:=xlAscending;

Key3:=Sheet.Range("D2"); Order3:=xlAscending;

Header:=xlYes; OrderCustom:=1; _

MatchCase:=False; Orientation:=xlTopToBottom

CurDate = Worksheets("Врем").Cells(1; 4)

Worksheets("ОтчетыИнвесторам").Select

i = 2

FlagDeal = False

FlagBuy = True

FlagCell = True

NN = 29 ' начало

m = NN

Range(Cells(NN - 1; 2); Cells(NN + 200; 6)).Delete shift:=xlToLeft

Rows(CStr(NN - 1) + ":" + CStr(NN - 1)).RowHeight = 28

Rows(CStr(NN - 1) + ":" + CStr(NN - 1)).WrapText = True

Rows(CStr(NN - 1) + ":" + CStr(NN - 1)).HorizontalAlignment =

xlCenter

Rows(CStr(NN - 1) + ":" + CStr(NN - 1)).VerticalAlignment = xlBottom

Cells(NN - 1; 2) = "№ выпуска"

Cells(NN - 1; 3) = "Дата погашения"

Cells(NN - 1; 4) = "Цена сделки"

Cells(NN - 1; 5) = "Количество"

Cells(NN - 1; 6) = "Сумма сделки"

Cells(NN - 3; 3) = "Совершенные сделки на рынке РКО"

Cells(NN - 3; 3).Font.Bold = True

sum = 0

SumBuy = 0

SumCom = 0

ComBirga = 0

Call FormBum

BumNum = Worksheets("Врем").Cells(1; 2)

ReDim BumArray(BumNum)

ReDim BumArrayV(BumNum)

Index = CInt(InputBox("Введите номер 1-го ордера"))

Do While Sheet.Cells(i; 1) <> Empty

If Sheet.Cells(i; 1) = CurDate And Sheet.Cells(i; 2) <> DilerConst

Then

FlagDeal = True

If FlagBuy And Sheet.Cells(i; 4) <> Empty Then

Покупка = True

CliNum = Sheet.Cells(i; 2)

Cells(m; 2) = "Покупка"

Cells(m; 2).HorizontalAlignment = xlLeft

Range(Cells(m; 2); Cells(m; 6)).Interior.ColorIndex = 15

m = m + 1

MM = m

FlagBuy = False

End If

If FlagCell And Sheet.Cells(i; 4) = Empty Then

If Not FlagBuy Then

s = 0

Col = 0

SumCom = 0

ComBirga = 0

For a = MM To m - 1

Cells(a; 6) = Cells(a; 4) * Cells(a; 5) * 10

If Cells(a; 4) <> 100 Then

SumCom = SumCom + Cells(a; 4) * Cells(a; 5) * 10

ComBirga = ComBirga + _

CDbl(Format(Cells(a; 4) * Cells(a; 5) * 0,1 *

Worksheets("Инфо").Cells(1; 2) + 0,001; "0,00"))

Else

Погашение = True

End If

Cells(a; 6).NumberFormat = "# ###"

s = s + Cells(a; 6)

Col = Col + Cells(a; 5)

Next a

sum = sum + s

SumBuy = s

Cells(m; 6) = s

Cells(m; 6).NumberFormat = "# ###"

Cells(m; 5) = Col

Cells(m; 2) = "Итого"

m = m + 1

End If

CliNum = Sheet.Cells(i; 2)

Cells(m; 2) = "Продажа"

Продажа = True

Cells(m; 2).HorizontalAlignment = xlLeft

Range(Cells(m; 2); Cells(m; 6)).Interior.ColorIndex = 15

m = m + 1

MM = m

FlagCell = False

End If

Cells(m; 2) = Sheet.Cells(i; 3)

q = 2

While Worksheets("Бумаги").Cells(q; 1) <> Empty

If Worksheets("Бумаги").Cells(q; 1) = Cells(m; 2) Then

Cells(m; 3) = Worksheets("Бумаги").Cells(q; 3)

Cells(m; 3).NumberFormat = "ДД.ММ.ГГ"

End If

q = q + 1

Wend

If Sheet.Cells(i; 4) <> Empty Then

Cells(m; 4) = Sheet.Cells(i; 4)

Else

Cells(m; 4) = Sheet.Cells(i; 5)

End If

Cells(m; 4).NumberFormat = "0,00"

Cells(m; 5) = Sheet.Cells(i; 6)

m = m + 1

If CliNum <> Sheet.Cells(i + 1; 2) Or Sheet.Cells(i + 1; 1) <>

CurDate Then

s = 0

Col = 0

For a = MM To m - 1

Cells(a; 6) = Cells(a; 4) * Cells(a; 5) * 10

If Cells(a; 4) <> 100 Then

SumCom = SumCom + Cells(a; 4) * Cells(a; 5) * 10

ComBirga = ComBirga + _

CDbl(Format(Cells(a; 4) * Cells(a; 5) * 0,1 *

Worksheets("Инфо").Cells(1; 2) + 0,001; "0,00"))

Else

Погашение = True

End If

Cells(a; 6).NumberFormat = "# ###,00"

s = s + Cells(a; 6)

Col = Col + Cells(a; 5)

Next a

sum = sum + s

If FlagCell Then SumBuy = s

Cells(m; 6) = s

Cells(m; 6).NumberFormat = "# ###,00"

Cells(m; 5) = Col

Cells(m; 2) = "Итого"

Cells(5; 4) = CliNum

If CliNum = FilialConst Then Cells(5; 4) = DilerConst

k = 2

While Worksheets("Клиенты").Cells(k; 1) <> Empty

If Worksheets("Клиенты").Cells(k; 2) = CliNum Then

Cells(4; 4) = Worksheets("Клиенты").Cells(k; 1)

End If

k = k + 1

Wend

Range(Cells(NN - 1; 2); Cells(m; 6)).Borders(xlLeft).Weight =

xlThin

Range(Cells(NN - 1; 2); Cells(m; 6)).Borders(xlRight).Weight =

xlThin

Range(Cells(NN - 1; 2); Cells(m; 6)).Borders(xlTop).Weight =

xlThin

Range(Cells(NN - 1; 2); Cells(m; 6)).Borders(xlBottom).Weight =

xlThin

Range(Cells(NN - 1; 2); Cells(m; 6)).BorderAround

Weight:=xlMedium

For b = 1 To BumNum

BumArray(b) = 0

BumArrayV(b) = 0

b = 2

While Worksheets("Сделки").Cells(b; 1) <> Empty

If CurDate >= Worksheets("Сделки").Cells(b; 1) And _

CliNum = Worksheets("Сделки").Cells(b; 2) Then

z = 0

For z1 = 1 To BumNum

If Worksheets("Врем").Cells(z1; 1) =

Worksheets("Сделки").Cells(b; 3) Then

z = z1

Exit For

End If

If z <> 0 Then

If Not IsEmpty(Worksheets("Сделки").Cells(b; 4)) Then

If CurDate > Worksheets("Сделки").Cells(b; 1) Then

BumArrayV(z) = BumArrayV(z) + Worksheets("Сделки").Cells(b;

End If

BumArray(z) = BumArray(z) + Worksheets("Сделки").Cells(b; 6)

Else

If CurDate > Worksheets("Сделки").Cells(b; 1) Then

BumArrayV(z) = BumArrayV(z) - Worksheets("Сделки").Cells(b;

End If

BumArray(z) = BumArray(z) - Worksheets("Сделки").Cells(b; 6)

End If

b = b + 1

Wend

' M+4

MMM = m + 5

Rows(CStr(m + 1) + ":" + CStr(m + 200)).Delete

FlagDepo = False

For b = 1 To BumNum

If BumArray(b) > 0 Or BumArrayV(b) > 0 Then

FlagDepo = True

Cells(MMM; 2) = Worksheets("Врем").Cells(b; 1)

If BumArrayV(b) < BumArray(b) Then

Cells(MMM; 4) = BumArray(b) - BumArrayV(b)

Else

If BumArrayV(b) > BumArray(b) Then

Cells(MMM; 5) = BumArrayV(b) - BumArray(b)

End If

Cells(MMM; 3) = BumArrayV(b)

Cells(MMM; 6) = BumArray(b)

MMM = MMM + 1

End If

If FlagDepo Then

Rows(CStr(m + 4) + ":" + CStr(m + 4)).RowHeight = 28

Rows(CStr(m + 4) + ":" + CStr(m + 4)).WrapText = True

Rows(CStr(m + 4) + ":" + CStr(m + 4)).HorizontalAlignment =

xlCenter

Rows(CStr(m + 4) + ":" + CStr(m + 4)).VerticalAlignment =

xlBottom

Cells(m + 4; 2) = "№ выпуска"

Cells(m + 4; 3) = "Входящий остаток"

Cells(m + 4; 4) = "Куплено"

Cells(m + 4; 5) = "Продано/ Погашено"

Cells(m + 4; 6) = "Исходящий остаток"

Cells(m + 2; 3).Font.Bold = True

Cells(m + 2; 3) = "Количество бумаг, принадлежащих Инвестору

(штук)"

Range(Cells(m + 4; 2); Cells(MMM - 1; 6)).Borders(xlLeft).Weight

= xlThin

Range(Cells(m + 4; 2); Cells(MMM - 1;

6)).Borders(xlRight).Weight = xlThin

Range(Cells(m + 4; 2); Cells(MMM - 1; 6)).Borders(xlTop).Weight

= xlThin

Range(Cells(m + 4; 2); Cells(MMM - 1;

6)).Borders(xlBottom).Weight = xlThin

Range(Cells(m + 4; 2); Cells(MMM - 1; 6)).BorderAround

Weight:=xlMedium

End If

' ------------------------------------------------------

' - расчет остатков

Set Ost812 = Worksheets("Остатки812")

Ost812.Range("B2").Sort Key1:=Ost812.Range("B2");

Order1:=xlAscending; _

Key2:=Ost812.Range("A2");

Order2:=xlDescending; _

Header:=xlYes; OrderCustom:=1; _

MatchCase:=False; Orientation:=xlTopToBottom

OstIn = 0

OstOut = 0

OstBegin = 0

OstInDate = ""

OstOutDate = ""

RowNum = 0

k = 2

DoFlag = True

Do While Ost812.Cells(k; 1) <> Empty

If Ost812.Cells(k; 2) = CliNum And DoFlag Then

If Ost812.Cells(k; 1) < CurDate Then

OstBegin = Ost812.Cells(k; 8)

Else

Do While Ost812.Cells(k; 1) <> Empty

If Ost812.Cells(k; 2) <> CliNum Then Exit Do

If Ost812.Cells(k; 1) = CurDate Then

OstBegin = Ost812.Cells(k; 3)

Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11

МЕНЮ

Анализ эффективности вложений денежных средств в РКО