Referencing Database tables with excel functions

[J. I. Howell]

I have a number of large data tables in a SQL database that I need to
access from Excel. Currently I move the tables to Excel and use each
column as a range in a SumProduct formula, creating a single cell
result for each column.

I would like to expand the number of sumproduct functions to the point
that moving all the SQL tables into Excel is impractical. Can someone
tell me if and how I could directly reference the SQL data without
brining it into EXCEL. Esentially I wish to change
sumproduct(a1:a400,d1:d400) into an expression like
sumproduct(a1:a400, reference to external datasource).

When I am done with this operation I will have between 3500 and 10,000
of the sumproduct functions, hence the reason to avoid brining all the
database information into Excel.

The sumproduct functions are used in a linear optimization (a1:a400
are dynamic or adjustable for the optimization nuts in the group), so
the cells have to be dynamically linked to the database with formulas
that excel recognizes (I cannot simply solve the problem using VBA to
populate the sumproduct cells in excel).

Any and all ideas are encouraged.

Thanks,

John

 

[Harlan Grove]

...
...

I would like to expand the number of sumproduct functions to the point
that moving all the SQL tables into Excel is impractical. Can someone
tell me if and how I could directly reference the SQL data without
brining it into EXCEL. Esentially I wish to change
sumproduct(a1:a400,d1:d400) into an expression like
sumproduct(a1:a400, reference to external datasource).

...

To be honest, if A1:A400 doesn't change during these runs, or could be generated
by SQL-like queries, you may be better off creating a new table in your
datasource containing these values, then doing your SUMPRODUCT calcs in the DBMS
using something like

Select SUM(Tbl1.Fld1 * Tbl2.Fld2) As SumProd From Tbl1, Tbl2;

which you could do using SQL.REQUEST. Pulling data back & forth across an ODBC
connection is likely to slow recalc to a crawl or maybe glacial pace.

 

[J. I. Howell]

...
..
..

To be honest, if A1:A400 doesn't change during these runs, or could be generated
by SQL-like queries, you may be better off creating a new table in your
datasource containing these values, then doing your SUMPRODUCT calcs in the DBMS
using something like

Select SUM(Tbl1.Fld1 * Tbl2.Fld2) As SumProd From Tbl1, Tbl2;

which you could do using SQL.REQUEST. Pulling data back & forth across an ODBC
connection is likely to slow recalc to a crawl or maybe glacial pace.

Harlan,

Thanks for the quick reply. Unfortunately, A1:A400 do change as part
of the optimization, while all the other values ( in the database
tables )remain constant. The sumproduct needs to be computed in Excel
for the optimzation to function.

Thanks,

 

[Harlan Grove]

...
...

. . . Unfortunately, A1:A400 do change as part
of the optimization, while all the other values ( in the database
tables )remain constant. The sumproduct needs to be computed in Excel
for the optimzation to function.

For best performance, the SUMPRODUCTs should be calculated in the application
that's the source of the more frequently changing values. That appears to be
Excel, in which case best performance would be achieved by bringing external
data into an Excel worksheet range, and using that range in SUMPRODUCT calls.

 

[Gerrit-Jan Linker]

Just wanted to say that my product SQL*XL could help if one 1 value is
required from the database. SQL*XL has a worksheet formula to retrieve
1 data item back from the database. You can then use that value in
your other formulae. Example:

=QueryValue("select count(*) from all_objects")

I will think about adding support for the sumproduct function and
equivalent functions.

You find out more about SQL*XL at www.oraxcel.com

Best regards,

Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com

 

[J. I. Howell]

Just wanted to say that my product SQL*XL could help if one 1 value is
required from the database. SQL*XL has a worksheet formula to retrieve
1 data item back from the database. You can then use that value in
your other formulae. Example:

=QueryValue("select count(*) from all_objects")

I will think about adding support for the sumproduct function and
equivalent functions.

You find out more about SQL*XL at www.oraxcel.com

Best regards,

Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com

Gerrit-Jan

Thanks for the input. The reason I am trying to go to a DB to store
the data is because I have such a large volume of data that storing it
in Excel is not too realistic. I appreciate your suggestions and will
keep your ideas in mind as I continue to explore the migration to a
database.

Respectfully,

John I Howell

 

[Gerrit-Jan Linker]

Hi John,

Well, you certainly set me thinking about supporting these functions
like sumproduct. I will have a look what I can do. Nevertheless, if
you want a lot of these calculations to be done it is bound to be
slugglish. Each time you need to go to the database and execute
something that will hit performance; logically.

If you need to do an awful lot of these calculations, perhaps you need
to try it the other way around. You could load your Excel data into
the database and solve it using SQL perhaps. SQL*XL can help again to
put it into the database for you if you like.

Best regards, Gerrit-Jan Linker
www.oraxcel.com
Home of SQL*XL : linking Excel to databases.

 

[Harlan Grove]

Well, you certainly set me thinking about supporting these functions
like sumproduct. . . .

What do you mean? SQL already supports the exact equivalent of Excel's
SUMPRODUCT. If a and b are fields/columns in table/relation tbl,

SELECT sum(a * b) as ab FROM tbl;

gives the sumproduct of the values in a and b.

 

[Gerrit-Jan Linker]

Hi,

Sorry if I was unclear. The original post was about doing a sumproduct
with data in Excel to data in a database. The problem was that putting
all the database data into Excel was too cumbersome. Therefore I
suggested to put the Excel information in the database so SQL can be
used to do it. You are right that you would just use Sum(a*b)

Best regards,

Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com
Home of SQL*XL - bridge between Excel and your databases
Home of LITlib Excel Power Users Pack - worksheet functions.