excel best practices for credit unions
TRANSCRIPT
![Page 1: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/1.jpg)
Excel Best Practices for Credit Unions
VBA/Macros
![Page 2: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/2.jpg)
What is VBA?
• VBA – Visual Basic for Applications– Programming language used in Excel– This language is used to write Macros
• Macros – Automated tasks or programs that run in Excel
![Page 3: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/3.jpg)
Why are VBA & Macros Important?
• They can automate repetitive tasks which saves time and increases productivity
• Other programs and software can be utilized in VBA, like Outlook or a data extraction software like Monarch Pro
![Page 4: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/4.jpg)
Example:
• Suppose that for each business day you would like to do a brief summary of your credit union’s financial position.
• In order to do this you need to bring in 3 reports and send out an e-mail to management.
• This process can be automated with the use of VBA and Macros.
![Page 5: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/5.jpg)
Example: Importing Reports
• Importing the 3 reports– The reports are in the same format every day– However, they have excess data that is not needed– Monarch Pro can be used to easily extract just the
needed data
![Page 6: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/6.jpg)
![Page 7: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/7.jpg)
![Page 8: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/8.jpg)
![Page 9: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/9.jpg)
Example: Send E-mail
• Once the Excel file has been updated, you want to send a financial statement summary e-mail to management.
![Page 10: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/10.jpg)
![Page 11: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/11.jpg)
![Page 12: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/12.jpg)
Example: Conclusion
• In less than 5 minutes: reports can be run, uploaded into Excel, and e-mails sent to management.
• Management can use this to monitor liquidity, increases/decreases in shares or loans, cash over/shorts, vault limit compliance, etc.
![Page 13: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/13.jpg)
How Else Can We Use VBA?
• Upon Open, a shared Excel file will open the named tab that corresponds to the user’s Active Directory Display Name.
![Page 14: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/14.jpg)
![Page 15: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/15.jpg)
![Page 16: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/16.jpg)
Other Ways to Use VBA
• Bank Reconciliations– Import transaction history from Corporate CU and
GL using a macro and Monarch
• Upload Journal Entries to Core Processor– Most cores have an import feature that allows you
to import a GL journal entry
![Page 17: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/17.jpg)
Tips & Tricks
• ALT-F11 will access VBA editoro Create code in
Modules
![Page 18: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/18.jpg)
Tips & Tricks
• Record Macro then Edito Create code in Modules
![Page 19: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/19.jpg)
Tips & Tricks
• ALT-F8 will bring up all available Macroso You can also
assign a Macro to a button
![Page 20: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/20.jpg)
Tip & Tricks• Start simple: use the Macro Recorder, then edit it.• Use named ranges in your Macro, so that the Macro
remains dynamic in case cell locations change.• Set up master templates.• Copy ideas from experts online and rewrite to fit your
needs.• For increased speed, turn off screen updating
(Application.ScreenUpdating = False) while the Macros run, and turn it back on at the end of the Macro (Application.ScreenUpdating = True).
![Page 21: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/21.jpg)
Resources
• Excel VBA Reference – https://msdn.microsoft.com/en-us/library/office/ee861528.aspx
• Ron de Bruin– http://www.rondebruin.nl/– Using VBA in Excel for Email automation with Outlook
• Chip Pearson – http://www.cpearson.com/Excel/topic.aspx– VBA and power formula use
![Page 22: Excel Best Practices for Credit Unions](https://reader031.vdocuments.us/reader031/viewer/2022030321/586f7b181a28abb6398ba1f4/html5/thumbnails/22.jpg)
Resources• Debra Dalgeish
– http://www.contextures.com/– Excellent Pivot Table and Formula reference
• Jon Peltier– http://peltiertech.com/Excel/Charts/ChartIndex.html– Charts, add ins, Tutorials
• Monarch – http://www.datawatch.com/products/datawatch-monarch/
• Free VBA Code, Formulas, Tips, & Tricks– http://www.ozgrid.com/VBA/