New release: Excel sample trading app

28 posts / 0 new
Last post
Chris
New release: Excel sample trading app

Hi everyone,

I’m excited to announce we’ve now released our Excel sample trading application.

You can download it here: Sample Apps

This is an example spreadsheet for connection to both the IG REST and Streaming APIs. Using the sample, you can:

  • Log in to your demo or live account
  • Retrieve positions, watchlists and markets
  • Retrieve historical prices
  • Stream live prices to individual cells
  • Place a basic trade

We’ve also included the full source code and an installable Excel Real Time Data (RTD) Streaming component for pulling in live prices into Excel, which will make your lives much easier when it comes to building your own Excel trading systems.

To get started

  1. Download the sample zip file from the Sample Apps page
  2. Unzip the file and run IG API Excel Stream Library.msi. This will install the Excel sample and the RTD Streaming component
  3. Open the Excel spreadsheet from Start > All Programs > IG API > IG API Excel Sample

As always, we’re interested in hearing your feedback, so let me know in this thread.

Chris

mgapi
Excel release

Hi Chris,
I downloaded this today. Frist issue was error message which said code must be updated for use on 64-bit systems. I added "PtrSafe" to
"Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
(pDst As Any, pSrc As Any, ByVal ByteLen As Long)

which seems to get the macro to work. (please advise if this is the what I should have done)

Second issue is logging in. I am getting a "Compile error: Type mismatch" in the following sub macro,
Public Sub Append(ByRef sThis As String)
With error message saying this line is causing the issue
CopyMemory ByVal UnsignedAdd(StrPtr(m_sString), m_iPos), ByVal StrPtr(sThis), lLen

User name & password I have typed in. The account is a spread betting account. API Key I have copied from an IG session, however it is over a month old.

Any thoughts as to what I am doing wrong?
Many thanks, Mike

Moncef
Excel sample app

Hi Chris,

I downloaded the latest Excel app released, and the "Application.WorksheetFunction.RTD(IG_API_RTD_PROC_ID, "", "connect", m_lsHost, m_currentAccountId, password)" does not seems to pass as VBA error displayed is "Unable to get the RTD property of the worksheet function class".
I tried to check on the references in VBA, make sure that the "IGApiExcelRTD" is loaded, HTML library as well, but that does not seems to be enought...
Do you know where it might does come from?

Thanks for your help,
Moncef

Chris
Excel sample app

Hi Moncef

What version of Windows and Excel (Help > About) do you have, and are they 32-bit or 64-bit versions?

Chris

Moncef
Excel sample app

Hi Chris,

I use Excel 2007 (12.0.6683.5002 SP3 MSO), my computer under Windows 7 (6.1.7601 SP1 build 7601) is 64bits, but my Office 2007 pack is 32bit.

Thanks for your help,
Moncef

lysan
prb Excel

Hello i have this message on new release Excel "Unable to get the RTD property of the worksheet function class. Office2013 and windows7 64 bits. Thanks for your replies.

Chris
Excel sample app

Hi Moncef and lysan

We’ve developed and tested the app around Office 2010 32 bit, and it appears the issue is related to 64 bit versions. We’re looking into this further, but we’d recommend trying with 32 bit if you can.

Chris

Chris
Excel sample app - 64 bit

Hi all

We think we've gotten to the bottom of the problems with 64-bit Excel compatibility. Below I've linked both 32-bit and a pre-release 64-bit version.

I'd really appreciate if you could give the 64-bit version a go and provide some feedback!

Let me know how you get on.

Chris

64-bit: https://labs.ig.com/downloads/api-sample-excel-1.0.3-64bit.zip
32-bit: https://labs.ig.com/downloads/api-sample-excel-1.0.3-32bit.zip

lysan
new release Excel 64 bit

Hello and thank you for new release 64 bit but :

the same message appear : Error '1004', Impossible de lire la propriété RTD de la classe WorksheetFunction.

The debbuger Visual Basic are stopped on line :

>>>If restClient.streamingAuthentication() Then

inf function : >>> Private Sub loginButton_Click()

Thank you for your help.

Chris
new release Excel 64 bit

Hi lysan

Which version of Excel are you using (and is it 64-bit), and what version of Windows (again is it 64 bit)?

Chris

Moncef
new release Excel 64 bit

Hi chris,

Same issue as other users on Excel, the RTD function call in the StreamingAuthentification is not working. I'm on Excel 2010 64bit, Win7 64bit, and .Net framework is 4.5.1.

Moncef

Nico
new release Excel 32 bit

Hello All,

Actually I have the same issue > Error '1004', Impossible de lire la propriété RTD de la classe WorksheetFunction
with the 32 bit version as well.

For information I have Excel 2007 (which is 32 bits).

It seems that samples have been tested with Excel 2010.

Does anyone manage to start the sample with Excel 2007 ?

Because an upgrade on Office 2010 is not a costless option ;-)

Many thanks for your help and advice.

Chris
new release Excel 32 bit

Hi Nico,

Just to confirm, you're using the 32-bit Excel API sample yes? And what version of Windows are you using?

