[FatimaFawaz] reconciliation tool (7h)

[shadiakiki1986]

@FatimaFawaz let's use this issue as a placeholder for your next task in financial engineering: building a reconciliation tool

Steps
- [x] familiarize yourself with [pandas](http://pandas.pydata.org/) via their [Lessons for new pandas users](https://pandas.pydata.org/pandas-docs/stable/tutorials.html#lessons-for-new-pandas-users) (~1h)
- [x] reconcile two CSV files with [pandas](http://pandas.pydata.org/) (~1h)
    - use the ssoc18.teamshadi.net server to write up a jupyter notebook that reads the files into a dataframe for each using [read_csv](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html)
    - I uploaded 2 sample files to your home directory on the server
    - use the [pandas.DataFrame.merge](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html) function to merge the two dataframes into one dataframe
    - calculate the a new field `diff = position of bank 1 - position of bank 2`
- [x] familiarize yourself with [flask](http://flask.pocoo.org/) (web app microframework) by following the [tutorial](http://flask.pocoo.org/docs/0.12/tutorial/)
- [x] create a flask app that allows a user to upload two files, and that reconciles them (as you did in the notebook above) and displays the reconciled dataframe with the `diff` field (~2h)
- [x] confirm that "symbol" and "position" are in the columns of the uploaded files (~1h)
- [x] list on the home page all the *file pairs* that were already uploaded so that clicking on them will directly go to their reconciliation (without having to re-upload) (~2h)

[FatimaFawaz]

So far I familiarised myself with Pandas, only went as far as need for the reconciliation. I wrote up a Jupiter notebook that read the two files each into a separate dataframe, then I merged the two into another dataframe. I added a column for the diff field where the calculations were made. I started working on Flask but thus far I only managed to reach step 4 of the tutorial. I think I need to go over Python packaging methods in order to make sure I packaged things correctly so that the application can actually get installed. Although, I did get an IP address, the app did not seem to be found. I am not sure if this is how it's supposed to be until I create actual webpages. I used the pipenv installer to for the setup.py and it did create a Pipfile.

[shadiakiki1986]

Ok. Let's have a call tomorrow and fix you're stuck at

On Wed, Jul 4, 2018, 17:08 Fatima-Fawaz notifications@github.com wrote:

So far I familiarised myself with Pandas, only went as far as need for the reconciliation. I wrote up a Jupiter notebook that read the two files each into a separate dataframe, then I merged the two into another dataframe. I added a column for the diff field where the calculations were made. I started working on Flask but thus far I only managed to reach step 4 of the tutorial. I think I need to go over Python packaging methods in order to make sure I packaged things correctly so that the application can actually get installed. Although, I did get an IP address, the app did not seem to be found. I am not sure if this is how it's supposed to be until I create actual webpages. I used the pipenv installer to for the setup.py and it did create a Pipfile.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/teamshadi/ssoc18/issues/7#issuecomment-402490070, or mute the thread https://github.com/notifications/unsubscribe-auth/AIAOhFRUHecX2qTN6XixB4tFE1eEKQ0Lks5uDMxtgaJpZM4VBATR .

[FatimaFawaz]

Created the reconciliation tool but it will not run beyond showing the webpage and refreshing after uploading the two csv files.

[shadiakiki1986]

@FatimaFawaz

• "flaskr_shadi.py" is a copy of "flaskr.py" but fixed (tested it working just now)
• minor fixes are also introduced in "templates / DF.html" (with comments in-line)

Please check them out and integrate "flaskr_shadi.py" changes into your own "flaskr.py".

I added 2 new steps for you (above in this issue description). Please check them out

[FatimaFawaz]

• Integrated your version of flaskr.py into mine
• made a check for the appropriate column names
• Added a name generator for csv files to be reconciled
• created a csv file that lists the paired files and the reconciliation (the file is displayed on the home page)
• created a section where it renders into a new html page based on the reconciliation chosen

What I attempted but isn't working:

• appending the file name and links to the listing csv file
• displaying the updated csv file on the home page

[shadiakiki1986]

@FatimaFawaz I just tried the app. After clicking upload, I got

builtins.PermissionError
PermissionError: [Errno 13] Permission denied: '/home/fatme/Recon/flaskr/flaskr/Upload Folder/f1.csv'

Is this a known issue ATM in your app?

[FatimaFawaz]

No it isn't. I get this on my side.

[shadiakiki1986]

Maybe it's related to the filename chosen? I'm using f1.csv and f2.csv. Can you try with that and let me know if it still works for you?

On Fri, Jul 13, 2018 at 2:28 PM Fatima-Fawaz notifications@github.com wrote:

No it isn't. I get this on my side. [image: screen shot 2018-07-13 at 2 27 38 pm] https://user-images.githubusercontent.com/40644150/42689449-035e390a-86a9-11e8-9d69-9ad162247290.png

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/teamshadi/ssoc18/issues/7#issuecomment-404806508, or mute the thread https://github.com/notifications/unsubscribe-auth/AIAOhMcFNqsbTNGz5hmuLpGJsVZWgivgks5uGIR6gaJpZM4VBATR .

-- Best, Shadi AKIKI www.akikieng.com/shadi

[FatimaFawaz]

It doesn't work with me either. Which is odd considering it took in other file names aside from the ones I tested with. edit: got the same error

[shadiakiki1986]

No problem. I just tried it with different names, and it's working. Here are my review points

• your flaskr folder is full of csv files alongside the py files. Maybe the code is accidentally saving the fiels there instead of inside Uploaded Files
• no need to save the results of the reconciliation in a csv file again
  • when previously uploaded files are re-visited, just re-calculate the reconciliation on-the-fly
  • it is possible (I'm not sure) that these are the files that are getting saved outside of the "Uploaded Files"
• after clicking Upload, there is a line in bold above the table that also contains the values from the table.
  • Erase that from the page since it doesn't add any value to what is already in the table
• When I tested the app, I uploaded two files: 20180713A-f1.csv and 20180713A-f2.csv, but I can't see the f2 file in the Uploaded Folder
• instead of saving all the uploaded filenames to a csv, just rename all uploaded pairs to: yyyymmdd_hhMMSS-f1.csv and yyyymmdd_hhMMSS-f2.csv
  • this allows you to find pairs by seeing that they share the same prefix
  • also, display the prefix on the front page
  • maybe at the bottom of the page, add a section called "previous uploads", and show a list of clickable prefixes (yyyymmdd_hhMMSS from above)
  • after clicking on the link, pass the prefix to the GET HTTP parameter (check here for an example)

[FatimaFawaz]

• Timestamp created for newly made files
• Prefix is added at creation of timestamp and files are added to the upload folder on that basis

Server stopped accepting cookies (too many redirects error). Cleared the cookies on browser and shut down previous terminal runs but nothing changed. The above changes were confirmed to work prior to this.

• Added an http parameter line that takes the argument from the URL and adds it to a new DF, although I didn't add the necessary param to it due to the server not working.
• new DF for previous recons uses make_clickable to add back the parameter from the URL (although I don't know how the parallelism between the two dataframes works yet I would assume it's a on same index basis) -make_clickable takes in the args and redirects to a URL containing them as params.

[shadiakiki1986]

I just checked your flaskr.py file and found that it was missing the following

def Recon():
    if request.method == 'GET':
        return render_template("DF.html")

Without this, your application was going into an infinite loop. I saved this in flaskr_shadi_2.py and ran it and saw it working.

Now there is another bug: NameError: name 'FileFrame' is not defined.

@MickMek is an engineer who is working with me on another project, but he's familiar with flask too. I'll ask him to get in touch with you today, maybe you guys can have an online meeting on meet.google.com where you can show him your working app and ask him your questions above

[shadiakiki1986]

So following our phone call now, I added a listfiles route that demonstrates

• how to list the file prefixes (in flaskr.py)
• and how to identify which one is selected (in the template listfiles.html)

Let's talk again tomorrow

[FatimaFawaz]

Added a new route that takes in the prefix, the program then does a reconciliation The program works now. However, after going to the URL with the prefix, does not permit upload.

[shadiakiki1986]

Congratulations! You just got your first working version :smile: I just opened a new github repository for you to commit your current code. Please check your email for details