The State of Virginina publishes an Excel spreadsheet for each year titled “Comparitive Report of Local Government” (http://www.apa.virginia.gov/APA_Reports/LG_ComparativeReports.aspx). We had a contractor build an Excel VBA project to normalize this data. The Excel file to do this normalization is found in Box at Clients à Virginia à Municiplaities à Excel Normalization. The file is name: FinancialDataImportTemplate.xlsm.
1. Open the Excel Import trmplate.
2. Rename the Compartitive Report to “Virginia.xls”. The paths in the VBA are hard coded to the above location (M:\BoxSync\Clinets\Virginia\Municiplaities\ExcelNormalization).
3. Open Virginia.xls
4. Delete the cells E63, E64, E65 from ‘Exhibit A’. I pull the names of the counties, towns, cities from this column so this data should not be there. There is stuff like ‘Locality’, ‘Total’, ‘Grand Total’ etc but I have put checks for that.
5. In the ‘Raw Data’ sheet press the ‘Fill Data’ blue button. Wait as data is filled.
6. Once it is filled just write “=TODAY()” for the ImpFddDateAdded and ImpFddDateModified columns. Double click on the lower right corner of both these cells to fill up the column.
7. The normalized data is now stored in the template file and can be imported into MySQL.
NOTE: Virginia does not report Fund Balance. Not sure if it is collected or not.