Complex VBA code

[booton]

Hello everyone!

I have a really complex (in my opinion) vba request if someone can please help me.

I have an Excel file with 4 sheets.

Input raw,
Input,
Discount Factors,
Upload.

From Input raw I need to take the data and put it into Input by year. To be more clear, for example I have data from 2020, 2019, 2018. first it needs to take the 2020 data and put it into input, after that delete it, then the 2019 and delete it and last 2018 and delete it.

Based on the rows that are copied from Input raw into Input there are some formulas in Discount Factors sheet. that is why i need the data copied year by year. When the data from one year only is in Input, in Discount Factors some cells needs to be copied into Upload sheet.

To summaries everything:
one year data only copied from Input raw into Input.
from Discount Factors some cells needs to be copied into Upload sheet
delete what is in Input sheet
copy next year data
and so on... until there are no more years...

Is it someone able to help me please? Is it possible to do this in VBA? I can share the excel :D

Thank you so much!!

 

[offthelip]

Your problem is fairly easy to solve using Vba, certainly not what I would consider complex, however to do it we would need to know the layout of your 4 sheets. please can you use the XL2BB utility to load your worksheet onto the BB. You can load stripped down copies to avoid sensitive data if necessary.

 

[booton]

Your problem is fairly easy to solve using Vba, certainly not what I would consider complex, however to do it we would need to know the layout of your 4 sheets. please can you use the XL2BB utility to load your worksheet onto the BB. You can load stripped down copies to avoid sensitive data if necessary.

Input raw table:

Discount factors Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Input Risk-free									Input Risk-free+Adjustment
2	Valuation date	Cohort	Transaction currency	Type	Reference date	Term	Value	Shift yield curve		Valuation date	Cohort	Transaction currency	Type	Reference date	Term	Value	Adjustment	Value with adjustment	Shift yield curve
3	12/31/2021	Year	EUR	Risk-free	12/31/2020	365	-0.0059	0 bps		12/31/2021	Year	EUR	Risk-free + Adjustment	12/31/2020	365	-0.0059	0.0045	-0.005893581	0 bps
4	12/31/2021	Year	EUR	Risk-free	12/31/2020	730	-0.004			12/31/2021	Year	EUR	Risk-free + Adjustment	12/31/2020	730	-0.004	0.0045	-0.003979424
5	12/31/2021	Year	EUR	Risk-free	12/31/2020	1095	-0.0025			12/31/2021	Year	EUR	Risk-free + Adjustment	12/31/2020	1095	-0.0025	0.0045	-0.002469316
6	12/31/2021	Year	EUR	Risk-free	12/31/2020	1460	-0.0015			12/31/2021	Year	EUR	Risk-free + Adjustment	12/31/2020	1460	-0.0015	0.0045	-0.001462121
7	12/31/2021	Year	EUR	Risk-free	12/31/2020	1825	-0.0008			12/31/2021	Year	EUR	Risk-free + Adjustment	12/31/2020	1825	-0.0008	0.0045	-0.000847274
8	12/31/2021	Year	EUR	Risk-free	12/31/2019	365	-0.0059			12/31/2021	Year	EUR	Risk-free	12/31/2019	365	-0.0059	0.0045	-0.005893581
9	12/31/2021	Year	EUR	Risk-free	12/31/2019	730	-0.004			12/31/2021	Year	EUR	Risk-free	12/31/2019	730	-0.004	0.0045	-0.003979424
10	12/31/2021	Year	EUR	Risk-free	12/31/2019	1095	-0.0025			12/31/2021	Year	EUR	Risk-free	12/31/2019	1095	-0.0025	0.0045	-0.002469316
11	12/31/2021	Year	EUR	Risk-free	12/31/2019	1460	-0.0015			12/31/2021	Year	EUR	Risk-free	12/31/2019	1460	-0.0015	0.0045	-0.001462121
12	12/31/2021	Year	EUR	Risk-free	12/31/2019	1825	-0.0008			12/31/2021	Year	EUR	Risk-free	12/31/2019	1825	-0.0008	0.0045	-0.000847274
13	12/31/2021	Year	EUR	Risk-free	12/31/2018	365	-0.0059			12/31/2021	Year	EUR	Risk-free	12/31/2018	365	-0.0059	0.0045	-0.005893581
14	12/31/2021	Year	EUR	Risk-free	12/31/2018	730	-0.004			12/31/2021	Year	EUR	Risk-free	12/31/2018	730	-0.004	0.0045	-0.003979424
15	12/31/2021	Year	EUR	Risk-free	12/31/2018	1095	-0.0025			12/31/2021	Year	EUR	Risk-free	12/31/2018	1095	-0.0025	0.0045	-0.002469316
16	12/31/2021	Year	EUR	Risk-free	12/31/2018	1460	-0.0015			12/31/2021	Year	EUR	Risk-free	12/31/2018	1460	-0.0015	0.0045	-0.001462121
17	12/31/2021	Year	EUR	Risk-free	12/31/2018	1825	-0.0008			12/31/2021	Year	EUR	Risk-free	12/31/2018	1825	-0.0008	0.0045	-0.000847274
Input raw

