These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, December 8, 2015

Beginning Equity Adjusting Journal Entry

This program was created to assist tax staff by expediting the preparation of business tax returns.

Tax season at an accounting firm can be extremely stressful.  Every minute counts.   No one wants to be in the office until midnight on the day of a tax deadline.   One time consuming activity is making the Beginning Equity Adjusting Journal Entry.  This project automates the production of the Beginning Equity Adjusting Journal Entry.

Each tax client has a varying number of accounts in each report.  Some simpler clients may not have a large chart of accounts.  However, the more complex clients can have a drastically longer chart of accounts. For this project I have included two additional files needed for the program—a QuickBooks report Excel file and last year’s Tax Report Excel file for a client.  These test files are an example of a simpler client as the number of accounts is not too large. 

This program has the potential to save vast amounts of the tax staff’s time.  No longer will the QuickBooks report and the prior year’s Tax Report need to be manually compared and processed.  This will increase the throughput of the firm during tax season.


Files:
***Note: The sample files have been anonymized to protect client information.

Monthly Budget Variance Report



Jeff Geddes


12/1/2015

BUSM 614
VBA Final Project Memo
 
 

EXECUTIVE SUMMARY:
Martin Anderson, PC is a probate, estate planning, and tax law firm located in Provo, UT. The firm consists of six employees: two lawyers, two paralegals, a receptionist, and an accountant. The firm has been in existence since 2001 when Martin separated from the Munster and Anderson Partnership. After an efficiency analysis performed in 2013, the firm has emphasized its probate practice.

Martin creates an annual budget in January that takes into consideration the prior year budget, prior year data, and current year data. In 2014, Martin wanted to get better control of his business operations and asked me to create a monthly P&L compared to Budgeted P&L, a report that could not be run in Quickbooks. After finding this initial report useful, Martin wanted to see more data including: Prior Year Month, Current YTD, Prior YTD, and Budgeted YTD. The report also included a wage summary for each employee under the same categories. With all the moving parts in this report, I found formula mistakes popping up almost every month. Completing the report took me at least an hour each month (usually longer), and the process was not enjoyable. I needed an automated process that was accurate, consistent, and efficient – so I started working on this VBA project.
 
 


THE PROJECT DESCRIPTION:
The report has three basic elements to its creation: (1) preparing the workbook, (2) creating the working budget, and (3) creating the monthly report. I wrote a separate macro for each of these steps.

(1) Preparing the Workbook

  • Add a sheet before prior month sheet by referencing the sheet name: Sheets.Add Before:=Worksheets("P&L " & priorMonth & " 15")
  • Use an input box to enter the first three letters of the current month: currentMonth = InputBox("Enter the first three letters of the current month"), name the activesheet
  • Use ErrorHandler: On Error GoTo Handler
  • Name the worksheet: ActiveSheet.Name = "P&L " & currentMonth & " " & CY
  • Repeat for the six spreadsheets (different naming convention for each sheet prevented use of loop)
  • Handler: use a message box if there is an error, then delete the worksheet if error.
  • After this Macro runs, I manually copy and paste the six reports from Quickbooks Online into their respective tabs.
