Automating My Data Gathering

If you don’t use excel VBA, you have NO idea how fast computer can do tasks. It’s just mind boggling how quickly the VBA can do your tasks. And you may not be aware just how computers can take over your job. You need to understand how to use the computers to your advantage so you can do the things beyond the routine.

Every night, I pull data from the web (mainly Wikipedia but lately also from the Covid Tracking Project) using the Power Query in either Excel or Power BI. I would like to pull from the web straight into Power BI for graphing purposes but sometimes the data from the web does not lend itself very well to a straight refresh (for example, field headers will change every day), so I will pass the data through Excel and do some finagling to get the data to where I need it. I probably spend over an hour pulling down information and storing it on my machine. At present time, I don’t use all of the information – largely because I learn what I really need – but in case a question comes up, I will have historical data to use.

So, I have constructed some VBA programs to pull in the information. I broke up the steps into roughly three parts, going from easiest to hardest. The first part reduced my time from over an hour to maybe about 20 minutes. I push a button “go” and in a minute the program has finished running. I’ve reduced the time from roughly 45 minutes to 1 minute.

I still have to review what the program did because the web sites will often change the layout of their data; that’s what happened the first night I ran the program. Something changed so I had to quickly alter the program.

Now I’m working on the second and third part of the programs and I’ll see if I can get the same type of time reduction.