Created by Christine Duddleston, Business Manager

SGPP/Sociology Business Center, College of SBS

Using a Macro to Prepare a General Error Correction (GEC) eDoc

Note: These steps should be used for expense transactions only.

When you are ready to add subaccounts to your accounts in UAccess Financials you will most likely be dealing with multiple transactions. You can upload dozens if not hundreds of transactions in one document by following the formatting provided in the GEC template.

Because you may need to upload several batches of transactions, creating a macro in excel to automatically format the your excel spreadsheet to match the GEC template can save you loads of time. Creation of the macro is a one-time set up and you can use your macro over and over again.

First, you need to get the existing transactions into a spreadsheet. This can be accomplished by downloading from either UAccess Financials or UAccess Analytics. This handout focuses on using UAccess Analytics. It is assumed you have some basic knowledge of how to download data from UAccess Analytics.

Steps:

  1. Download detailed transaction history for desired account(s) and time period.
  2. UAccess Analytics > Dashboards > General – Financial Management > Transactions Tab
  3. Confirm Organization Code and Period Number filters are correct
  4. You may want to use specific accounts numbers instead of the Organization Code.
  5. Filter Category Code to “Ex”
  1. Open up the GEC Template available from UAccess Financials. The data you downloaded with include loads of stuff you don’t need. The template clearly shows what to keep and what to get rid of.

GEC Template from UAccess Financials

Correlating Headers from Analytics:

Template / Chart / Project / Org Ref ID / Ref Origin Code* / Ref Number* / Line Descr / Amount
Analytics / Not there / Project Code / Organization Reference ID / Origin / Document Number / Entry / Current Month Actuals
  1. Start Recording Macro
    Now that you know which data you need to keep and where it should go you are ready to start recording your Macro. This information is based on Excel 2010 for Windows.
  2. Click the View Tab in Excel
  3. Click the drop down arrow on the Macros icon
  4. Click the “Record Macro” button (Create)
  5. Enter a name and description that works for you. For example, “GEC” as the macro name and “GEC Shortcut” as the description.
  6. Save the macro in the Personal Macro Workbook
  7. Format your spreadsheet to match the GEC Import template.

Since you are recording keep in mind that every move you make, each cell that you modified will be part of your macro. Think of it as making a movie.

  • Delete unnecessary columns
  • Move remaining data into the correct columns
  • Use Find and Replace to remove dashes in any cell
  • Format Object Code column for leading zeros, four digits (this should be column D)
  • Format Origin Code column for leading zeros, two digits (this should be column H)
  • Use Find and Replace to remove any commas from the Line Description (Entry) column (this should be column J)
  • Format Amount column for text (this should be column K)
  1. Stop Recording Macro
    Once you have formatted your downloaded transactions, stop recording your Macro
  1. Click the drop down arrow on the Macros icon
  2. Select “Stop Recording”
  3. Save as a .CSV
  4. Name the Excel spreadsheet to identify it as the FROM GEC.
    (For example, Account#_Period#_GEC_FROM)

Beyond Creating the Macro

Congratulations, you just created the spreadsheet for the FROM data (used in the FROM accounting line of the GEC). As you know, you also need to create spreadsheet for theTOdata (used in the TO accounting line of the GEC). This can be accomplished by making a copy of your From spreadsheet and making the required changes. In this case, adding the subaccounts. Both spreadsheet must be saved as a .CSV. Make sure to name each spreadsheet logically so you can easily identify them when needed.For example, Account#_Period#_GEC_FROMvsAccount#_Period#_GEC_TO.

IMPORTANT! – You must remove the header row of each spreadsheet before you upload them into the GEC.

Now that you have created both a FROM and TO spreadsheet, you can upload these into a GEC document in UAccess Financials. It is assumed you have some basic knowledge of the GEC.

Steps:

  1. Initiate a new GEC document in UAccess Financials
  2. Click the “Import lines” in the From section of the accounting lines
  3. Click “Browse” to find your FROM spreadsheet and select it
  4. Click “Add” and your GEC data will upload
  5. After ensuring the data is loaded correctly, upload the data from your TO GEC file in the TO section of the accounting lines
  6. Enter the rest of the required information of your GEC (sub accounts, description, notes, etc) and you are ready to submit.

Creation of the macro is a one-time set up.

Once you’ve saved your GEC macro, you can use it over and over again – saving you time!When you are ready to move additional transactions, all you will need to do is:

  1. Download the transactions from Analytics and save as a .csvinto Excel
  2. Select View from the Excel menu
  3. Select View Macros and click on Macro you created
  4. Select Run and your data will be formatted based on the formatting you previously recorded
  5. Be sure to create a FROM and TO spreadsheet
  6. Import both files into a new GEC

1