(2) Creating the Budget Comparison

  • Reference the Current and Prior Year Dates by looking into the last and second to last worksheet names in the workbook: CY = Right(Worksheets(Worksheets.Count - 1).Name, 2), and the same for PY but without subtracting 1 from worksheets.count.
  • Create a prior month reference by looking into the prior month tab name: priorMonth = Left(ActiveSheet.Name, 3)
  • Copy the prior month working budget, and paste into a new tab created before the prior month: Sheets.Add Before:=Worksheets(priorMonth & " Comparison P&L"), and worksheets(activeindex+1).select…cells.select…election.copy…asteSpecial, etc.
  • Name the active sheet using an input box: ActiveSheet.Name = InputBox("Enter the first three letters of the current month") & " Comparison P&L"
  • Create a reference for currentMonth: currentMonth = Left(ActiveSheet.Name, 3)
  • Increase the prior month budget column reference by 1 to reflect an increase of 1 month on the current sheet: Range("S1").Value = Range("S1").Value + 1
  • Change the date in cells B5 and C5 using the DateAdd formula: Range("B5").Value = DateAdd("m", 1, Range("B5").Value)
  • Insert the current month into vlookup formulas throughout the spreadsheet by changing the formula at the top, then copying the formula down to the bottom of the P&L: Range("B7").FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],'P&L " & currentMonth & " 15'!R6C1:R100C3,2,FALSE)),0,(VLOOKUP(RC[-1],'P&L " & currentMonth & " 15'!R6C1:R100C3,2,FALSE)))"
  • Copy and paste range formatting of unchanged columns to changed columns: Range("B7").COPY Destination:=Range("B8:B10,B13:B39,B41:B45,B47:B55,B57:B60,B62:B64,B66")
  • Set x = 69, then use this reference to insert current month into vlookup formulas calculated in columns B, C, F, and G for Employee A (note that employee A refers to different spreadsheets than other employees): Range("B" & X).FormulaR1C1 = "=Numbervalue(IF(ISNA(VLOOKUP(RC[-1],'MA Payroll " & currentMonth & " 15'!R6C1:R100C26,10,FALSE))," & "0.00" & ",(VLOOKUP(RC[-1],'MA Payroll " & currentMonth & " 15'!R6C1:R100C26,10,FALSE))))"
  • Set x = x+1 and Loop until endXlDown -1 to insert current month into vlookup formulas calculated in columns B, C, F, and G for all other employees: Range("C" & X).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],'Payroll " & currentMonth & " 14'!R6C1:R100C26,10,FALSE))," & "000" & ",(VLOOKUP(RC[-2],'Payroll " & currentMonth & " 14'!R6C1:R100C26,10,FALSE)))"
  • Where necessary, check each cell in the payroll range for "#N/A" and replace with "":Selection.Replace What:="#N/A", replacement:="", Lookat:=xlPart,SearchOrder:=xlByRows, MatchCase:=False
  • Autofit column width: Columns("A:AZ").AutoFit
  • I also used the ribbon creator to create a new tab and three buttons, one for each Macro.




(3) Creating the Monthly Report

  • Create a reference to the budget worksheet: budget = ActiveWorkbook.Name
  • Open the "template" worksheet: Workbooks.Open (ThisWorkbook.Path & "\template.xlsx")
  • Input the first three letters of the current month using an input box, and name the activesheet: ActiveSheet.Name = currentMonth & " Comparison P&L"
  • Save the activeworkbook as an xlsm file with a new name in the same folder: ActiveWorkbook.SaveAs Filename:= ThisWorkbook.Path & "\2015 " & currentMonth & " Comparison P&L.xlsm", FileFormat:= xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
  • Set reference to currentMonth workbook: monthlyReport = ActiveWorkbook.Name
  • Copy and paste specified columns from the Workbook(budget) and Worksheets(currentMonth & " Comparison P&L") into the monthly report workbook sheets 1, sheets 2, and sheets 3. Change names of the tabs. Auto adjust columns. Also, on the first tab, adjust column width of the memo column:
    • Workbooks(budget).Activate
    • Worksheets(currentMonth & " Comparison P&L").Activate
    • Range("B5:D80").Select
    • Selection.COPY
    • Workbooks(monthlyReport).Activate
    • Range("B5:D80").PasteSpecial xlPasteValues
    • Workbooks(budget).Activate
    • Range("F5:H80").Select
    • Selection.COPY
    • Workbooks(monthlyReport).Activate
    • Range("G5:I80").PasteSpecial xlPasteValues
    • Columns("A:AZ").AutoFit
    • Columns("E").ColumnWidth = 45
    • Columns("J").ColumnWidth = 45. Also, adjust the width of the Memo column in tab 1 to 45





CHALLENGES AND THINGS I LEARNED
 

I learned a lot during this project from encountering challenges that we had not yet covered in class and finding solutions through recording macros and google searches. These challenges included: error handling, adding sheets in a specific location, referencing sheets by order, adding a month to a date formula, using FormulaR1C1 referencing, working with a template worksheet, saving a workbook as, the replace function, and adjusting column width.
 

Error handler – As I practiced running each of the macros, I ran into errors when I had not first deleted the prior run worksheets. The error was consistent, so I looked up how to deal with errors on Google. I created an "On Error Goto…Handler: Exit Sub" argument. I created a subsequent msgbox to display the error I kept seeing. One of the problems I ran into, however, was that the Handler ran each time whether or not the project had an error. I used a google search to realize that I needed an "exit sub" before my error handler code. This solved my problem.
 

