Computer Programs

In this assignment you will be creating an Excel macro. Macros are a set of storedprocedures that a computer program, such as Excel, can execute when called upon. Macros, sometimesreferred to as Bots, are very useful when you have to perform the same set of tasks on a regular basis. For example, when you have to format data is the same way on a regular or repeated basis. In some regardsMacros can be written using a programing language called Visual Basic for Applications (VBA) or they can be created by recording your keystrokes or functions. In this regard macros are similar to the scripts that can be written within IDEA or other computer programs. Macros can be stored within a specific workbook, within your Personal Macro Workbook or within a new workbook.In this assignment assume that you are working at MassYork Banking Corp. The software development team is still developing the full version of the CTR automated preparation system you requested. However, it will be some time before they are able to finish the project. In the meantime they have been able to produce a summary report that shows every transaction or combination of transactions that require the filing of a CTR. However, it does not identify which group at the bank should complete the CTR, nor does it provide all of the data necessary for completing Parts I, II and III the CTR. However, it is better than nothing, as it performs the tedious task of identifying when a CTR needs to be filed.The report comes to you on a daily basis. Some days the report is rather short, only a few lines, other days it is long, up to a hundred lines. Thus far you have been manually formatting the report each day, however, that is getting old. As a result, you decide to create a macro that will format the data for you.The source data file for this assignment is the “CTR Data Extract” file. You are to create an Excel macro that will perform the 15 tasks listed below. Please name the macro “CTR_Extract_Review” and save it within the CTR Data Extract Excel file. When recording the Macro, please provide a brief description of it. You do not need to set-up a short-cut key for the macro. You may write the macro using VBA if you like, or you may record the macro using the Record Macro function within the Macros dropdown of the View tab.The steps your macro should perform are:1)Start your marco with Cell A1 of the “Starting Point data” worksheet, as the active cell.2)Rename Sheet 3 as “CTR Data Extract Review”3)Copy data from Starting Point Data into the “CTR Data Extract Review” tab.4)Use Text to Columns on the data so that each field is in its own column.5)Insert two new columns between the fields, SSN_State and Trans_Total. Name one of the new columnsSSN and the other State.6)Use the LEFT function to populate the SSN column with the Social Security Number for each line of data, and the RIGHT function to populate the Statecolumn with the two-letter abbreviation of the state for each line of data.7)Format the Trans_Total field as currency, with 0 decimal places.8)Format the Trans_Date field as a date in the format of MM/DD/YY, so that each line has the same number of characters.9)Insert two new columns between the Trans_Date and Multi_Trans columns, Name the first new column CTR_Due_Date and the second Days_til_CTR_Due. Populate the CTR_Due_Date as the Trans_Date plus 25 days. Populate the Days_til_CTR_Due as the difference between the CTR_Due_Date and today’s date.10)Create a new field between the Multi_Trans and Own_Behalf fields. Name the new field Single_Trans.11)Using the IF function, populate the Single_Trans field to contain a 1 if the Multi_Trans field contains a Nand a 0 if it does not contain the letter N.12)Create a new column, and call it NCCG_Responsible.13)Sum Single Trans” and Own_Behalf. If the value is 2, make column NCCG_Responsiblebe 1, if not make it 0.14)Sort the data descending based on the NCCG_Responsible and Days_til_CTR_Duefields, so the ones that are your group is responsible are shown first, and are listed with the oldest transaction date appearing at the top.15)Set a filter so that only the transactions for which, your group is responsible for completing a CTR are showing. However, don’t delete the other items, simply set it so they are not visible. .Once you stop recording the macro, save the file as a Macro Enabled Project, using the following naming convention: Excel_Macro_Lastname_Firstname.A few tips and hints:1) It is hard to record a macro correctly the very first time. It may take multiple attempts, that is fine keep trying until you get it right. If you do have multiple macros, please delete all except for the one that you are submitting to be graded.2) I highly recommend that after recording your macro you run it to make sure it does what you want it to do. In order to do this, you will need to insert a new tab into the Excel Workbook and call it Sheet3, as that is the sheet the macro will attempt to use. You will also need to either delete or rename any tab named “CTR Data Extract Review” that you might have already created, otherwise Excel will freak out. It doesn’t allow for two tabs to have the same name.3) The macro will do what you tell it to, in the order you tell it. That being said your macro doesn’t have to be perfect, and you can actually correct minor errors later within the same macro. For example, if you want the macro to put the term “Customer Name” into cell B1, but you accidentally type “Customer Number” in the cell, and then realize it prior to stopping your macro recording, you can go to B1 and change it to “Customer Name” and the output of the macro will be correct. It will take an extra step, and if this is something that you’d be giving to a client you’d want to go into the source code and clean up, but for the purposes of this course that sort of quick fix will be fine.4) Make sure to save any Workbook that includes a macro as a Macro Enabled Excel file.5) It won’t be an issue for this course, but be aware of the fact that your macro will only do what you tell it on the range you tell it. So, for example if you create a macro to format data using a tab that contains data in the first 20 rows, and then run that macro on a tab with data in the first 50 rows, the macro will only format the data in first 20 rows, because that is what you told it to do. So, build your macros based on the size of the data you think you might use, not necessarily the size of the data you are currently using.6) Macro names cannot contain spaces.

Don't use plagiarized sources. Get Your Custom Essay on
Computer Programs
From $8/Page
Order Essay
Full satisfaction or return your money back

Our experts will write you a top-quality paper and revise it an unlimited number of times until you're 100% satisfied - or offer a refund.

Check Prices
Get the best discount and better grades here
Pages (550 words)
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know that being a student these days is hard. Because of this, our prices are some of the lowest on the market.

Instead, we offer perks, discounts, and free services to enhance your experience.
Sign up, place your order, and leave the rest to our professional paper writers in less than 2 minutes.
step 1
Upload assignment instructions
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
How to get the most out of your experience with Toptutor4me
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
Here is what our happy clients say about us.
Receiving customer feedback and reviews from legit students who have utilized our services is one of our favorite part of the job. We love seeing testimonials from happy students whom we have helped to succeed. Read through the below review page to see what our partners think.
Healthcare Writing & Communications
Perfect! Thank you so much for all of your help!
Customer 452483, October 31st, 2021
Classic English Literature
Great paper, thank you!
Customer 452591, May 5th, 2022
English 101
Thank you guys for always being there and helping me always get a 100% on my assignments!
Customer 452483, August 16th, 2021
Healthcare Writing & Communications
Thank you so much for all of your hard work! Appreciate it all!
Customer 452483, November 14th, 2021
Thank you so much!
Customer 452461, June 23rd, 2021
Introduction to Sociology
Thank you very much for all of your hard work!
Customer 452483, October 15th, 2021
Thank you so much!
Customer 452483, July 26th, 2021
Thank you so much! Appreciate it!
Customer 452483, November 8th, 2021
Excellent!!! Thank you.
Customer 452487, September 13th, 2021
Introduction to Sociology
Amazing! Thank you so much for your hard work, appreciate it!!
Customer 452483, October 24th, 2021
English 101
Perfect! Very informative! Appreciate all of your hard work! Thank you very much.
Customer 452483, September 22nd, 2021
Healthcare Writing & Communications
Perfect! Thank you for also remembering to highlight the necessary words. Appreciate all of your help and hard work!
Customer 452483, November 7th, 2021
Total Reviews
Satsfaction rate
6 pages
Average paper length
Customers referred by a friend
Enjoy the best prices and lifetime 15% discount
Use a coupon TOP15 and enjoy expert help with any task at the most affordable price.
Order Now Order in Chat