Identify reason for negative balance in budget

[theryankelly]

From Comment in Issue #3

@nelsonri72
https://www.google.com/fusiontables/DataSource?snapid=S1428703movB
http://www.ri.gov/opengovernment/

NEGATIVE BALANCES:
There are also negative balances throughout the balances dataset some resulting in overall negative balance for the year in some categories/agencies.

[joealba]

I will grab updated data and push another set of json files in hopes that the output winds up without negatives and is easier to work with. But, the data is the data. You may wind up with some negative values and need to code accordingly.

Negative spending is good, isn't it? :)

[nelsonri72]

Summary of issue below: Can the '*BALANCES' dataset be validated to the transparency portal and provide the filter/coding needed to match up? If historical data has changed, I would think the Transparency portal should reflect that as well.

---

NEGATIVE EXPENSES: (as requested in meeting)
There are negative expenses throughout the '*balances' dataset (a.) some resulting in overall negative expenses for the year in some categories/agencies(b.).

(a.) NEGATIVE EXPENSE only records

https://www.google.com/fusiontables/DataSource?snapid=S1439102pXAe

(b.) AGGREGATE EXPENSE TOTAL BY DEPT/AGENCY -
Notice some have negative expense balances while ALL +/- TOTALS DON'T MATCH UP TO TRANSPARENCY PORTAL (http://www.ri.gov/opengovernment/agencies)
e.g. see year 2013 - Agency: 014 MILITARY STAFF
$-4,339,231.9

or Agency:013 DEPARTMENT OF ADMINISTRATION 2013 SUM ($253,942,515.86) (COUNT 4354 records)

Database:
https://www.google.com/fusiontables/DataSource?snapid=S1439201trhY

Spreadsheet:
https://docs.google.com/spreadsheets/d/1BzvD3MQla6xyCQUjsrlmUfo052NnI1iHr8SO8VWqYis/edit?usp=sharing

[joealba]

I have pushed updated json files with current data as of October 5.

[nelsonri72]

The Agency JSON files only have 11 negative balances in Benefits category in years 2014,2015. Is the JSON data more valid than the original datasets?

YEAR/AGENCY/CATEGORY/TOTAL

2014-Department of Education Benefits -2202.5
Department of Revenue Benefits -89.96
Department of Transportation Benefits -526.66
Department of Children, Youth and Families Benefits -427
Office of the General Treasurer Benefits -414.77
Department of Corrections Benefits -968.19
Office of the Adjutant General / Military Staff Benefits -682.5
Department of Business Regulation Benefits -703.87

2015 Department of Revenue Benefits -579.61
Department of Transportation Benefits -877.06
Office of the Adjutant General / Military Staff Benefits -654.79

[purpletieri]

Are those negative balances in benefits from general revenues? My initial guess is that they're benefits for employees paid with state dollars that are later reimbursed from federal sources. The problem is that the reimbursements occur later through accounting journal entries so they may not show up as expenditures in this dataset.

Sent from my iPhone

On Oct 21, 2014, at 17:50, nelsonri72 notifications@github.com wrote:

The Agency JSON files only have 11 negative balances in Benefits category in years 2014,2015. Is the JSON data more valid than the original datasets?

YEAR/AGENCY/CATEGORY/TOTAL
2014-Department of Education Benefits -2202.5
Department of Revenue Benefits -89.96
Department of Transportation Benefits -526.66
Department of Children, Youth and Families Benefits -427
Office of the General Treasurer Benefits -414.77
Department of Corrections Benefits -968.19
Office of the Adjutant General / Military Staff Benefits -682.5
Department of Business Regulation Benefits -703.87

2015 Department of Revenue Benefits -579.61
Department of Transportation Benefits -877.06
Office of the Adjutant General / Military Staff Benefits -654.79

—
Reply to this email directly or view it on GitHub.

[purpletieri]

I imagine Joe Alba's updated JSON files are more accurate than the original datasets we provided since his methodology is consistent with what's on the state's transparency portal. What we sent was just a data dump with no filtering.

Sent from my iPhone

On Oct 21, 2014, at 17:50, nelsonri72 notifications@github.com wrote:

The Agency JSON files only have 11 negative balances in Benefits category in years 2014,2015. Is the JSON data more valid than the original datasets?

YEAR/AGENCY/CATEGORY/TOTAL
2014-Department of Education Benefits -2202.5
Department of Revenue Benefits -89.96
Department of Transportation Benefits -526.66
Department of Children, Youth and Families Benefits -427
Office of the General Treasurer Benefits -414.77
Department of Corrections Benefits -968.19
Office of the Adjutant General / Military Staff Benefits -682.5
Department of Business Regulation Benefits -703.87

2015 Department of Revenue Benefits -579.61
Department of Transportation Benefits -877.06
Office of the Adjutant General / Military Staff Benefits -654.79

—
Reply to this email directly or view it on GitHub.

[nelsonri72]

@purpletieri The JSON datasets don't contain the 'Source' data to determine if 'General Revenues' but after comparing to the original datasets, not all of the negs are of general revenues

Department of Transportation -526.66 09 Other Funds
Department of Children, Youth and Families -427 01 General Revenue
Office of the General Treasurer -414.77 03 Restricted Receipts
Department of Corrections -968.19 01 General Revenue
Office of the Adjutant General / Military Staff -682.5 02 Federal Funds

---

@joealba I'm thinking we may need to get additional JSON data with 'Source', including 'Fund', 'LineSequence', and 'Natural' (a subcategory to existing 'Category' JSON dataset). I would think if multiple datasets, then they'll need to be indexed to link to each other, especially in the Sankey diagram

We may need this data for charts by 1.)Source, 2.)Fund, 3.)Dept(Agency), 4.)Program(LineSequence) 5.)Expenditure Type(Category/Subcategory(Natural) (e.g. aid to cities, has subcategory/natural acct.s)

[joealba]

@nelsonri72 I can add those additional pieces of JSON data, but you will wind up with many additional data points if I do so -- since I'll have to group on those data elements too, not just on the expenditure category. Is that what you are looking for?