Cell Formulas
Range	Formula
Q3:Q17	Q3	=0.45/100
R3:R17	R3	=P3*(1+Q3)

Cells with Data Validation
Cell	Allow	Criteria
H3	List	0 bps,100 bps
S3	List	0 bps,100 bps

 

[booton]

Input table:

Discount factors Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Days in period	365
2		Discount Factor Periodicity	Year					Check												Check
3	Input Risk-free							5	5 rows		Input Risk-free + Adjustment									5	5 rows
4	Valuation date	Cohort	Transaction currency	Type	Reference date	Term	Value	Shift yield curve	Year		Valuation date	Cohort	Transaction currency	Type	Reference date	Term	Value	Adjustment	Value with adjustment	Shift yield curve	Year
5	12/31/2021	Year	EUR	Risk-free	12/31/2020	365	-0.0059	0 bps	1		12/31/2021	Year	EUR	Risk-free	12/31/2020	365	-0.0059	0.0045	-0.005893581	0 bps	1
6	12/31/2021	Year	EUR	Risk-free	12/31/2020	730	-0.004		2		12/31/2021	Year	EUR	Risk-free	12/31/2020	730	-0.004	0.0045	-0.003979424		2
7	12/31/2021	Year	EUR	Risk-free	12/31/2020	1095	-0.0025		3		12/31/2021	Year	EUR	Risk-free	12/31/2020	1095	-0.0025	0.0045	-0.002469316		3
8	12/31/2021	Year	EUR	Risk-free	12/31/2020	1460	-0.0015		4		12/31/2021	Year	EUR	Risk-free	12/31/2020	1460	-0.0015	0.0045	-0.001462121		4
9	12/31/2021	Year	EUR	Risk-free	12/31/2020	1825	-0.0008		5		12/31/2021	Year	EUR	Risk-free	12/31/2020	1825	-0.0008	0.0045	-0.000847274		5
10
Input

Cell Formulas
Range	Formula
H3,T3	H3	=COUNT(I5:I1444)
I3,U3	I3	=IFS($C$2="Year",COUNT(F5:F124)&" "&"rows",$C$2="Half-Year",COUNT(F5:F124)*2&" "&"rows",$C$2="Quarter",COUNT(F5:F124)*4&" "&"rows",$C$2="Month",COUNT(F5:F124)*12&" "&"rows")
I4,U4	I4	=IFS($C$2="Year","Year",$C$2="Half-Year","Half-Year",$C$2="Quarter","Quarter",$C$2="Month","Month")
R5:R9	R5	=0.45/100
S5:S9	S5	=Q5*(1+R5)

Cells with Data Validation
Cell	Allow	Criteria
H5	List	0 bps,100 bps
T5	List	0 bps,100 bps
C2	List	Year,Half-Year,Quarter,Month

 

[booton]

Hello and thank you so much for answering :D really appreciated!!!

first please just make that import from Input raw to Input by year.

Thank you so much!!!

 

[offthelip]

Do you want valuation date or reference date to determine the year?
what column/rows need to be copied from Discount factors to upload sheet??

 

[booton]

Do you want valuation date or reference date to determine the year?

Column E from Input raw Table

 

[offthelip]

what column/rows need to be copied from Discount factors to upload sheet??

 

[booton]

from discount factors column E2 F2 G2 H2 to column aa2 ab2 ac2 ad2 from upload sheet

 

[offthelip]

And where do you want the next years