Script to download contribution information and export to JSON

[tommy-stone]

- Total contributions calculation
    1. Import $candidate_name_finance.csv 
    2. From the A-Contributions, C-Contributions, I-Contributions sheet there is a Tran_Amt2 column. Take the sum of each column and add them together (this is the sum of total campaign contributions
3. Export information to JSON for later user
- Total expenditures calculation
1. Import $candidate_name_finance.csv
2. From the D-Expenditure, G-Expenditure, E-Expenditure sheet there is an Amount column. Take the sum of each column and add them together (this is the sum of total campaign expenditures 
3. Export information to JSON for later use
- Contributions per zipcode
1. Import $candidate_name_finance.csv
2. From the A-Contributions, C-Contributions, I-Contributions sheet there is a Tran_Zip4 column. Find all unique values from each column in each sheet. Add all the values for each zip code so that there is a list of each zip code and total amount of campaign contributions from zip code
3. Export to json for later use
- Contributions per occupation
1. Import $candidate_name_finance.csv
2. From the A-Contributions, C-Contributions, I-Contributions sheet there is a Tran_Occ column. Find all unique values for Tran_Occ. Then take a total of the campaign contributions per each Tran_occ group.
3. Export to JSON for later use

[BolunThompson]

Hello! I've finished writing the script and here's the link, but I have a few questions. In the issue, you mentioned an F-Expenditure sheet, which doesn't exist. Do you mean the E-Expenditure sheet? Also, should I export the calculations to one or to multiple JSON files? Lastly, in the repo, where will the cal-data file be? In the script, I left it in my documents folder, but that would need to be changed for actual use. @tommy-stone

[tommy-stone]

@BolunThompson

Clarification for F-Expenditures
The file I sent was an example. Unfortunately the public.netfile.com site is still broken. I found an old 2019 xlsx file from November. The 2019 file is in downloads/static/efile_SD_CSD_2019.xlsx. This has the F-Expenditures sheet. You can do another pull to download the file and work from that. efile_SD_CSD_2019.xlsx is static but there is also another file called efile_SD_CSD_2020.xlsx which will be added to your script. You can do another pull request from master to download the files.

JSON file export
Each calculation will have its own JSON file. I am not sure where it is going to live so for now let's put it in downloads/raw

cal-data file
Which file are you referring to?

Also, make sure to create a separate branch fo this work. We want to commit it to master after we have tested everything.

[BolunThompson]

@tommy-stone

The 2019 file isn't in the folder, but in the 2017, 2018, and 2020 files there is also no F-Expenditure sheet. Do you mean the F-Expenses sheet?

Sorry for the ambiguity, I was asking where would be the file where I would take the data from (e.g efile_SD_CSD_2020.xlsx). Just to confirm, this script only needs to run on one file, correct? (ie: you can only specify one file to take the data from)

Also in the future, should I contact you on Slack about this or on Github?

[tommy-stone]

@BolunThompson Sorry, i didn't do the pull request. I verified they are there now https://github.com/opensandiego/disclosure-backend-static/tree/master/downloads/static.

That was a mistake on my part, it's not G,D,F-Expenditures but G,D,F-Expenses (as you had mentioned). I updated the notes to reflect this.

The files are saved in downloads\static folder and will be updated on a weekly basis. The files will always have the same name though. The script will run on two files, 2019 and 2020 (because of the year change). 2019 file will be static.

If it's a quick question this works (we can also document our notes). If it's something more in depth slack would be better.

[BolunThompson]

There is only a F-Expenses sheet, no G or D expenses sheet. There is also no Amount column on the F expenses sheet. What should I use as an alternative?
@tommy-stone

[tommy-stone]

@BolunThompson Alright, I see where I messed up and I correct the above description. In the D-Expenditure, G-Expenditure, E-Expenditure sheets there is an amount column that you will sum up.