How do I automate with excel by pulling files in my work shared network drive?

So this one is a doozy… I’ve been assigned to make an COI tracking sheet (certificate of liability). I am able to created an excel table by exporting a bunch of data from my legal team Sharepoint which contain information such as “subcontractor company name” “requestor” “subcontract #” “line of business” you get the idea.

The purpose of this tracking sheet is for the project managers can look up when their COI insurance(general, automotive, workers comp, etc…) is about to expire so they can get renewed. So I have a column for each type of coverage and they are all color coded (green = good, Yellow = about to expire, Red = expire) my main hiccup right now is figuring out to automate a way to pull information for the COI (ACCORD 25 form) so it can all update as it gets entered in

Note: this COI tracking sheet is going to live in an online share point(so excel online)

The COI files do not live in the share point they live a share network drive in a folder and the COI are in sub folders with each individual subcontractor.

Just looking for advice

Thanks!