FinancialAutomaton
New Member
- Joined
- Aug 21, 2015
- Messages
- 1
Hello everyone,
FinancialAutomaton here, I've got a multi-step macro I want to put together. As you can see by the post title, I want to do quite a few things, and I'll be going into detail step by step. If you have a good solution to only one part, let me know and I'll cross it off the list! I've got a pretty decent idea of how things "should" be done, but I don't know the coding steps to get it done. I realize that this is pretty long and in depth, but I figure it would be better to show the whole system I want, instead of posting individual sections I want.
As for what it's doing, I've got a profit and loss statement that I run from a server side system, and open into excel. I need to do the aforementioned QC checks, and then save two different versions. One version is the full file, we keep that internally. The second file is the one we send to the client, its the exact same file that's had some pages taken off of it, why, I don't know, perhaps to reduce file size or something, but it's what we do.
To get started, the first task is to perform some basic semi automated quality control checks, where failures get escalated for me to approve. Here's how I see this section working:
Go to sheet "Snapshot"
compare cell B15 to B16, if cell B15 is within +-20% of the value of cell B16, show popup "Daily return within benchmark estimation range", if else show decision popup "Please confirm daily return, exceeding 20% range of benchmark" with decision options of "Confirmed" and "Denied". If confirmed, continue to next step, if else end script and show message "Exceptional daily return, please confirm."
In active workbook, go to sheet "QC"
Create popup window showing values listed in cells B13, C13, and D13, asking to confirm if these are acceptable variances for month to date, quarter to date, and year to date respectively. If yes, continue to next step, if not, end script, show message "Please confirm QC variances".
Go to sheet "DTD Pnl"
create a filter on the sheet with Row 4 being the headers. Filter heading "Coupon/Dividend" to show only top and bottom 3% of values. Create decision popup "Extreme values for Coupon/Dividend from known sources?" with decision options of "Confirmed" and "Denied". If confirmed, continue to next step, if denied end script and show message "Please investigate large DTD coupon values"
Provided the quality control checks detailed above, the next thing that needs to be done, is to save the file. What's tricky about this one, is I have to do this whole process 5 times, with different files being saved to different locations. The file name is also something I can't quite figure out. It's easy enough to save the file as the value of cell A1, what I need to do is save it as the value of cell A1, the as of date, and the word Full (to indicate it's the full version of the file instead of the chopped down one). The as of date is in cell A2, but for example its in this format "As of Date: 08/20/2015". As I'm sure we know, slashes don't get along well with file names. I have no real idea, but think the coding would go something like this:
go to sheet "snapshot"
Designate filename as string
Designate filedate as string
filename = snapshot cell A1
filedate = snapshot cell A2
replace "/" in string filedate with "-"
If filename = "PnL - CHIAX" save as "filename + filedate + "full"" here: "P:\Projects IT\CSAM\PNL\Folder A" else go to next if
Once it's been saved for internal use, now I need to prepare it for external use. For that, I need to cut some of the extraneous pages out, which I already figured out the code to do it myself, so yay! If you know of a better way to implement it though, let me know.
The penultimate step would be to save it for client use, in the same location as it was saved before. I guess we could go through the logical steps that we did before, with the if statements and save as "filename + filedate", but if there's a simpler way to save it into the same folder as before to keep the code efficient, that would be good too.
The final step, is really really easy, I got this guys.
That's the whole thing I want it to do, basically when it's working, I'll download the file, start the macro, decide if things are ok, and it'll save both versions and close. I appreciate any help you're all willing to provide me, thanks for reading!
-FinancialAutomaton
FinancialAutomaton here, I've got a multi-step macro I want to put together. As you can see by the post title, I want to do quite a few things, and I'll be going into detail step by step. If you have a good solution to only one part, let me know and I'll cross it off the list! I've got a pretty decent idea of how things "should" be done, but I don't know the coding steps to get it done. I realize that this is pretty long and in depth, but I figure it would be better to show the whole system I want, instead of posting individual sections I want.
As for what it's doing, I've got a profit and loss statement that I run from a server side system, and open into excel. I need to do the aforementioned QC checks, and then save two different versions. One version is the full file, we keep that internally. The second file is the one we send to the client, its the exact same file that's had some pages taken off of it, why, I don't know, perhaps to reduce file size or something, but it's what we do.
To get started, the first task is to perform some basic semi automated quality control checks, where failures get escalated for me to approve. Here's how I see this section working:
Go to sheet "Snapshot"
compare cell B15 to B16, if cell B15 is within +-20% of the value of cell B16, show popup "Daily return within benchmark estimation range", if else show decision popup "Please confirm daily return, exceeding 20% range of benchmark" with decision options of "Confirmed" and "Denied". If confirmed, continue to next step, if else end script and show message "Exceptional daily return, please confirm."
In active workbook, go to sheet "QC"
Create popup window showing values listed in cells B13, C13, and D13, asking to confirm if these are acceptable variances for month to date, quarter to date, and year to date respectively. If yes, continue to next step, if not, end script, show message "Please confirm QC variances".
Go to sheet "DTD Pnl"
create a filter on the sheet with Row 4 being the headers. Filter heading "Coupon/Dividend" to show only top and bottom 3% of values. Create decision popup "Extreme values for Coupon/Dividend from known sources?" with decision options of "Confirmed" and "Denied". If confirmed, continue to next step, if denied end script and show message "Please investigate large DTD coupon values"
Provided the quality control checks detailed above, the next thing that needs to be done, is to save the file. What's tricky about this one, is I have to do this whole process 5 times, with different files being saved to different locations. The file name is also something I can't quite figure out. It's easy enough to save the file as the value of cell A1, what I need to do is save it as the value of cell A1, the as of date, and the word Full (to indicate it's the full version of the file instead of the chopped down one). The as of date is in cell A2, but for example its in this format "As of Date: 08/20/2015". As I'm sure we know, slashes don't get along well with file names. I have no real idea, but think the coding would go something like this:
go to sheet "snapshot"
Designate filename as string
Designate filedate as string
filename = snapshot cell A1
filedate = snapshot cell A2
replace "/" in string filedate with "-"
If filename = "PnL - CHIAX" save as "filename + filedate + "full"" here: "P:\Projects IT\CSAM\PNL\Folder A" else go to next if
If filename = "PnL - SIF" save as "filename + filedate + "full"" here: "P:\Projects IT\CSAM\PNL\Folder B" else go to next if
If filename = "PnL - CIT" save as "filename + filedate + "full"" here: "P:\Projects IT\CSAM\PNL\Folder C" else go to next if
If filename = "PnL - Exxon" save as "filename + filedate + "full"" here: "P:\Projects IT\CSAM\PNL\Folder D" else go to next if
If filename = "PnL - SoNM" save as "filename + filedate + "full"" here: "P:\Projects IT\CSAM\PNL\Folder E" else create failure notice
Once it's been saved for internal use, now I need to prepare it for external use. For that, I need to cut some of the extraneous pages out, which I already figured out the code to do it myself, so yay! If you know of a better way to implement it though, let me know.
Code:
Application.DisplayAlerts = False
For Each ws In Worksheets
Select Case ws.name
Case "Snapshot", "DTD Pnl", "MTD Pnl", "YTD Pnl", "Unfunded", "Risk", "Position All", "Investment", "CCY", "Daily Trades", "Monthly Trades"
Case Else
ws.Delete
End Select
Next ws
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst[INDENT] Application.DisplayAlerts = True[/INDENT]The penultimate step would be to save it for client use, in the same location as it was saved before. I guess we could go through the logical steps that we did before, with the if statements and save as "filename + filedate", but if there's a simpler way to save it into the same folder as before to keep the code efficient, that would be good too.
The final step, is really really easy, I got this guys.
Code:
ActiveWindow.Close[INDENT]End Sub[/INDENT]That's the whole thing I want it to do, basically when it's working, I'll download the file, start the macro, decide if things are ok, and it'll save both versions and close. I appreciate any help you're all willing to provide me, thanks for reading!
-FinancialAutomaton
