Chapter 14: Using Excel to Analyze Finance Data

On this page:

• Download Results from WINGS Express Finance
• AutoFilters
• Macros
• Sorting Data
• Excel Subtotals
   

Download Results from WINGS Express Finance

1. Click the Download button near the bottom of the web page you wish to download in WINGS Express Finance. *(Note this downloads all info related to the Finance query, not just the rows displayed).
2. At the File Download pop-up window, click SAVE.
3. Browse to the location you would like to save the results & edit the ___.csv filename as you would like. Then click SAVE.
4. Once the download is complete, you can then use Excel to open the .csv (comma separated value) file.
5. Use Save As on the Microsoft Office Button to save the file as a ____.xlsm macro-enabled Excel workbook.
    

---

AutoFilters

1. Hilite the row that has the column headings for your data.
2. From the menu, select Data/Filter.
3. There is now a down-arrow that appears next to each column heading. If you click the down-arrow for a column you may select one of the values. E.g. if you click the down-arrow of a rule code column & select the BD01 entry from the drop-down list then only the rows with BD01 will appear.
4. Once you make a selection for a column, the down-arrow appears differently.
5. To get all of the data rows to re-appear, click the down-arrow & select ALL.
6. You may use the Custom choice in the drop-down menu to type in criteria for a column.
    

---

Macros

Excel macros can be defined to automate routine tasks. Rather than having to select the same formatting options in each downloaded file, you can record and save these formatting actions then use the Macro on any document you want to perform these same tasks.

The first time that you use Macros at your computer, you will need to turn on the Developer Tab:

1. Select the Microsoft Office Button then click the Excel Options button (towards the bottom-right of that menu)
2. You should already be on the Popular Category (Categories are on the left), make sure the option Show Developer tab in the Ribbon is selected
3. Click OK

To create a macro, open the spreadsheet that you wish to format

1. Select the Developer tab
2. Select Record Macro from the Code group
3. In the Record Macro dialog box, name your new Macro
   1. It is very useful to use a name that applies to either the formatting contained in the Macro or the document type you will be using the Macro for in the future. (Hint – If you name the macro the same as the screen from which you download e.g. Transaction Detail then it will be easy to remember which Macro to use).
4. Optional - If you would like to use a keyboard shortcut, enter a letter or number in this field
   1. You will need to select the ctrl key along with this character for the keyboard shortcut to work. Make sure you do not select a key associated with another keyboard shortcut that you use in Excel.
5. Make sure to select Personal Macro Workbook in the “Store Macro In” box – as this will assure that the macro is available for you whenever you try to run it from your desk.
6. Optional - Type a description of the Macro
7. Click Ok to begin recording your formatting actions
8. Format your spreadsheet (Example: bold the row with your column headings, change the column widths, format the dollar columns to display $, etc)
   1. At the bottom left of your Excel window, a rectangular icon should appear to let you know that Excel is recording your keystrokes
9. When you are finished recording the formatting tasks, stop recording by clicking on the icon in the lower left or click the Stop Recording button in the ribbon

Macros that are saved in your Personal Macro Workbook can be used with any Microsoft Excel document on the computer that you are using. The next time you download raw data, you can use the Macros button on the Developer tab to select a previously recorded macro and run it to repeat the formatting steps that you recorded.
 

---

Sorting Data

To sort data by a particular column:

1. Hilite the data you wish to sort
2. From the menu, select Data/Sort
3. In the pop-up window, select the column(s) you wish to sort by. Then click OK
4. You can use the Undo button if the result is not what you desired
    

---

Excel Subtotals

To retrieve a subtotal in Excel:

1. Hilite all the headers & data in your worksheet
2. From the menu, select Data/Subtotals
3. In the Subtotals pop-up box, select
   1. At each change in: the column that indicates when to subtotal (e.g. Acct)
   2. Use function: e.g. select SUM
   3. Add subtotal to: check the columns you want (all the $ columns)
   4. Click OK

New rows appear with subtotals. Clicking the 1/2/3 boxes near the upper left of the worksheet will allow you to drill up or down. Example: clicking 2 shows only subtotals.

Using Excel to Analyze Finance Data
 

---