71 lines
3.6 KiB
QBasic
71 lines
3.6 KiB
QBasic
Attribute VB_Name = "Module2"
|
|
Sub Button2_Click()
|
|
|
|
Set cwb = ActiveWorkbook
|
|
Set summarySheet = cwb.Worksheets("SUMMARY")
|
|
Set secinfoSheet = cwb.Worksheets("SECURITIES INFO")
|
|
Set dataSheet = cwb.Worksheets("INPUTS")
|
|
Set fundingSheet = cwb.Worksheets("FUNDING")
|
|
Set controlSheet = cwb.Worksheets("control")
|
|
|
|
local_date_format = controlSheet.Range("DT_FORM_EX").NumberFormat
|
|
|
|
issue_name = InputBox("Введите краткое название бумаги")
|
|
issue_isin = InputBox("Введите ISIN бумаги")
|
|
issue_amount = InputBox("Введите объем покупки бумаги, шт")
|
|
issue_price = InputBox("Введите цену покупки/размещения бумаги")
|
|
issue_date = InputBox("Введите дату размещения в формате " & local_date_format, "User date", Format(Now(), local_date_format))
|
|
issue_currency = InputBox("Введите валюту выпуска: RUB / CNY")
|
|
issue_notional = InputBox("Введите номинал бумаги, ед. валюты")
|
|
|
|
If Not IsDate(issue_date) Then
|
|
issue_date = CDate(issue_date)
|
|
issue_date = issue_date * 1
|
|
End If
|
|
|
|
issue_coupon = InputBox("Введите ставку купона по выпуску")
|
|
issue_yield = InputBox("Введите доходность выпуска")
|
|
|
|
last_row = secinfoSheet.Cells(secinfoSheet.Rows.Count, 2).End(xlUp).Row
|
|
secinfoSheet.Cells(last_row + 1, 1) = issue_isin
|
|
secinfoSheet.Cells(last_row + 1, 2) = issue_name
|
|
secinfoSheet.Cells(last_row + 1, 3) = issue_price
|
|
secinfoSheet.Cells(last_row + 1, 9) = issue_notional
|
|
secinfoSheet.Cells(last_row + 1, 10) = issue_currency
|
|
secinfoSheet.Cells(last_row + 1, 12) = issue_date
|
|
secinfoSheet.Cells(last_row + 1, 13) = issue_amount
|
|
secinfoSheet.Cells(last_row + 1, 14) = issue_coupon
|
|
secinfoSheet.Cells(last_row + 1, 15) = issue_yield
|
|
|
|
secinfoSheet.Cells(last_row, 7).Copy
|
|
secinfoSheet.Range("G" & last_row + 1).PasteSpecial Paste:=xlPasteFormulas
|
|
|
|
data_last_row = dataSheet.Cells(dataSheet.Rows.Count, 2).End(xlUp).Row
|
|
|
|
dataSheet.Cells(data_last_row + 1, 2) = issue_date
|
|
dataSheet.Cells(data_last_row + 1, 5) = issue_name
|
|
dataSheet.Cells(data_last_row + 1, 6) = "Buy"
|
|
dataSheet.Cells(data_last_row + 1, 8) = issue_amount
|
|
dataSheet.Cells(data_last_row + 1, 9) = issue_price
|
|
dataSheet.Cells(data_last_row + 1, 10) = issue_price * issue_amount * issue_notional / 100
|
|
dataSheet.Cells(data_last_row + 1, 11) = issue_price * issue_amount * issue_notional / 100
|
|
dataSheet.Cells(data_last_row + 1, 16) = issue_name
|
|
|
|
|
|
funding_last_row = fundingSheet.Cells(fundingSheet.Rows.Count, 5).End(xlUp).Row
|
|
funding_last_col = fundingSheet.Cells(1, fundingSheet.Columns.Count).End(xlToLeft).Column
|
|
fundingSheet.Cells(funding_last_row + 1, 2) = issue_name
|
|
fundingSheet.Cells(funding_last_row + 1, 1) = issue_currency
|
|
Range(fundingSheet.Cells(funding_last_row, 3), fundingSheet.Cells(funding_last_row, funding_last_col)).Copy
|
|
fundingSheet.Range("C" & funding_last_row + 1).PasteSpecial Paste:=xlPasteFormulas
|
|
|
|
summary_last_row = summarySheet.Cells(summarySheet.Rows.Count, 1).End(xlUp).Row - 1
|
|
summary_last_col = summarySheet.Cells(summary_last_row - 1, summarySheet.Columns.Count).End(xlToLeft).Column
|
|
summarySheet.Rows(summary_last_row & ":" & summary_last_row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
|
|
Range(summarySheet.Cells(summary_last_row - 2, 1), summarySheet.Cells(summary_last_row - 2, summary_last_col)).Copy
|
|
summarySheet.Range("A" & summary_last_row).PasteSpecial Paste:=xlPasteFormulas
|
|
summarySheet.Range("A" & summary_last_row).PasteSpecial Paste:=xlPasteFormats
|
|
summarySheet.Cells(summary_last_row, 1) = issue_name
|
|
End Sub
|
|
|