Microsoft Excel Development: John's Finance Page

Visual Basic and Microsoft Excel
M icrosoft Excel is a spreadsheet application that can be used in an infinite number of finance related calculations. Since the introduction of Microsoft Excel 97, applications can be developed internally that communicate with Excel via Visual Basic programming language. These applications can automate tedious tasks that often had to be exercised manually in older versions. I have developed several applications using UserForms within Microsoft Excel. Userforms are essentially windows where grapical buttons, text boxes, and list boxes can be placed. Each object on the UserForm can be assigned a macro to run given a certain set of variables and definitions. For example, one might want to have a message box pop up when a certain statement (x > 1) is true. Since this area of my site is so new, the breadth of the content is not as great as I would like it to be. However, I think that a description of the large application that I have recently developed (FOCARB), and macros examples from the application will give you a greater understanding of how you can also develop applications for business. BRAND NEW! Check out some of the examples that I have been promising!!
FOCARB: Foreign Content and Re-Balancing
FOCARB is the application that I recently developed for a large Canadian based mutual fund company. In Canada, the government requires that retirement investment portfolios of individuals (RRSP's), contain a maximum foreign content level of 20%. The government utilizes book value, or weighted average historical cost for the calculation of this foreign content, and ultimately tax penalties. Obviously, individuals would like to maximize this amount in order to diversify away Canadian investment risk, but the problem lies in the fact that multiple funds may have been purchased, on multiple occasions and prices, and at various times. How can you maximize these book values? That is the question that FOCARB will provide with accuracy to the billionth, and then FOCARB will determine the most optimal transfer path in dollar and share amounts. Here is what the program can actually do in a processing environment, or for the strategic investor, or broker: Determine the amount of funds required to maximize or minimize the presence of a specific fund or stock in the book value of a portfolio Provide the minimum number of trades and their exact values to the billionth percentile using either current dollar amounts or share values Process all types of requests including those where the scenario of "take only from a particular fund," and "leave fund out of switch," are being requested by the client Provide a post-switch breakdown of the book value and all of its components with extreme accuracy. This allows the user to obtain key "what if" information today that would not normally be available in the processing environment until the running of system trades (usually the next day). For example, if a client or investor wanted to know how the average cost of a fund or stock has changed given a certain switch scenario Process tedious current dollar swtiches utilizing the most optimal path feature. This will provide the processor or investor with a minimal number of swtich instructions to execute. The current feature also allows for US dollar funds or stocks to be factored into the switch. This may be used for such things as a change in asset allocation given new scenarios.