Add a sheet in a specific place – I knew how to add a new sheet to the workbook, but how to add a sheet in a specific place of my workbook was a challenge for me. I wanted the most recent profit and loss reports to show up to the left of the prior month reports. I used the "sheets.add Before:=… argument to refer to the prior month sheets. I found this on Google.
 

Referencing a worksheet by order – I needed to refer to the prior month budget worksheet by selecting the sheet to the right of the activeworkbook. I used the Worksheets(ActiveSheet.Index + 1) to solve this problem. I also used the embedded worksheets.count function to pull references to prior and current years: Right(Worksheets(Worksheets.Count - 1).Name, 2). I learned how to do this in class, and then checked syntax on Google.
 

Referencing part of the name in a worksheet – I wanted to eliminate the use of another input box for the prior month, so I used "priorMonth = Left(ActiveSheet.Name, 3)" after I had selected the prior month worksheet. I knew how reference tab names, but had never tried using the Left() function in the same process.
 

Increasing the Month of a Date – I used the Date Add function to increase the Current Month in a date formula. I found this formula on Google, and tried it until I got it to work: DateAdd("m", 1, Range("B5").Value).
 

FormulaR1C1 – I discovered this kind of referencing when I used the Macro recorder. My goal was to have the spreadsheet display the formula, rather than just a value. One of the problems I had was learning that absolute references do not work with FormulaR1C1. Also, learning how the referencing worked was a bit of a challenge at first, but I figured it out through trial and error.
 

Creating a template worksheet – The monthly report is a 3-tab worksheet. I thought I would try creating template worksheet in the same folder as my budget working document, then open this worksheet when creating the monthly report, and save it as its own 2015 Monthly Report. This worked out very well. I used the thisworkbook.path function to open the workbook.
 

Replace function – Since some employees change through the years, the vlookup argument used in the spreadsheet sometimes returned an N/A#. This caused problems for my sum calculations. I used the macro recorder to learn about the "selection.replace What:… function. This worked wonderfully.
 

Column width adjustment – I used the columns.auto fit to adjust each spreadsheet. Then, I used the columnwidth = 45 to specially adjust my monthly report column where I make comments.
 
 




ASSISTANCE RECEIVED
I did not receive assistance from anyone, nor did I copy substantial parts of code from another person’s project or Google. However, I did research a lot on Google and find syntax. For example, I found the following online: "worksheets(worksheets.Count).Select", then change it to: right(worksheets(worksheets.Count).name,2).




FILES - note that the last file is the final report that is created with the 2nd and 3rd files through the Macro


  1. http://files.gove.net/shares/files/15f/jjgeddes/Geddes_Jeffrey_-_VBA_Final_Project_Memo.pdf
  2. http://files.gove.net/shares/files/15f/jjgeddes/MAA_P.C._Budget_-_2015_repaired_1.xlsm
  3. http://files.gove.net/shares/files/15f/jjgeddes/template.xlsx
  4. http://files.gove.net/shares/files/15f/jjgeddes/2015_Oct_Comparison_PL.xlsm

Sunday, December 6, 2015

DayTrader

This model focuses on the theory that an individual may be able to find profitable stocks to trade on a daily basis. Many experts suggest that focuses on short-term performance and expecting large, short-term gains is not only unreasonable, but stupid. This model is a test for the contrary opinion.

This model enables a user to scan almost 7000 stocks and estimate which stocks would be beneficial for a timely gain. The user enables Excel to automatically check every five minutes and decide whether to buy or sell stocks.

This model is currently independent of a user and is designed to simply explore the possibility of short-term gains. Future enhancements must be made for this model to be usable for a human to interact with actual investing using advice generated from this model.

http://files.gove.net/shares/files/15f/kpcott11/DayTrader_Instructions_and_Reflections.pdf
http://files.gove.net/shares/files/15f/kpcott11/Project.zip

Friday, December 4, 2015

Performance Attribution

