Hi all,
I am beginner in Excel VBA, just starting to learn it and I would really need some help with the below problem. I am struggling to make it work with no success so far…
I am starting with 3 separate files (one having an amount of today, the second an amount of yesterday and the third is a condition that has to be checked) and I want to generate a new file that would show the variance as percentage between the 2 amounts, today and yesterday, based on the threshold limit from the 3rd file.
My files would look like this:
1) File as of Day-1 (around 25.000 rows)
<tbody>
</tbody>2) File as of Day (around 25.000 rows)
<tbody>
</tbody>3) In addition I have a third file with two worksheets:
- Worksheet (“Thresholds”)
<tbody>
</tbody>- Worksheet (“MMI”)
<tbody>
</tbody>I would like to generate a file that shows the below results:
In the first worksheet will have the content of “file as of Day-1” except the client code “ABC”,“CDB” and except the funds “ab12345”, “fb45546” and “hj4564”.
In the second worksheet will have the content of “file as of Day” except the client code “ABC”,“CDB” and except the funds “ab12345”, “fb45546” and “hj4564”.
Third worksheet :
<tbody>
</tbody>The column F will show the variance between the amount as percentage (at shareclass level):
- if “amount D” if equal to zero or empty and “amount D-1”>0, the variance will be -100%
- if “amount D-1” if equal to zero or empty and “amount D”>0, the variance will be +100%
The column “comments” will have three values:
- “Above threshold” if the variance is greater than the threshold defined at client level
- “Below threshold” if the variance is lower than the threshold defined at client level
- “MMI” if the fund is classified as MMI
Many thanks for your help
I am beginner in Excel VBA, just starting to learn it and I would really need some help with the below problem. I am struggling to make it work with no success so far…
I am starting with 3 separate files (one having an amount of today, the second an amount of yesterday and the third is a condition that has to be checked) and I want to generate a new file that would show the variance as percentage between the 2 amounts, today and yesterday, based on the threshold limit from the 3rd file.
My files would look like this:
1) File as of Day-1 (around 25.000 rows)
| Column A | Column B | Column C | | Column N |
| Shareclass | Fund | Client Code | ……. | Amount |
| | | | | |
<tbody>
</tbody>
| Column A | Column B | Column C | | Column N |
| Shareclass | Fund | Client Code | ……. | Amount |
| | | | | |
<tbody>
</tbody>
- Worksheet (“Thresholds”)
| Column A | Column B | Column C |
| Client Name | Client Code | Threshold |
| | | |
<tbody>
</tbody>
| Column A | Column B |
| Fund | MMI |
| | |
<tbody>
</tbody>
In the first worksheet will have the content of “file as of Day-1” except the client code “ABC”,“CDB” and except the funds “ab12345”, “fb45546” and “hj4564”.
In the second worksheet will have the content of “file as of Day” except the client code “ABC”,“CDB” and except the funds “ab12345”, “fb45546” and “hj4564”.
Third worksheet :
| Column A | Column B | Column C | Column D | Column E | Column F | Column G |
| Shareclass | Fund | Client Code | Amount D-1 | Amount D | Variance | Comments |
| | | | | | % | |
<tbody>
</tbody>
- if “amount D” if equal to zero or empty and “amount D-1”>0, the variance will be -100%
- if “amount D-1” if equal to zero or empty and “amount D”>0, the variance will be +100%
The column “comments” will have three values:
- “Above threshold” if the variance is greater than the threshold defined at client level
- “Below threshold” if the variance is lower than the threshold defined at client level
- “MMI” if the fund is classified as MMI
Many thanks for your help
