I have been assigned to create a SharePoint for a business with multiple departments. So far it it has gone well but one of the requirements was a dashboard like tool that automatically updates daily with some of the most important numbers for the day. The dashboard would pull information from excel sheets on a local server. I have done a good bit of searching and have not found anything that would be able to do this. Is it even possible to do? If so could you explain, thanks. I know this is brief so if there are any questions let me know.
You could synchronize a SharePoint list with the excel spreadsheet. This would allow you to edit the excel sheet which would update a list on the site. You could then make the dashboard using information with the list, which will sync with any changes to the excel sheet. You can find information on syncing the list and excel sheet here.
By using Excel Web Access Web Part you can embed complete Excel workbooks, or its parts including sheets, named areas and named items such as figures and tables.
If you need to do visualization or show some graph, you should have it done in Excel as a figure, name the figure, and have it called by your Excel Web Access Web Part. The naming will remain although the data inside the area would be updated. A named area is then defined within the SharePoint inside the web part's properties:
(This is optional.) To display a single item, such as a chart, table, PivotChart report, or PivotTable report, in the Named Item box, type the name of the item. This causes only that item to be displayed in the Web Part, instead of displaying the entire workbook.
If your requirement is to show several named areas of the workbook, then you'll also need several Excel Web Access Web Parts, as each web part may show only one (named) area at once.
Read more about Display Excel content in an Excel Web Access Web Part. Also a related read of naming e.g. tables inside Excel: Rename an Excel table.
When you are defining the Excel file to be used in the web part, there might be issues when calling a file outside the SharePoint. There are many scenarios where issues might occur, so I'll not get into that. Also the referencing might work without any issues. Talking from own experience, I've sometimes had to implement a replication for the Excel file in order to get it into SharePoint's environment to trivialize the referencing.
As you added in a comment above, your current way of handling the Excels involves a naming convention of adding the ongoing weekly number to the end of the file name. This would cause the Excel to not update in your web access web part. I had a similar issue in one customer case, where we as a workaround agreed to instead name each week's file with the same file name as the previous ones (i.e. without the unique number). Then by using SharePoint's file versioning, which can be enabled in the library the document is hosted in, the Excels of the previous week could be stored as past versions. This had its additional benefits, as the history remained accessible within SharePoint, and no manual file naming was required.