Cells(m; 2) = AllVol

Cells(m; 2).NumberFormat = "0"

Cells(m; 3) = SumPog11 / SumPog22

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

Cells(m; 4) = SumPriobr11 / SumPriobr22

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

Range(Cells(m; 1); Cells(m; 4)).Interior.ColorIndex = 15

Range(Cells(7; 1); Cells(m; 4)).Borders(xlLeft).Weight = xlThin

Range(Cells(7; 1); Cells(m; 4)).Borders(xlRight).Weight = xlThin

Range(Cells(7; 1); Cells(m; 4)).Borders(xlTop).Weight = xlThin

Range(Cells(7; 1); Cells(m; 4)).Borders(xlBottom).Weight = xlThin

Range(Cells(7; 1); Cells(m; 4)).BorderAround Weight:=xlMedium

Range(Cells(m; 1); Cells(m; 4)).BorderAround Weight:=xlMedium

Cells(m + 1; 1) = "Стоимость портфеля по балансу"

Cells(m + 2; 1) = "Текущая стоимость потфеля"

Cells(m + 1; 1).Font.Bold = True

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

Range(Cells(m + 1; 1); Cells(m + 2; 4)).BorderAround Weight:=xlMedium

Cells(m + 1; 4) = PortfelBalance * 10

Cells(m + 1; 4).NumberFormat = "### ### ###,00"

Cells(m + 1; 4).Font.Bold = True

Cells(m + 2; 4) = PortfelCost * 10

Cells(m + 2; 4).NumberFormat = "### ### ###,00"

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

If DialogPrint("Портфель2"; 1) Then Exit Sub

End Sub

'-------------------------------- Печать Журнала лицевого учета ---------

Sub PrintMagazine()

Dim Sheet As Object

Dim i; k; BumNum; m; m1; j As Long

Dim Bum(ConstMaxBum) As Long

Dim Volume(); BiginIndex(); dates(); V(); Vol As Integer

Dim sum; Price() As Double

Dim DateMas() As Date

Dim Flag; BumIndex() As Boolean

Dim ComBirga; ComMas(); MagMas(); Mag(4) As Double

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

i = 2

Flag = True

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

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

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

Flag = False

Exit Do

End If

i = i + 1

Loop

If Flag Then

MsgBox "Сделок в текущий день не было"

Exit Sub

End If

Set Sheet = Worksheets("Бумаги")

i = 2

BumNum = 0

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

If (Sheet.Cells(i; 2) <= CurDate And Sheet.Cells(i; 3) >= CurDate) Then

Bum(BumNum + 1) = Sheet.Cells(i; 1)

BumNum = BumNum + 1

End If

i = i + 1

Wend

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

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

Key2:=Range("D2"); Order2:=xlAscending; _

Header:=xlYes; OrderCustom:=1; _

MatchCase:=False; Orientation:=xlTopToBottom

ReDim Volume(BumNum; MaxCount)

ReDim Price(BumNum; MaxCount)

ReDim DateMas(BumNum; MaxCount)

ReDim dates(BumNum); V(BumNum); BeginIndex(BumNum)

ReDim BumIndex(BumNum); ComMas(BumNum)

ReDim MagMas(BumNum; 4)

For i = 1 To BumNum

ComMas(i) = 0

dates(i) = 1

Next i

i = 2

While Cells(i; 1) <> Empty And CurDate > Cells(i; 1)

If Cells(i; 2) = DilerConst And Cells(i; 7) <> "списание" _

And Cells(i; 7) <> "зачисление" Then

Flag = True

For k = 1 To BumNum ' поиск номера бумаги

If Cells(i; 3) = Bum(k) Then

Flag = False

Exit For

End If

Next k

If Flag Then GoTo cont

If Not IsEmpty(Cells(i; 4)) Then

Volume(k; dates(k)) = Cells(i; 6)

Price(k; dates(k)) = Cells(i; 4)

DateMas(k; dates(k)) = Cells(i; 1)

dates(k) = dates(k) + 1

