Re: Excel to Linux DDE solution?

From: prg (rdgentry1_at_cablelynx.com)
Date: 03/27/05

  • Next message: Michael Heiming: "Re: How do I run remote X application?" 
    Date: 26 Mar 2005 21:30:33 -0800

    Kiwi Trader Guy wrote:
    > > 1) Write a Windows client i.e. a library, dll, .NET component,
    whatever
    > > you're most comfortable with.
    > >
    > > 2) Write a server to run on the Unix/Linux system. Language of your
    choice.
    > >
    > > 3) The client and server communicate via tcp-ip. The Excel/VB app
    sends
    > > commands to the client, the client forwards them to the server, the
    server
    > > forks a process on the Unix/Linux box and executes the command.
    >
    > Thanks,
    >
    > This is the way to do it yes. I've noticed a Shell command from
    within Excel
    > so could use Windows SSH. Plus I could use a Linux based Database
    with
    > triggers invoked using ADO I guess. My trading systems are based
    around
    > Linux, but I was looking to offload some processing and crunch some
    numbers on
    > the server.
    >
    > Would be nice if there was a set of libraries to assist with this.

    Now you're clarifying ... a bit ... ;-)

    Is this along the lines of what you're seeking?

    You want a database on one machine -- perhaps Linux.

    You have Excel running on Windows and want to _connect_ to the database
    (via ODBC or ADO) and run queries that will fetch/manipulate data on
    the database and return it to Excel.

    You'll use Excel to further manipualte the returned data -- either more
    calculations and/or formatting.

    You would not _normally_ shell into the remote db box, but it would be
    nice if you could, for whatever reason, to get at or to manipulate the
    data.

    Depending on just what you need to do, you would usually use stored
    procedures or UDFs on the db to provide back end calculations. You
    _might_ need to invoke an app on the back end to provide custom
    procedures not readily available via db stored procs. This too can
    usually be accomplished via the facilities of the db, but not always in
    a "reasonable" fashion, ie., it might be easier just to run an app
    directly from a remote shell (or web browser).

    Depending on your familiarity/comfort with Linux and databases you have
    several choices:

    -- MySQL will first come to mind as it's all the rage. It _is_ fast
    for _some_ applications and has tools available to set it up and
    maintain it. It does _not_ provide what I consider to be essential
    data integrity features needed by a capable _relational_ SQL db.
    Perhaps your data will not require extensive use of multiple, related
    tables and foreign keys. Runs well on both Linux and Windows, but the
    Windows license may be prohibitive for you. Stored procs/triggers?

    -- PostgeSQL has acquired renewed, capable life in the last few years
    and _does_ support relational integrity features as well as stored
    procs and supports multiple procedure languages. For your particular
    needs it may be the way to go for this reason. It's new to Windows and
    I'm not sure of all its connection options. My limited experience has
    been good, but it may prove overwhelming at first.

    -- Firebird has robust data integrity features, stored procs (via its
    own stored proc language), and runs quite well on Windows and Linux.
    The Linux toolset is slim, especially compared to what's available on
    Windows. It supports ODBC and ADO connections. It's lightweight and
    one of the easiest dbs to maintain that I've used. I try to use it
    (prefer it, more familiar with it) whenever I can as it's
    multi-platform support is good both for development and deployment.
    Transaction support (OLTP) unsurpassed, IMO.

    You'll have to be _explicit_ about the kinds of libraries you're
    seeking as I doubt anyone around here has done extensive
    financial/stock market data crunching in a commercial setting. Seems
    I've read someone claiming some relevant background, but don't remember
    who.

    I use Python for some (lightweight) math work but it has several very
    capable (low level?) libraries available for it. It's used extensively
    in bioinformatics work. You can probably find similar libraries on
    Perl.

    Other than spreadsheets, the only other financial package I've _played_
    with runs via the R language, Rmetrics. R runs on Windows as well as
    Linux and has several other packages available that might interest you,
    viz., working with web servers.

    Which brings up the last consideration. If you have any possible need
    to provide a client front end other than Excel, eg., custom application
    on the client or web based access via a browser, you'll want to
    consider the development tools available and how well the db works with
    things like PHP or other server scripting environments (Zope, Perl,
    Python).

    For Excel clients, just set up and try some simple tests, via localhost
    db servers and ODBC/ADO, should tell you what's worth investigating
    further.

    If you go the route that chub suggested, ie., provide your own
    client/server apps/libraries, connection management, and data tranfer
    you may have quite a bit of work ahead unless you keep it pretty simple
    and use available networking libraries with Python or Perl. Many
    people are (over?)using web server/interfaces to avoid this detail work
    by relying on the infrastructure provided by web (application) servers.
     A hammer to swat a fly? Price of flexibility?

    some links to R libraries:
    http://www.r-project.org/
    http://www.itp.phys.ethz.ch/econophysics/R/
    http://lib.stat.cmu.edu/S/
    http://franklin.imgen.bcm.tmc.edu/R.web.servers/
    http://research.warnes.net/projects/rzope

    db links:
    http://dev.mysql.com/downloads/mysql/4.1.html
    http://www.postgresql.org/
    http://www.ibphoenix.com/main.nfs?a=ibphoenix&s=1111892103:262350&page=ibp_download
    http://www.ibphoenix.com/main.nfs?a=ibphoenix&s=1111892103:262350&page=ibp_contrib_download

    hth,
    prg

  • Next message: Michael Heiming: "Re: How do I run remote X application?"