User:Erbingha/setup daily Macro

From WolfTech
Jump to navigation Jump to search

This article gives instructions on how to perform certain PeopleSoft financial queries.The setup_daily macro is for working with the following PeopleSoft financial queries:

  • cals_actuals_by_fas
  • cals_actuals_by_ouc
  • cals_actuals_by_project
  • cals_actuals_by_bctf

To use this macro, you will need to install the macro on your computer system. See PS-004: Installing the Excel setup_daily Macro for PeopleSoft Queries if you have not done this.

1. Go into the PS Financials Reporting database and open one of the queries cals_actuals_by. You have a choice of pulling the data up by FAS, OUC, Project #, or by Budget Code.

2. Once you have the query loaded, just click on the large blue "X" which represents MS Excel to send the data directly to an MS Excel spreadsheet. In some cases where your computer may not be configured correctly, you may have to start Excel before this step.

3. You'll get a prompt box with blank fields to plugin. All of the queries ask for the fiscal year (enter '2000' for the current year) and for a begin & ending dates which represent the journal date. (ie the journal date is greater than the begin date and less than the end date) Then, depending on the query you have chosen, you will be prompted for either the FAS account, OUC, Budget Code or project.

4. After the data has been loaded into the spreadsheet, go to the Excel menu bar and choose "tools, macro, macros, run" and select "PERSONAL!sheet1.setup_daily". This macro will delete the blank columns beginning with column "B", and then sort the spreadsheet by FAS, Project, and Object. It will then add subtotals to the transaction amount based on Object code.