Child pages
  • Create Financial Detail Spreadsheet with Running Fund Balance

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

This process uses the Report Direct Link to populate USAS-R data into a spreadsheet. The steps below outline the procedure using a spreadsheet template for the SSDT Financial Detail Report July 1 Cash Balances. The Report Direct Link can also be utilized on any custom or template USAS-R report to pull data into Excel. 

  1. Click to download the spreadsheet template for Financial Detail Template with Running Fund Balance. This template will support approx. 50,000 entries. The formulas used to calculate totals can be modified if more entries are needed.
  2. In USAS-R, click to generate SSDT Financial Detail Report July 1 Cash Balances. 
  3. Enter Report Parameters
    1. Format: HTML-Table
    2. Include specific Fund(s) or Fund-SCC(s) if desired
    3. Report for a specific posting period can be used to generate report for previous Fiscal Years if needed. See  the Reports Manager section on Query Parameters options included on SSDT Template Reports for additional detail on how to use this parameter.
  4. Save the report parameters using the Save and Recall option.
    1. Select the blank option and enter a save name. 



    2. Once you tab off the field, the 'save' icon will be active.  Click on  to save your report and parameter settings. 

    3. Your saved report name will now be included in the 'save and recall' drop down menu. 
  5. Once the report parameters have been saved, the link icon will become available. Click the link icon to open the Report Direct Link pop up window. 
    1. Right click on the URL link that appears in the pop up window. Choose to copy the link address.



      Note
      Do not check the box for 'Include parameters?' This option adds to the length of the link that could cause issues with the Excel character limit.


  6. Open the Financial Detail Template with Running Fund Balance spreadsheet in Excel. Navigate to the Data Tab. Click 'Get External Data from Web.'



    1. Paste the Link into the Web Address field. Click Go. 


    2. Enter username and password for USAS-R. 

      Note
      If you are not prompted for credentials after you click 'Go' then Excel has not started loading the data. If this happens, close the pop up window and click on 'Get External Data from Web' again.


      Warning
      It is common for this step to take time to load - specifically when pulling a large set of data (full Fiscal Year). The HTML Table to show in the preview window and the 'Import' option to become available when Excel has finished loading.


    3. Once data from link loads, click Import.
    4. Click on cell A3 to select where to put the imported data.


    5. Enter username and password for USAS-R. 

      Warning
      It is common for this step to take time to load - specifically when pulling a large set of data (full Fiscal Year). Excel will indicate in the bottom left corner of the window that it is running a query in the background. 


  7. Enter the Beginning Fund Balance in cell E2.



    1. The SSDT Cash Summary report can be generated in USAS-R for the same parameters used when generating SSDT Financial Detail July 1 Cash Balances (fund, posting period, etc.) to get the Beginning Fund Balance. Once the beginning Fund balance is entered, this report is also useful for balancing the other Calculated fields on the Financial Detail spreadsheet - Ending Fund Balance, Total Received Amount, Total Expended Amount.
    2. Once the Beginning Fund Balances has been entered, Column J will populate with the running Fund Balance for each transaction.
  8. Save the Excel spreadsheet to your computer.
  9. To Refresh spreadsheet
  10. Send to others

Refresh Spreadsheet

Now that the spreadsheet has been saved with the Report link, it can be opened at a later time and the data can be refreshed to match the current USAS-R database information for the saved report parameters. For example, this spreadsheet for the Financial Detail report can be created at the beginning of the fiscal year and then refreshed throughout the year to view the current FYTD figures.

  1. Open the saved Excel spreadsheet
  2. Navigate to the Data tab. Click 'Refresh All.'

    Image Added

    1. Enter username and password for USAS-R. 

      Warning
      It is common for this step to take time to load - specifically when pulling a large set of data (full Fiscal Year). Excel will indicate in the bottom left corner of the window that it is running a query in the background.


      Note
      It is also important to note that the report parameters saved using Save and Recall must remain active and unchanged in USAS-R. Any changes to the saved report parameters used for the report link will impact the data in the spreadsheet.


Send to Others