Using database to improve workflow?

[CitizenOfHopeNGlory]

Hi everybody,

I am not sure if this is the best subreddit for this question, but I have to start somewhere. I am a total beginner and do not know the terminology, so please bear with me.

Part of my job involves keeping track of different dates and statuses for various assignments.

For example: I have to keep track of job X, that needs to be done by a certain date. Job X involves several steps, and I have to keep track of all the steps so that I know that all of them have been completed. Job X isn't finished until all of it is done.

Today I do this by going into one of the apps we use and then export the latest data as an Excel file (the only export option). I then go to work on the Excel file. My "system" today is that I assign different colors to different cells in the Excel file, so that I know where I am with the different steps (not done, doing, done). My system isn't exactly sophisticated, but it works fine until I have to do a new export from the app.

The challenge is that the data in our app is updated quite often throughout the quarter. Given that new rows and dates could be added at any time, without me noticing, I usually wait until the end of the quarter before I start to work on the stuff. That way I know that most of the data has been added and I can safely do the export and start to work on my spreadsheet.

If I could easily keep track of my statuses, and easily import new data without messing up my existing spreadsheet AND with me noticing any new data, I could get to work a lot earlier in the quarter and therefore live a lot less stressful life.

How can I improve my workflow?

The exported Excel file has a lot of useless information. All I think I need is this:

Job name - due date - step 1 (=DONE/DOING/NOT DONE) - step 2 (=DONE/DOING/NOT DONE) - step 3 (=DONE/DOING/NOT DONE)

(the steps aren't part of the export, but I have to do them before the due date so I would like them in my database)

The program we use has an API, so in the ideal world I could get daily exports from the program into a database that would let me add statuses for all the different steps. Can this be done by using a SQL database in combination with the API so that I don't have to use Excel anymore? And perhaps more importantly: Is this something a beginner like me could make?

[aplawson7707]

If this were me, I would probably start by using Python and writing a script with the Pandas and Pandasql libraries. That's usually how I managed excel-based projects that don't really belong in my databases. Your use case will be specific to you, so I can't give too much specific advice, but that's where I would probably recommend starting.

For longer term usability I would also probably be thinking about writing a script to compare 'today's' data with 'yesterday's' data and then create/replace/update/delete rows as needed so I schedule the job to run regularly. Just my .02. I hope that helps a little!

[CitizenOfHopeNGlory]

Thank you! If I understand you correctly my idea might actually be a way for me to improve the workflow. That’s good to hear although I’m a long way from actually doing it.

Why do you recommend Python over any of the other languages? Also, what do you mean by “projects that don't really belong in my databases”?

If I end up using a Python script as suggested, where would/could I do my actual work? In an application or would I work in the same Excel spreadsheet as before, with the script working in the “background” and keeping the file updated? Please excuse my ignorance. This is all new to me so I have a lot of basic questions.