David Tate's Final VBA project - Update FX rates

Executive Summary

The Company:

Unicity International is headquartered in Orem, UT and has operations and offices located throughout the world. It sells dietary and cosmetic products through direct marketing channels (Multi-Level Marketing).

The Problem:

Unicity International deals extensively with foreign exchange rates (fx rates) as the majority of its business is overseas. It is critical that Unicity regularly update the fx rates for each currency that it has transactions in. The updating process is done on the last business day of every month by one employee. This employee will manually look up the average monthly rate and the spot rate on the last business day of the month then input that information into a spreadsheet. The employee will then calculate the inverse rates and update the corresponding sheets as well. As part of updating the spreadsheets, the employee will have to add rows, columns, and format the new cells to match the past format. At this point the employee will then copy and paste the information found on the webpage into a word doc and create a PDF file that will be used by the auditors to verify that the FX rates Unicity used were the correct ones. This process is repeated for each relevant currency and takes between 6-8 hours to complete and is often inaccurate.

The Solution:

This workbook removes all of the manual processing described above. Specifically, it does the following based on input gathered from the user:

• It looks up the spot rates on the date provided by the user
• It looks up the average monthly spot rate for each currency
• It inputs those values into the appropriate worksheets
• It calculates the inverse spot and average rates and updates the appropriate worksheets
• It updates the worksheets to match the prior formatting
• It creates, saves, and closes one support document (PDF) that contains the necessary information for each fx rate
• It than creates and saves a non vba copy of itself (management does not want vba on their computers)

Additional Features:

• Basic error trapping for the most likely errors
• Allows for the user to add or remove currencies from the matrix
• All user interaction is conducted through the ribbon and subsequent forms
• Has a toggle button that hides/shows the supporting tabs for each currency
• Has progress bars indicating how the program is progressing while running
• Information about the program (who created it, the version, and how to contact me)
• Sends me an email that alerts me after the program has been run

Conclusion: This process once took between 6-8 hours to complete and had a high degree of inaccuracy. It now can be completed in about 5 minutes with perfect accuracy assuming the user doesn’t make a mistake I did not account for.

http://files.gove.net/shares/files/12w/dlt53/David_Tate-_VBA_Final_Project_write_up.pdf

http://files.gove.net/shares/files/12w/dlt53/Exchange_rate_summary_Post.xlsm