Excel Solver / Quadratics in SQL Server

Question

Excel Solver / Quadratics in SQL Server

steve 19850

SSC Veteran

Points: 221
More actions
December 8, 2010 at 4:16 am

#243612

Hi,
Bit of a long shot this one. I have a requirement to replicate some of the Excel Solver functionality within SQL Server. Ideally I wanted a purely TSQL function, but this looks unlikely now, so I'm also looking at CLR options.
What I need to do is analyse some financial fund data (36 price values) against somewhere between 2-5 benchmarks (also 36 price values each) and determine the sensitivities (weights) of the benchmarks that best follow the fund. In other words find a set of benchmark weights which minimizes the tracking error between the resulting benchmark and the fund. It's explained perfectly here.
This is done with a few clicks in Excel, which is why it's so frustrating that I can't find a SQL Server contained solution. However, I do appreciate that it's a quadratic problem, so may not be so easily portable to SQL Server. I've have looked at the Frontline Solver (http://www.solver.com) and building a C# Dll, but I'd rather avoid that if possible.
I'm not expecting someone to simply give me the solution, but anyone got any thoughts or experience that might assist?
Many thanks,
Stephen

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

It looks like this one has been sitting all day. Working with a quadradic formula is way outside of my experience, but you may consider looking into SSAS as a possible solution. Somewhere between named calculations and mining structures may get you where you want to be. This would be a SQL Server based solution, but not T-SQL.

I'm not sure if this is even doable, but due to the lack of response, I thought I'd at least through this out there.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

if ur able to pull out the actual function from excel then i'm sure someone can duplicater it in tsql.

steve 19850 SSC Veteran Points: 221 More actions · Answer 3

Hi,

Thanks both for your replies. I will look at SSAS for sure, although I'd hoped to make is a pure SSIS/TSQL solution, but the more I research the less likely that looks.

Unfortunately, there is no Excel 'function'. Because it's a 'Solver' there's no specific cell functions only recourse to the cells for data to pass to the quadratics...

I'll keep looking 😀

Cheers,

Steve