Silverfund is BYU’s student-managed investment portfolio, comprised of approximately $3M equity and $5M fixed income. Each year (fall/winter semesters) a team of twelve MBA students are selected from the finance majors to manage the portfolio. Ownership of the fund begins in October and ends at the end of March, during which time they are allowed to trade on behalf of the portfolio. Fixed income trades are limited to AAA rated US bonds (corporate, treasuries, municipals) that mature before the end of April. On the equity side there are very few restrictions and purchases (and shorts) of any stock are permitted. The benchmark against which the equity portion is measured is 80% S&P 500, 20% Russell 2000, and Silverfund is ‘encouraged’ to maintain this weighting.


Performance attribution is the dis-aggregation of performance (returns relative to the benchmark; alpha) into its constituent components, primarily allocation and selection. Allocation is measuring the impact of allocating funds between different asset classes, stock types, industries, sectors etc.Selection measures the impact of choosing particular stocks within those areas of allocation. In addition to allocation and selection, the impact of currency movements and hedging activities can also be included in the analysis. Performance attribution is a huge business and asset managers who do not perform this analysis in-house will contract external providers, at significant cost, to perform this analysis for them on a regular basis.

This project aims to automate the otherwise complicated maintenance process of the performance attribution spreadsheet created by this year's Silverfund team. By eliminating the manual manipulation of data within the spreadsheet we have been able to ensure a higher level of accuracy and minimize the risk of errors, as well as speeding up the process significantly. In addition, we have added other useful features that would not be possible without the use of VBA. This spreadsheet covers the equity portion of the portfolio and the selection impact (alpha contribution).


Tuesday, April 28, 2015

Scraping Census Data

My project is a web scraping tool useful in gathering and aggregating demographic information. This useful information is left of separate pages for each city, county, and state with no simple way of collecting the information for thousands of cities. Additionally each Metropolitan Statistical Area (MSA) is an important geographic area in the real estate industry, but there is no information about each collective MSA. My project gathers all these points, and can also aggregate the information by MSA.

Saturday, April 18, 2015

Conversation Theory - Improving Knowledge Structures

The central purpose of this application is prototype an interface to build a dynamic model of knowledge of any topic.  Traditional architectures of intelligent tutoring systems employ a model of student knowledge and a model of expert knowledge.  The difference between these models is what the student has yet to learn.  Since the 1970s, these models have become more and more complex resulting in a wide range of research related to how to improve the student model and the expert model, and how to compare them for differences.  Artificial Intelligence has a strong hold in this arena because of the similarities between model-generation and the definition of artificial intelligence espoused by scientists for decades.
The purpose of this project within this context is to take a small step toward applying technology to improve teaching and learning.  Specifically, this project takes the ideas from Conversation Theory and applies them to the design and development of an interface to build an entailment mesh.  An entailment mesh is an instructional design mechanism for instructors, instructional designers, and educational technology developers.  It is a way to organize knowledge that is different from current knowledge representation systems.  This application helps the user participate in a knowledge generating activity either by oneself or with other users.  Instructors, students, instructional designers, and educational technology developers will all find value through this tool.  Instructors will find benefit in TW by building a more coherent (tight) curriculum including aligning the curriculum with course objectives and skills.  Students will find value in TW by challenging what they think they know on a topic and learning from themselves and others what they did not know they did not know.  Instructional designers can find value in TW by comparing the knowledge structures they have embedded in instructional products and services with the dynamic knowledge structures afford by TW.  Finally, educational technology developers will discover value in TW by learning how dynamic knowledge structures will lead to improving the design of adaptive learning technologies.


http://files.gove.net/shares/files/15w/jsc44/Final_Project_Write-up.pdf

http://files.gove.net/shares/files/15w/jsc44/Building_Entailment_Meshes.xlsm


Friday, April 17, 2015

Automating Circulation Data Records

EXECUTIVE SUMMARY

I have been a student worker in the Harold B. Lee Library’s Office of Digital Content Management for close to three years. Among other things, my office is in charge of generating and recording the library’s circulation reports. Circulation data is collected as follows: At the beginning of each month, a text file is generated using a program called WorkFlows, which, among other things, stores and records information about library circulation. This text file shows 1) the number of items checked out, 2) the number of individual patrons who check out items, and 3) the ratio of items checked out per patron for each hour of each day that the library’s circulation desks are in operation during the last full month. The output from these text files is then read into an Excel spreadsheet, where it can be used for analysis. Up until recently, data from these text files was entered manually into the spreadsheet. For my project, I made the decision to write a program which would automate this process, saving on average half an hour to 45 minutes of worktime.