Chris

Nico
new release Excel 32 bit

Hi Chris,

Yes, I use the 32-bit Excel sample.
And I have Windows 7 - 32 bits.

Chris
new release Excel 32 bit

Hi Nico

We think some of these issues might relate to earlier versions of .NET framework. Are you using .NET 4.0 or an earlier version (e.g. 3.5)?

If you're using an earlier version, it might be worth updating to 4.0 to see if this helps. Let me know if it does!

Chris

Nico
new release Excel 32 bit

Hello Chris,

Thanks for your reply.

Actually I'm already on .NET 4.5.
Then I'm not sure this can be the solution.

Chris
new release Excel 32 bit

Hey Nico,

Due to the issues we were having with version 1.0.4, we have rolled back to the previous version. Could you uninstall your current version and reinstall via the labs site.

Let me know how you get on.

Thanks!
Chris

Nico
new release Excel 32 bit

Hello chris,

After some tests, it appears that I still have the same issue described above whatever the sample version (1.0.3, 1.0.4 or 1.0.5).

Any clue ? do you know someone who manage to start the sample with Excel 2007 ?

Thanks

offthelip
I have managed to get the the

I have managed to get the the sample APP working , I am using EXCEL 2007 on a 64 bit machine on Win 7 pro.
I have currently been modifying it to my own requirements and I can login , download historic data, get the realtime data working and then open trades. I am still trying to get closing trades to work. The API doesn't seem to "Close" Trades. I currently need to log in to the web site to close trades.

Nico
Excel API

Hello offthelip,

Thanks for your feedback.
Then the difference is I use Win 7 32 bit... may be a clue.

Did you manage to login at the first try ? or did you modify some options in Excel/windows ? In my case, I have disabled all connections securities in Excel ...

On my side, I will try to find another machine with a different set up to see if it works better.
Thanks.

Chris
Excel
Hi Offthelip, Would you mind sharing what you did to get your scenario to work? Chris
offthelip
to get EXCEL api to work

Hi Chris,
It is a bit difficult for me say exactly what I did to get the sample API to work , because I had started writing my own VBA application from scratch before you released the Sample API, and I had succeeded in logging in and downloading historic prices and getting market information with my own code. What I do remember is that I had to sort out the VBA references to get the API work, in particular you need to tick the boxes for:
Microsoft Forms 2.0 Object library
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects 6.1 Library
Apart from that I think all I did was download the application and follow the instructions.
I hope this helps.

offthelip
EXCEL API does work

I have just downloaded the EXCEL 32 bit API to a ASUS ee netbook with an ATOM dual core CPU running windows 7 starter and EXCEL 2007
It worked without any problems at all , I didn't even need to set the references in the VBA, they were already set.
Having done this I did find the netbook was too slow to run my application and the API, so I need to get a faster machine. So I have got the 63 bit and the 32 bit API samples working.

Nico
Thanks offthelip

Thanks offthelip

Very weird as it seems I have the same configuration.
I have checked that all references you mentionned are selected correctly.

I don't know what to test anymore. I may try to develop something from scratch like you.

Just for info, is that normal that no additional add-in is selected in the Add-in manager?

Thanks anyway for your feedback & tests.

offthelip
Addins

Hi Nico,
I looked at what addins were actually active on my working desktop system, I have got analysis toolpack, one addin I wrote myself and what appears to be 20 copies of ig.api.excel.rtd.igapirtdserver/ mscoree.dll
I didn't do anything to get the addins installed apart from run the Ig installer.
I have occasionally had the ""Unable to get the RTD property of the worksheet function " error, but I have only had this when I was running on a laptop with a wireless connection. My desktop has an ethernet cable to the router and my broadband speed is pretty good ( only 1 mile from the exchange) So do wonder whether the error is due to a latency issue with the set up of the streaming service.
The only other difference is that I have modified the code so that I am not using any of the buttons on the original "main" sheet. I login by running a stand alone macro, but this shouldn't make any difference

richard
excel demo

Chris (and others) I managed to get the Excel demo working (only with 32 bit version on my 64 bit machine) but it does not show any data or allow any dealing on UK individual shares for spread betting.

It does however show indices but my watchlists for UK shares return no data and I cannot search for UK shares.

Is it possible for you to update/fix the Excel example to allow betting on individual UK shares please?

Thanks in advance

rob162a
Excel sample trading app

Hi Chris,
I downloaded the app (windows7, excel2007, 32bit) and using the api key generated in 'my account' on the web trading platform I logged in successfully and retrieved positions and watchlists.
As there is no logout button I simply closed the excel window when finished.
However, on reopening and attempting to login, authentication failed with an api key invalid error.
The next day however, I was able to login without problem.
I am not too familiar with the streaming data and connection programming interfaces but I would like to modify your excel sample to include my own formulae, functions etc. so would appreciate any guidance on this connection issue.
I'm sure I am missing something obvious!!
Regards,
Rob

koub
Hi everyone

Read this thread: https://labs.ig.com/node/132
and confirm to Chris that all is OK after update the code.

Log in or register to post comments