Best strategy to automating 'complex' calculation tasks from Excel

• eduardoalmeidabr

  Old Hand

  Points: 350

  More actions

  April 10, 2020 at 10:05 pm

  #3742127

  I have a workbook which has 4 spreadsheets.

  The first spreadsheet uses parameters and data from the other three to calculate some functions and in the end it gives the net present value of some benefit plan conceded to an employee for each participant and its dependant (roughly 85,000 participants).

  1. Description

     1.1. First spreadsheet - Net Present Value of the Obligations

  That same first spreadsheet uses 21 tables with ~30 columns each. That first table is the only different from the others. The other 20 tables have the same exact structure, only the parameters received by each table are different (age and interest rate).

  Each table is calculated as follows:

  The data from the user is retrieved from the third table using VLOOKUP. The age of the user is calculated from their birthdate. The current age is then incremented until 120, creating one row for each age from the current age until the user reaches the age of 120. Each variable from the table is calculated for each age using the variables' previous values.

  I achieved that result using CTEs (common table expressions) for the first table.

  But the other 20 tables whose strutucre is the same for all of them need to be calculated using data from that first CTE.

  1.1.1. First table

  Only the first table has the age and it's used by all the tables. I was able to translate this first table using CTE (and I use it with the fourth spreadsheet that I was able to translate to SQL. See below.) See on StackOverflow

  

  1.1.2. Second table and the following tables

  That pattern repeats for the other 19 tables (20 tables with the one below). Each row represents one age from the first table.

  In this table I have to get the total sum of the social secutiry benefit, disability savings, pension and release earnings columns and sum those values to the columns with the same name/header in the other 19 tables, only the parameters are different.

  

  I need to pass this workbook calculation to SQL, but I don't know what's the best approach.

  1.2. Second spreadsheet - Parameters

  These are the prameters used to calculate the first table in the first spreadsheet. Just VLOOKUPs to retrieve data from other tables with some probabilities.

  1.3. Third spreadsheet - Benefit plan participant data

  In this spreadsheet we have the information of the participant and their dependants. I have to calculate the variables from the first table for them both. I use a CTE and in its recursive part I am using a OUTER APPLY function, because when I used JOIN as soon as one of them reached the age of 120 the younger one would stop calculating. I don't have this problem using

  1.4. Fourth spreadsheet - Comutation table

  The commutation tables were done using once again CTEs. I used a stored procedure to produce a CTE and then when I call the calculation procedure I create a temptable and JOIN with the calculation table in SQL.

  2. Approaches

  Firstly, I don't even know if using CTEs are the best approach even for calculating the first table. I have serious doubts about my work, because I have nobody to whom I can ask questions, only Q&A (like StackExchange) sites and forums on the internet.

  Secondly, I have the woorkbook on Google Sheets (in Portuguese), but I'm reluctant to share it, because there are really sensitive data in it. But if you need access to it, send me your email address so I can share it.

  3. Conclusion

  I need some brainstorming on the best approach to solve this problem.

  CTEs, SSIS, windowing, etc. I just don't know what to do. I am really stuck. Cannot move forward.

  Let me know if you guys need some more clarification on my part. I really need help.

  I am trying to move to SQL Server specialized forums, because the community, for what I have seen, is always trying to push the others to find the best solution. Asking questions, helping to understand the problem better.

  Sorry for bad English!

  And thank you, guys!