1 / 36

Data Integration and Consolidation

This guide outlines the step-by-step process for creating specific folders on the C drive related to Budget Management System (BMS) data. The folders include BMS-ALLOTMENT, BMS-UTILIZATION, BMS-PRIORYEARS, and BMSAllData. It details exporting Excel files from these folders, integrating data, and saving outputs systematically. Users can follow the procedures for data manipulation, ensuring effective consolidation and reporting of essential BMS files. Proper management and integration improve oversight and resource allocation across divisions and schools.

colum
Télécharger la présentation

Data Integration and Consolidation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DataIntegrationandConsolidation

  2. CreateFolderat Drive C: Name of folders to be created: • BMS-ALLOTMENT • BMS-UTILIZATION • BMS-PRIORYEARS • BMSAllData C:\BMS-ALLOTMENT C:\BMS-UTILIZATION C:\BMS-PRIORYEARS C:\BMSAllData

  3. Exporting and keeping of Excel Files from each Implementing Unit BMS C:\BMS-ALLOTMENT C:\BMS-UTILIZATION C:\BMS-PRIORYEARS C:\BMSAllData

  4. Export data to Excel. Step 1 Step 2 Utilizations Allotments Step 4 Prior Year’s Step 3

  5. SAVE ASto C:\BMS-UTILIZATION folder.Filename:Name of Division/School

  6. SAVE ASto C:\BMS-ALLOTMENT folder.Filename: Name of Division/School

  7. SAVE ASto C:\BMS-PRIORYEARS folder.Filename: Name of Division/School

  8. Exported Excel FilesSaved at C:\BMS-UTILIZATION folder Names of implementing units including the division office proper

  9. Exported Excel FilesSaved at C:\BMS-ALLOTMENT folder Names of implementing units including the division office proper

  10. Exported Excel FilesSaved at C:\BMS-PRIORYEARS folder Names of implementing units including the division office proper

  11. Integration of Excel Files Allotments Prior Years Utilizations C:\BMSAllData

  12. Three (3) Excel FilesIntegrator of all BMS excel files Filenames:

  13. Integrate data fromC:\BMS-UTILIZATION folder 1. Open this excel file: Integrator – BMS-UTILIZATION Click this button.

  14. Integrate data fromC:\BMS-UTILIZATION folder Output: Delete column A

  15. Integrate data fromC:\BMS-UTILIZATION folder Output: If you noticed, there is no heading. The next step is to copy the heading from one of exported excel files (C:\BMS-UTILIZATION folder). Insert row at first row (A1)

  16. Integrate data fromC:\BMS-UTILIZATION folder Output: Copy columns A1:T1 from one exported excel file and Paste here at A1

  17. Format Column/Cells to TextFormat 1. SelectColumn

  18. SAVE TOC:\BMSAllData folder Output: Save As to C:\BMSAllData folder. Use exactly this filename:tempTable

  19. Integrate data fromC:\BMS-ALLOTMENT folder 1. Open this excel file: Integrator – BMS-ALLOTMENT Click this button.

  20. Integrate data fromC:\BMS-ALLOTMENT folder Output: Delete column A

  21. Integrate data fromC:\BMS-ALLOTMENT folder Output: If you noticed, there is no heading. The next step is to copy the heading from one of exported excel files (C:\BMS-ALLOTMENT folder). Insert row at first row (A1)

  22. Integrate data fromC:\BMS-ALLOTMENT folder Output: Insert row at first row (A1)

  23. Integrate data fromC:\BMS-ALLOTMENT folder Output: Copy columns A1:X1 from one exported excel file and Paste here at A1

  24. Format Column/Cells to TextFormat 1. SelectColumn

  25. SAVE TOC:\BMSAllData folder Output: Save As to C:\BMSAllData folder. Use exactly this filename:tblAllRAODataAnnexA1

  26. Integrate data fromC:\BMS-PRIORYEARS folder 1. Open this excel file: Integrator – BMS-PRIORYEARS Click this button.

  27. Integrate data fromC:\BMS-PRIORYEARS folder Output: Delete column A

  28. Integrate data fromC:\BMS-PRIORYEARS folder Output: The next step is to copy heading from one of exported excel files from (C:\BMS-PRIORYEARS folder). Insert row at first row (A1)

  29. Integrate data fromC:\BMS-PRIORYEARS folder Output: Insert row at first row (A1)

  30. Integrate data fromC:\BMS-PRIORYEARS folder Output: Paste heading here at A1

  31. SAVE TOC:\BMSAllData folder Output: Save As to C:\BMSAllData folder. Use exactly this filename: PriorYearObligation

  32. Contents ofC:\BMSAllData folder tblAllRAODataAnnexA1 tempTable PriorYearObligation

  33. CONSOLIDATEDATA

  34. Consolidate Data 1. Open this Access file: Consolidator Access File

  35. Preview/Print Reports

  36. Thank you!

More Related