An Approach to do this via Excel?? (1 Viewer)

[arpod]

Hi there,

Not 100% sure if this is the right area, but I have a bit of a weird one - I think. Basically I have a query in Access that outputs some values, which I then need to calculate by subgroup in Excel, and return the output result to the relevant record in Access. My (possibly naive) thinking is that I'd save the query into a temp table, then update that.

I realise that it's possible to run Excel calculations from Access, but I don't believe it's possible in this instance as the calculation is the result of a 100K row Monte Carlo simulation in Excel that's not using a normal distribution. (Unlike the sample, which is just calculating an average). It's also why the sample spreadsheet is setup horizontally rather than vertically.

So from a logical viewpoint, it is copying all the values in a subgroup, calculating the value for each in Excel, then adding the resultant value back to Access.

Attached are a demo DB and Excel file. The DB has two tables, the intent to highlight what before (Example_Input) and after (Example_Result) look like, as well as show the subgrouping within.

I'd be interested in any suggestions on how to approach this. My coding skills are poor, but I know enough to do some clunky editing on existing code.

Thanks in advance for looking.
A

 

[arnelgp]

see Query1 for totaling the measurement.
see the final query, Query2.

 

[arpod]

Thanks @arnelgp ... that would definitely work if not for the part in the second main paragraph in the OP. The underlying intent isn't to get a percentage of the subtotal - instead it is a calculation that needs to happen in Excel as the actual values are a Monte Carlo calculation rather than a percentage. Apologies for the confusion.

A

 

[arnelgp]

then show show us the calculation of monte carlo.

 

[GPGeorge]

Thanks @arnelgp ... that would definitely work if not for the part in the second main paragraph in the OP. The underlying intent isn't to get a percentage of the subtotal - instead it is a calculation that needs to happen in Excel as the actual values are a Monte Carlo calculation rather than a percentage. Apologies for the confusion.

A

Sometimes what is clear to us in the context where we are working is entirely invisible to others.

When that happens, the best way to get an appropriate response to a question is to provide as much detail about the entire context as possible.

As Arnel pointed out, if you want to use " a monte carlo" calculation, part of the context needed is an explanation of what that is and how it is to be used.

 

[arpod]

Fair enough .. I will try to add some detail. Basically the calculation in Excel is a Monte Carlo approach where 20 * 100,000 cells are calculated via formula (using a Beta distribution) for each iteration. The resultant summary calculations provide is "output" value required. (A Monte Carlo simulation being the process of estimating the likelihood of an outcome by looking at the results of a large number of simulated situations).

Hence my understanding that this calculation isn't replicable in Access, nor by calling an Excel function from within Excel.

So I am looking for any insight into a process that can facilitate "moving" the values to Excel and the results back to access.

A

 

[GPGeorge]

Export and Import are basic automation processes.

Therefore, if the requirement is to move the raw data into Excel, where you can further manipulate it, and then to move that manipulated data back into Access, you can do so by creating a template Excel file with the structure you need and export the data into a "Data Landing" worksheet in that Excel file. Once it's there, do whatever you need to do to it and write it into a "Data Export" worksheet from which Access can retrieve it. As long as the outputs and inputs are in tabular format, it's pretty straightforward both directions. That kind of thing is Arnel's forte.

 

[arpod]

Thanks @GPGeorge. That's the crux of what I'm trying to do.

 

[gemma-the-husky]

I have to say I can't believe there is any process Excel can do that can't also be done in Access.
There may be functions that are built into Excel that would need programming in Access. It might be more efficient to do that rather than incorporate excel, but I don't know. It might be too hard to set up the function in Access.

I also imagine you need to be perfectly sure that access or excel "random" functions are acceptable for whatever you are trying to model.
You may well also need a repeatable random sequence while you develop your solution, before switching to a genuine non-repeatable random sequence.

 

[arpod]

Hi @gemma-the-husky ... the Excel side uses an add-in I 'inherited' - SIPmath Modeller Tools.

As far as I can see what it creates is native Excel (i.e. doesn't need the addin to share etc) - but I suspect the easiest approach would be to have a process to copy the access query (or temp table) values to Excel, then copying the output back to Access. And including a way to iterate by subgroup.

A

 

[gemma-the-husky]

It says there's a free version, but I couldn't see how to get the free version. Expensive to just have a look.

 

[arpod]

There's a free version (you have to provide details though - they don't seem to spam though). Pic attached - it won't let me include a URL.

 

[arnelgp]

I have to say I can't believe there is any process Excel can do that can't also be done in Access.

there is a Free Statistic VBA Library here.
i just don't know if the Beta() function is the same Beta Distribution?
ALGLIB Free Edition

 

[arpod]

I think it is in this case ... but the issue I suspect is to be able to generate what is essentially a matrix of up to 200,000 cells based on this function (and a "seed" value) for up to 20 measurements near instantaneously. If I can achieve it using what's already in place, but taking out the manual side, then it's a win!

 

[arnelgp]

able to generate what is essentially a matrix of up to 200,000 cells based on this function

are you talking about AlgLib?
you can create a VBA that will dump those 200,000 cells (records) in a table.
until we see the monte carlo code you have there is no telling if access (alone) can do it.

 

[gemma-the-husky]

there is a Free Statistic VBA Library here.
i just don't know if the Beta() function is the same Beta Distribution?
ALGLIB Free Edition

@arnelgp That link doesn't show a VBA version?

 

[arnelgp]

near the "bottom" of this page:
ALGLIB Free Edition

 

[gemma-the-husky]

Well that's complicated. All open source basic from the look of it.

 

[arpod]

If there's a way I can achieve it by transferring the data between the two apps that's probably preferable, at least in the short term. Trying to move what's in Excel currently into Access isn't a wheel I'm overly keen to recreate.

 

[Isaac]

@arpod
Just use automation such as DoCmd.TransferSpreadsheet or (my preference: excel vba's Range.CopyFromRecordset to dump the data wherever spot it needs to be fed into your Excel calculator.

You will need to make sure your Excel calculator is set up to look at a particular sheet, maybe a sheet named RawData ... and double check the ranges it looks for if needed to be dynamic ... and then you can use Excel Automation from Access VBA.

Declare a variable as Object which is set as createobject("excel.application")
Declare a variable as Object which is set as the excel app Workbooks.open(path) method
Declare an object variable which is set as the Workbook.Worksheets("name of worksheet")
..etc

You can also code your excel tool with a macro that does the calculation, if it's not inherently volatile to the excel formula setup, and use the excel application object's Application.Run "macroname" to execute it, then read the range value from wherever the final answer is to bring back to Access.

The possibilities are several, just need to learn if not already known how to open the workbook, dump the data (Range.CopyFromRecordset), execute the calculator and/or read the resulting value.