The program that I have written includes a File Dialog, which allows the user updating the spreadsheet to open the text file containing the data for the month that they wish to import into the spreadsheet. The program then uses arrays to find and paste in the data, as well as the date heading for the data. After the program has run, the user is asked whether they would like to import more text files containing circulation into the spreadsheet, continuing in a loop if the user selects the “Yes” option and exiting the program if the user selects the “No” option.

Files:

Thursday, April 16, 2015

What am I Eating?

Eating healthy has been a struggle for many people nationwide. We always think we should be eating healthier, and we make New Year’s resolutions to do so, but usually no changes are actually made to our diets. We at Health-Tracker are committed to helping America improve their diet through our eating programs.

Health-Tracker is a non-profit organization that offers programs including meal plans, health evaluation, personal trainers, and much more in order to help people improve their health. In our work to help people improve their health we have found that the most important step to any successful diet is to track the foods you eat. The tracking process helps us see clearly what we eat, how much we eat, trends in diets, and the nutritional value of what we eat. This is essential to identify how your diet is lacking, and to keep you accountable to the goals you have set. With a clear record of what you eat, you can’t “forget” about the cake you ate, and you can clearly see just how few vegetables you are probably eating.

The Health-Tracker food tracking program will help you to quickly and easily create a log of the foods you eat and their nutritional value. To begin using our program you input your profile including your height, weight, age, and gender. The system will then pull your nutritional needs from the internet to give you an estimate of what you need in your diet including calories, amount of fat, amount of carbs, amount of protein, vitamin a, vitamin c, vitamin d, calcium, iron, and fiber. Next, foods are entered to the Food Log sheet of the program. On the food log you open an internet browser to get URLs for the foods you ate so the program can get the nutritional information from the internet and input it to the log. As you enter the foods you eat into the food log our program will track this nutrition info to give you continuous updates on how you are doing to meet your nutrition goals. You can see all-time, weekly, and monthly reports to show your results over time and to help identify trends.


By tracking the foods you eat, you will be able to get a clear picture of your current nutrition needs. By taking a little time to input the foods you eat you will get a useful report to show which nutrients you are lacking, how many calories you are eating in a day, and how you can improve. We at Health-Tracker hope this information will be useful to help bring in a healthier, happier America.

Wednesday, April 15, 2015

Elder’s Quorum Contact tools / HT Reporting Tool

In my ward’s Elder’s Quorum we use a Google Form to collect home teaching reports. However, the task of organizing the results from the form to a simple percentage is a manual task. For this reason I have built a tool whose purpose is to gather the data from the Google Form and automatically compile it into a report on home teaching (a final percentage). In addition to this several other functions were built into the program. First, the spreadsheet stores the names, emails, and phone numbers of the quorum members. With the click of a button I can send a mass-text or a mass-email to all the elders. Second, once the numbers are compiled an email can be sent to the need-to-know parties.

Database Consolidation Report Generator

American Greetings Corporation, LLC is the world’s largest greeting card company. Based in Brooklyn, Ohio, a suburb of Cleveland, the company sells paper greeting cards, electronic greeting cards, party products, and electronic expressive content. (Wiki) I will be interning with American Greetings (AG) this summer and got a jump start on some potential projects for this summer.
         
When there is an issue at one of 90,000 locations that carry AG’s products, management has to manually pull data from multiple databases to compile a snapshot of that stores current situation. My project automates the process of gathering all necessary information into an easy to understand dashboard. The data in this project has been falsified for the protection of AG. The user simply enters the chain number and store number for the store of interest then at the click of a button, my macro reaches out to the necessary databases (dummy databases on websql.byu.edu were used in place of AG’s actual data), establishes a connection, then gives the user options for which data to include in the dashboard. The user can choose to get a store summary, order tables, recent shipping info, whether or not to generate a PDF report, and to send an email with the report. Following the users selection, the macro takes over to generate the dashboard and report if chosen. The user will input a name for the report as well as email credentials and info.

This project automates the lengthy data gathering process freeing up managements time to look over the data and make meaningful decisions to correct issues faster. The simplicity of the macro enables lower level employees to easily generate a report and automatically send to their superior for valuable analysis. 

 

Blog Archive