Post reply

Excel Solver / Quadratics in SQL Server

  • 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

  • December 8, 2010 at 10:07 pm

    #1260420

    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

  • December 9, 2010 at 9:33 am

    #1260633

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

  • December 9, 2010 at 9:38 am

    #1260637

    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

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

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