Peskarik
10-22-2008, 02:25 PM
Hello, everyone!
I really need some help. :(
I have made an automated reporting tool (was asked to do that for some people at work). It is based on Excel. I have a file that reads in data from another Excel file (data file), then does a lot of analysis, makes plots, tables, etc.
I wrote the code to be very dynamic and flexible, no hard-coding. Still, I have a problem that I do not know how to deal with.
Let me explain:
in the main excel file I read in financial data for some funds and indices. Some of the funds belong to our firm, some are from other firms (our peers), and I have a couple of indices.
Also, one of the funds/indices is set to be the benchmark.
As of now, I hard-code the name of the benchmark fund. But the end user should not go into code and change the names at all! I want the end user to click a button, a couple of message boxes pop up asking for location of data file, for location of the output files, and what the benchmark fund should be (based on the names of funds the data for which I read in already), then code runs, producing excel file and powerpoint file with figures and tables.
What I need to do:
in the enclosed excel file, the first page contains names of funds/indices, as I read them in from data file. On the second sheet, I ordered them in such a way that first four names are funds from my firm, then come 3 indices, then come funds of peer companies.
How can I turn sheet 1 into sheet 2?
Optimal would be that once sheet one is created a box comes up, containing all the names in sheet 1, and next to each name there are four check boxes where one can mark if the name belongs to own company fund, to index, or to peer company fund, and also if particular fund/index is the benchmark. (Basically 5 columns in that box - column of names and 4 columns (named "own", "index", "peer", "benchmark") of empty check boxes). In the first 3 columns one should be able to check several boxes, while in the 4th ("benchmark") - only one.
Then, based on how boxes were checked a list like on sheet 2 should be produced and the name of benchmark fund should be save as a global constant so that it can be used across many code modules in the report-generating excel file.
I wonder if this is possible to make with Excel VBA? That would be optimal, because if the names would change the user can always select which ones are indices, which ones are peers etc.
If the above is not possible, then another solution would be to use .find() method with some key words. FOr example, all names containing "alpha" or "beta" should be specified as own firm's funds, all names with "index" or "investable" should be indices, the rest would be peer funds. This solution is not optimal, because name "Ex Managed USD" on the sheet belongs to own firm's funds, but does not contain key words "alpha" or "beta".
If the solution with the message box is not possible, how would I write the code that takes sheet 1 in enclosed file and produces sheet 2? In this code there will be .find() and .findnext() methods, obviously. And I also want the empty rows between the groups, just like in sheet 2.
SOrry for a lengthy description, but it is hard to explain the problem in a few words.
Please, could someone help me. I don't know how to do this :dunno :( , and that is the last thing I need to do with the file, except for copying tables/plots from Excel to PowerPoint (I think I can figure that out myself).
Thank you a lot in advance!!!
:help
I really need some help. :(
I have made an automated reporting tool (was asked to do that for some people at work). It is based on Excel. I have a file that reads in data from another Excel file (data file), then does a lot of analysis, makes plots, tables, etc.
I wrote the code to be very dynamic and flexible, no hard-coding. Still, I have a problem that I do not know how to deal with.
Let me explain:
in the main excel file I read in financial data for some funds and indices. Some of the funds belong to our firm, some are from other firms (our peers), and I have a couple of indices.
Also, one of the funds/indices is set to be the benchmark.
As of now, I hard-code the name of the benchmark fund. But the end user should not go into code and change the names at all! I want the end user to click a button, a couple of message boxes pop up asking for location of data file, for location of the output files, and what the benchmark fund should be (based on the names of funds the data for which I read in already), then code runs, producing excel file and powerpoint file with figures and tables.
What I need to do:
in the enclosed excel file, the first page contains names of funds/indices, as I read them in from data file. On the second sheet, I ordered them in such a way that first four names are funds from my firm, then come 3 indices, then come funds of peer companies.
How can I turn sheet 1 into sheet 2?
Optimal would be that once sheet one is created a box comes up, containing all the names in sheet 1, and next to each name there are four check boxes where one can mark if the name belongs to own company fund, to index, or to peer company fund, and also if particular fund/index is the benchmark. (Basically 5 columns in that box - column of names and 4 columns (named "own", "index", "peer", "benchmark") of empty check boxes). In the first 3 columns one should be able to check several boxes, while in the 4th ("benchmark") - only one.
Then, based on how boxes were checked a list like on sheet 2 should be produced and the name of benchmark fund should be save as a global constant so that it can be used across many code modules in the report-generating excel file.
I wonder if this is possible to make with Excel VBA? That would be optimal, because if the names would change the user can always select which ones are indices, which ones are peers etc.
If the above is not possible, then another solution would be to use .find() method with some key words. FOr example, all names containing "alpha" or "beta" should be specified as own firm's funds, all names with "index" or "investable" should be indices, the rest would be peer funds. This solution is not optimal, because name "Ex Managed USD" on the sheet belongs to own firm's funds, but does not contain key words "alpha" or "beta".
If the solution with the message box is not possible, how would I write the code that takes sheet 1 in enclosed file and produces sheet 2? In this code there will be .find() and .findnext() methods, obviously. And I also want the empty rows between the groups, just like in sheet 2.
SOrry for a lengthy description, but it is hard to explain the problem in a few words.
Please, could someone help me. I don't know how to do this :dunno :( , and that is the last thing I need to do with the file, except for copying tables/plots from Excel to PowerPoint (I think I can figure that out myself).
Thank you a lot in advance!!!
:help