I've got maybe average excel skills but no VB experience (which I probably need to pick up) and I have a bit of a challenge in trying to produce a report that is easier to read than what I have now. I inherited the employees and processes around this workflow and so while I have limited control over the main spreadsheet where the data is I need to figure out a way to generate a report that's easier to understand.
The main spreadsheet that gets updated daily through quite a manual process. The main spreadsheet is overwritten each day with new data but when the update process is done in the morning a copy of the spreadsheet is saved offline and then forwarded to the recipients in tabular format. The folks who read the data would be better served with a stacked column chart showing contribution for a number of different values. Tabular data even causes some confusion for me and you know how they say "a picture speaks louder than words". Below is a pic of the portion of the spreadsheet I am most concerned with (probably should have upload the sheet, but some of it is quite proprietary);
[/IMG]
Each day, the numbers on this sheet are summarized from a much more extensive "data" sheet elsewhere in the workbook. This data is updated and manipulated for a number of different products (muni, corp, agency, etc...) and the pic above shows the summary of that data manipulation.
What I would like to learn how to do is to create some type of a macro or function that I can press (or my employee can press) which will take the numbers listed above and copy them into another spreadsheet and append them, by date to the to the values which would already be there. From that daily process I can, over time, create stacked contribution chart which show each of these values graphically...and voila! Easy to read report.
As these valued change daily I naturally need to make sure I keep track of the dates and the values on each of these dates.
The graph(s) need to be drive by date, group name (muni, corp, etc), market val long, market val short, market val net, unrealized p/l long, unrealized. p/l short, unrealized p/l net
Am I making any sense? Any additional guidance, thoughts or ideas would be most helpful.
The main spreadsheet that gets updated daily through quite a manual process. The main spreadsheet is overwritten each day with new data but when the update process is done in the morning a copy of the spreadsheet is saved offline and then forwarded to the recipients in tabular format. The folks who read the data would be better served with a stacked column chart showing contribution for a number of different values. Tabular data even causes some confusion for me and you know how they say "a picture speaks louder than words". Below is a pic of the portion of the spreadsheet I am most concerned with (probably should have upload the sheet, but some of it is quite proprietary);
Each day, the numbers on this sheet are summarized from a much more extensive "data" sheet elsewhere in the workbook. This data is updated and manipulated for a number of different products (muni, corp, agency, etc...) and the pic above shows the summary of that data manipulation.
What I would like to learn how to do is to create some type of a macro or function that I can press (or my employee can press) which will take the numbers listed above and copy them into another spreadsheet and append them, by date to the to the values which would already be there. From that daily process I can, over time, create stacked contribution chart which show each of these values graphically...and voila! Easy to read report.
As these valued change daily I naturally need to make sure I keep track of the dates and the values on each of these dates.
The graph(s) need to be drive by date, group name (muni, corp, etc), market val long, market val short, market val net, unrealized p/l long, unrealized. p/l short, unrealized p/l net
Am I making any sense? Any additional guidance, thoughts or ideas would be most helpful.
