Program to take spreadsheet data, compute data, charts/graphs for multiple pages that allows user input

Question

I have a spreadsheet that is for a group of clients. Each line of data has information on insurance claims, of which include a client number that is unique to that client.

I am looking for a program/software that will allow me to compute various metrics and present those in a table, with an overall comparison to all the other clients.

I also need to present data that is compiled into a top 5 metric that is then placed in a bar graph.

This data needs to be computed for each individual client, and I often have to do this as one-off projects throughout the year for specific clients.

Finally, I need to then pass this off to colleagues that will then look at the information and add in text that is relevant to each client.

I receive these files as a .CSV file and currently work in Excel to compute the information and then copy and paste the information to a document file.

I work in a Windows environment. I am currently limited to Office tools, however I'm willing to use my own personal computer to install an application that will give me the ability to do what I'm looking for.

Thank you for your time and suggestions. Please let me know if you have any questions.

Answer 1

Good explanation of your project - thanks for that.

If I was doing this, I would use Access as the core hub.

Some of the Access elements that would be useful:

1. Drive Excel to read spreadsheets and import them into Jet
2. Reporting direct from Access for lists
3. Drive Excel to create spreadsheet output, and graphs from that

There might be a more dedicated tool, that doesn't need programming, if there is I'd also be keen to know of it.

Answer 2

I recommend that you use esProc to complete your one-time project for one year without any cost. The esProc is ready to use, and its simple scripts can import CSV files into internal tables easily, associate user data in several tables, filter by conditions, group, and summarize various calculations. Finally, you can export the summary data into Excel by user ID.

For a simple example, the code is as follows:

sOrder=file("D:\\sales.csv").import@t()
emp=file("D:\\emp.csv").import@t(EId,Name,Dept,Gender)

// Order table left associated employee table
jt=join@1(sOrder:s,SellerId;emp:e,EId)
eOrder=jt.new(s.SellerId:SellerId,s.Amount:Amount,s.OrderDate:OrderDate,
              e.Name:Name,e.Dept:Dept)

//select orders by time range
day100=eOrder.select(OrderDate>=startDate&&OrderDate<=endDate)

// Find the 3 largest orders for each sales
top3=eOrder.group(SellerId;~.top(3;-Amount):t).conj(t)

//Export details of up to 3 orders by employee ID
top3.(file(~.SellerID/".xlsx").xlsexport@t(~))

Answer 3

Metabase is an opensource reporting and data visualisation tool that you can easily run locally on your desktop by downloading the jar file.

It allows easy import of data and you can use the inbuilt db without connecting to any other database if so wish.

Once data is imported you can create dashboards charts tables etc quite easily without any knowledge of sql etc. as it's intended for non-technical users.