C# and Excel Interface

[ADDA]

Hi all

I am trying to write a betting strategy, but I don't have very much VBA experience. I have more experience with C#.

* Is there a way to interface C# with excel in real time?
* Would this be done with a COM interface, and could someone direct us to more information about this?
* How difficult is it to set this up?
* Would this type of interface limit functionality?
* Would using this sort of interface introduce any latency issues?

Thanks so much for any help.
ADDA

[osknows]

ADDA,

All of what you have asked is possible and there are many excel add-ins written in C# that are available. Personally I haven't tried it myself but these links should give a few pointers

http://support.microsoft.com/kb/302084
http://support.microsoft.com/kb/302901

Os

[ADDA]

Hi

Thanks so much for the reply and help so far.

I noticed that you can use a COM interface directly from C# to BA. Is it better to go from C# to EXCEL OR to set up a direct link from C# to BA?

Do you know how to set up the COM interface in C#? I noticed there is a config file on the Gruss BA help file, but I need to figure out how to connect C# to the BA on port (8000) and I'm not sure if the config file is needed or not. Any help would be greatly appreciated.

Thanks!
ADDA

[jokerjoe]

If you don't need Excel I'd say it's better to go direct, why have something running if you don't need it. I don't have the files in front of me but I managed to connect in VB2008 using the spreadsheet example as a guide.

[GaryRussell]

I have created a working example in C#, download it from the following link. You will need to change the reference to the dll as it references my development dll. The example demonstrates fetching the first horse race, hooking up the pricesUpdated event and reading the prices.

http://www.gruss-software.co.uk/Betting ... Test_C.zip

[ADDA]

Hey Gary

Thanks very much for the example - I also found it last night on another thread on the forum.

I am trying to get a race description string from the COM interface like you have in Excel in A1.
Philly (US) 9th May - 21:22 R8 1m Allw

The closest I can get is the following but it seems to give only the track name and the start time (which seems to be the local time of the event).

Code: Select all

string description = evnt.eventName + "(" + evnt.startTime + ")";

Can anyone assist?

@jokerjoe - thanks for your advice, you're absolutely right! I am just trying to work out if there is any disadvantage to using the COM interface rather than Excel.

Thanks for the excellent help.
ADDA

[laffo16]

hi, sorry 2 bump this old thread. does anybody have a copy of the c# com example posted above, the link is dead. any other simple c# example would be fine also. cheers! its been a while :]

[GaryRussell]

I have fixed the dead link, you should be able to download it now.

[laffo16]

if any one can give me directions on how to compile this in visual studio 2010 would be much appreciated. i'm not sure if i'm changing the reference correctly. i'm currently going "Project > Add Reference > Browse" and selecting "bin\Debug\BettingAssistantCom.dll", is this correct? sorry im still very new to this language.

also when i add the project, a wizard appears out of thin air ^^ and asks me to convert the script to support 2010 (their is no other choice but to convert), their are no errors in conversion but it warns me about using possible incorrect version of .net framework.

the error i get is:
ba = new ComClass(); --- RemotingException was unhandled.

"The method 'set_updatePricesClient' was not found on the interface/type 'IRemoteLib.IRemoteLib, IRemoteLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=db4e9e697e27045e'."

[laffo16]

can anyone give me instructions on how to compile the above code in VS 2010 pls!

[laffo16]

i get this when i go Project > Add Reference

[laffo16]

think im getting some were, but is this function missing?

pricesUpdatedDelegate()

Error 1 The type or namespace name 'pricesUpdatedDelegate' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\stone\Desktop\BA_COM_Test_C\BA_COM_Test_C\BA_COM_Test_C\Form1.cs 45 49 BA_COM_Test_C

[laffo16]

just some quick notes on how i have got this working, pls note im a total rookie at c#.

firstly, in Betting Assistant, make sure COM support enabled if you havent already: options > preferences > com server > enable

In Visual Studio 2010, after running "BA_COM_Test_C.csproj" and converting the script to a newer version. In the "Solution Explorer" at the top right, you'll see "[+] References". Expand it and remove both BettingAssistantCom & IRemoteLib.

Then right click "[+] References" > "Add Reference" and re-add them, i used the files located it gruss's default dir "c:\program files\betting assistant\" which worked for me. (BettingAssistantCom.dll & IRemoteLib.dll)

right click "Form1.cs" > "View Code"

i have a feel pricesUpdatedDelegate() has been left out by mistake, but it could be somethng entirely different, still, i removed any reference to the this function which leaves me with the code below. just copy and paste it over your Form1.cs code and it should work.

Code: Select all

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using BettingAssistantCom.Application;

namespace BA_COM_Test_C
{
   
    public partial class Form1 : Form
    {
        private ComClass ba;
       
        public Form1()
        {
            InitializeComponent();
            ba = new ComClass();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            BettingAssistantCom.Application.Balance bal;
            Object[] sports;
            Object[] events;
            double diff;
            String result;
            bal = (Balance)ba.getBalance(1);
            AvailableBalance.Text = "Available balance:" + bal.availBalance.ToString();
            Balance.Text = "Balance:" + bal.balance.ToString();
            Exposure.Text = "Exposure:" + bal.exposure.ToString();
            sports = ba.getSports();
            foreach (BfSport sport in sports) {
                if (sport.sport == "Horse Racing - Todays Card") {
                events = ba.getEvents(sport.sportId);
                foreach (BfEvent evnt in events) {
                    System.TimeSpan TS = new System.TimeSpan(evnt.startTime.Ticks - DateTime.Now.Ticks);
                    diff = TS.TotalSeconds;
                    if (diff >= 0 && evnt.eventName.IndexOf("(") == -1) {
                        result = ba.openMarket(evnt.eventId, evnt.exchangeId);
                        eventName.Text = evnt.eventName + "(" + evnt.startTime + ")";
                        pricesUpdated();
                        break;
                    }
                }
                }
            }
            ba.refreshRate = 1;
        }

        public void pricesUpdated()
        {
            Object[] prices;
            listBox1.Items.Clear();
            prices = ba.getPrices();
            foreach (Price priceItem in prices)
            {
                listBox1.Items.Add(priceItem.selection.PadRight(50,' ') + "\t" + priceItem.backOdds1);
            }
        }
    }
}