1-variable Data Table w/ dependency on MS Query fails to update correctly

[trippknightly]

I have an Excel financial model whose bottom line values are dependent
on results pulled from a simple MS Query (3 of them, actually, and
together they take about 10 seconds +/- to run).

I'm trying to run a simple data table to see how those results vary by
changing a single assumption.

In my data table, I have a single assumption (Fed Funds Rate) that
should affect the results of the query. This assumption itself is not
a parameter in the queries, however another variable calculated in the
model (that changes w/ FFR) is passed as a parameter.

The problem I'm having?

The query results are NOT changed in the various input variable (Fed
Funds Rate) scenarios I'm trying to depict - the results are held
constant and in so doing the data table is flat out wrong.

I've also confirmed 3 "likely suspects" are set up as they should be:

- Enable automatic refresh is activated on spreadsheet open when
prompted

- The parameter properties are set to "Refresh automatically when cell
value changes"

- Background refresh is NOT enabled in the query data range properties
(this actually doesn't make a difference checked or not)

Anybody have any guidance? Is it just not possible to use these 2
things (MS Query and data sensitivity tables) together?

I suppose I could cook up a macro to do this as a workaround; good
thing I don't have to do it in volume.

 

[gocush]

From your description, it sounds like you are trying to do 2 things:

1. Have an automatic update of you data (with the FFR changing and updating
the data thru calculated formulas).
2. Run scenarios with the FFR being changed manually so you can compare the
results of the bottom line.

In #1 I think you would have to have the FFR inside the db and the db must
be updated thru external means. Then the MS Query would query the db and
download the recordset to excel on an automatic basis as set in the
properties as you described.

If you want to run scenarios, I think you would want to have the FFR
variable in excel, with a macro to run thru the data table using your
variable as a factor on the data.

 

[trippknightly]

Thanks.

Wrong on 1, right on 2. No updates of data based on Excel data. Excel
model determines parameters to feed the query, whose results further
feed subsequent steps in the Excel model.

The real issue at hand is (apparently) data tables calculated w/o first
waiting for dependent query to generate new results that feed the
model. I've noticed some discussion of a similar issue w/ pivot
tables, as well as 1 MS KB article which doesn't quite describe my
issue but related: http://support.microsoft.com/kb/236990.

 

[gocush]

Excel
model determines parameters to feed the query, whose results further
feed subsequent steps in the Excel model.
*******************
This sound like you may be approaching a circular loop. If this is all in
one step, the code may be divided into steps with separate command buttons so
that excel will "wait" for the results of the query before moving on to the
last step.

 

[trippknightly]

Well, not really. The sequencing's OK, so no circularity.

Another thing I've discovered is even if I try to do a data table using
VBA, I still run into the same problem. There's no awareness that a
query needs to finish before proceeding through the VB. This again
despite unchecking the background refresh property for the data area.