V(k) = V(k) + Cells(i; 6)

Else

V(k) = V(k) - Cells(i; 6)

End If

End If

cont:

i = i + 1

Wend

For k = 1 To BumNum

For i = dates(k) To 1 Step -1

If V(k) > Volume(k; i) Then

V(k) = V(k) - Volume(k; i)

Else

Volume(k; i) = V(k)

BeginIndex(k) = i

Exit For

End If

Next i

Next k

For k = 1 To BumNum

BumIndex(k) = False

If V(k) > 0 Then BumIndex(k) = True

Next k

ComBirga = Worksheets("Инфо").Cells(1; 2)

i = 2

While Cells(i; 1) <> Empty

If (Cells(i; 1) = CurDate And Cells(i; 2) = DilerConst) _

And (Cells(i; 7) <> "зачисление" And Cells(i; 7) <> "списание") Then

For k = 1 To BumNum

If Cells(i; 3) = Bum(k) Then

BumIndex(k) = True

If Not IsEmpty(Cells(i; 4)) Then

ComMas(k) = ComMas(k) + Format(Cells(i; 4) * Cells(i; 6) * ComBirga * 0,1 + 0,0001; "0,00")

Else

If Cells(i; 5) <> 100 Then

ComMas(k) = ComMas(k) + Format(Cells(i; 5) * Cells(i; 6) * ComBirga * 0,1 + 0,0001; "0,00")

End If

End If

End If

Next k

End If

i = i + 1

Wend

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

Worksheets("Журнал лицевого учета").Select

Cells(5; 1) = CurDate

Cells(49; 2) = ComBirga

Покупка = False

Продажа = False

Vol = 0

sum = 0

For k = 1 To BumNum

If BumIndex(k) Then

m = 7

Range("A7:C43").ClearContents

Range("E7:G43").ClearContents

Vol = 0

sum = 0

For i = BeginIndex(k) To dates(k)

If Volume(k; i) > 0 Then

Cells(m; 1) = DateMas(k; i)

Cells(m; 2) = Volume(k; i)

Cells(m; 3) = Format(Price(k; i); "0,00")

Vol = Vol + Volume(k; i)

sum = sum + Format(Price(k; i); "0,00") * Volume(k; i) * 10

m = m + 1

End If

Next i

Cells(6; 2) = Vol

Cells(6; 4) = sum

Cells(49; 3) = ComMas(k)

Cells(5; 3) = CStr(Bum(k)) + "MFTS"

i = 2

m1 = 7

j = BeginIndex(k)

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

If Sheet.Cells(i; 1) = CurDate And Sheet.Cells(i; 3) = Bum(k) And _

Sheet.Cells(i; 7) <> "зачисление" And Sheet.Cells(i; 7) <> "списание" And _

Sheet.Cells(i; 2) = DilerConst Then

If Not IsEmpty(Sheet.Cells(i; 4)) Then

Покупка = True

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

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

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

Volume(k; dates(k)) = Sheet.Cells(i; 6)

Price(k; dates(k)) = Sheet.Cells(i; 4)

DateMas(k; dates(k)) = Sheet.Cells(i; 4)

dates(k) = dates(k) + 1

m = m + 1

Else

Продажа = True

Vol = Sheet.Cells(i; 6)

If Vol < Volume(k; j) Then

Cells(m1; 5) = Vol

Cells(m1; 6) = Format(Price(k; j); "0,00")

Cells(m1; 7) = Sheet.Cells(i; 5)

Volume(k; j) = Volume(k; j) - Sheet.Cells(i; 6)

m1 = m1 + 1

Else

If Volume(k; j) = 0 Then j = j + 1

While Vol > Volume(k; j) And Volume(k; j) <> Empty

Cells(m1; 5) = Volume(k; j)

Cells(m1; 6) = Format(Price(k; j); "0,00")

Cells(m1; 7) = Sheet.Cells(i; 5)

Vol = Vol - Volume(k; j)

j = j + 1

m1 = m1 + 1

Wend

If Volume(k; j) <> Empty Then