Top Learnings from 2020

A lot of my time this year, has been spent doing project management activities.
Unfortunately this is hard to automate, until we get robots. So cool projects were on the backseat - hence not too many updates.

However here is my list of things that are exciting enough that I remember.

Excel

The focus on Excel this year, was mainly on dashboards due to the aforementioned project management focus. Honestly, I am terrible at designing dashboards. Just good at programing them.

The new dynamic array update is amazing. It opens up many possibilities - I have created a lot of dashboards with smart graphs and sparklines using named arrays and dropdowns.

The filter/unique formulas are finally in Excel (have been in Google Sheets for awhile) and I have been using them immensely. My company moved from on premise shared data storage to Sharepoint a few years ago, which means we can’t link between workbooks with formulas. So we are forced to query them in - which gets our data to be in a tabulated format (or at least more so than before). This makes it easy for me to create interactive dashboards with Unique, Sort and Filter. One example I have is an order list - the user can filter down to the products they want, add it to their shopping cart, then send off their cart to accounts.

Works beautifully - however too many of these formulas can lead to spreadsheet slowdowns.

As of December 2020, not all users have these features. So creating and sharing some of the dashboards will have limited reach.

Python

I’ve used this to support Excel using things like Xlwings and keyboard presses. Most projects were in my free time, as projects around my work were limited this year - project/people management leaves little time for technical creativity.

For example during a dashboard creation with Excel, I had to copy certain formulas every few hundred rows. This was very time consuming and boring (but made the data incredibly tabulated and structured) - there was probably a better way but I had no time to discover it. So I quickly used some pyautogui (i.e automating mouse movement and keyboard input) to accurately automate the task. Only took me 10 minutes to automate this, so definitely a win. Plus a heck of a lot more entertaining!
I’ve also used this technique, (with machine vision), to automate Tinder and Twitter activities.

I’ve also learnt a bit more about getting Python to talk with SQL databases. Using Yfinance, I mined daily share data for my local market and stored it into a local DB file. I queried it back into Python and used some machine learning algorithms from SKLearn to tell me which stock would have the greatest 2 day returns. I.e buy low today, sell high tomorrow. Millions of $, here I come!

PowerBI

Structured data in Excel, databases, dashboard requirements, means PowerBi was a cool tool to get into. I learnt a lot of Dax and how to get the dashboards to sort of do what I want.
It is a struggle coming from Excel to PowerBi - just because you are limited from what you can do. Possibilities aren’t endless like Excel/Python.

However it forces you to create high level dashboards with drilldowns, which is probably good dashboard design. It is great sharing your dashboards, and not having to worry about macros breaking or slowdowns. Despite huge databases in the background.

That’s all I can think of right now. My personal goal for next year is to continue with Python, and learn more about databases and Python dashboard/visualizations tool. I want to be a top poster on r/dataisbeautiful.
I just need to find projects at work/i.e that make me money to help with motivation.

If any of the projects I mentioned above interest you, let me know in the comments and I will potentially create a post/tutorial sharing it.

Finally, I didn’t mention Covid and just remembered about it now. I am lucky enough to be in a competent country that got rid of it very quickly. Life gets back to normal FAST. I see people online claiming life will never be the same, handshakes, hugs gone. This is all nonsense - people forget about it all in a couple of weeks. So keep learning, vaccine should eliminate it, and normality will resume quicker than you think.

Hopefully people just remember to not eat bats.