Hi all
We’ve recently been putting together a new type of Excel sample app that we wanted to share on the Community as a beta test, before adding it to our Sample Apps page.
This app is aimed at those who aren't necessarily programmers, but want to easily insert API functions into Excel. It’s in the form of a spreadsheet that includes a set of Excel functions, macros and built-in menus to help you access your IG data using IG’s API. There is also an automated trading sheet that allows you to define triggers for automated trades. There are other instructions and hints throughout the app as well.
If you’re interested in taking part, let me know in this thread, and I’ll get in touch via email with the sheet and instructions on how to install it.
We’re very keen to get feedback on this so we’ll also be following up to get your thoughts. We’re particularly interested on your ideas about automated trading, and how we can make it easier for you in this and other sample apps.
It is a beta, so the sample app is provided ‘as is’ and at this stage will only work with your demo account. Get in touch if you'd like to take part.
Chris
Hello Chris,
Following your proposal, I am very interested in receiving and testing your beta version of Excel document.
I can give you my feedback after testing.
Thanks in advance.
JF
Hi Chris,
I'd like to take part in the beta test please.
CG
Hi Chris,
Please add me to the list of willing beta testers
Offthelip
Hi all,
Thanks for letting us know of your interest. We'll be getting in touch with you in the next couple of days to begin the testing.
Chris
Hello Chris,
Thank you for sending Excel beta sample.
I have installed Excel sample and after launching the Excel sample (With Excel 2010 french edition), I have an error in "Visual Basic for Applications", and Excel is totally blocked and I am not able to continue the test (I am obliged to kill Excel with task manager).
The error is the following :
Compilation error in "Visual Basic for Applications" in "Sheet 13 (Start)" at line :
"Set MenuItem = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup)"
in the méthod "Public Sub SetupMenus()".
Regards
JF
Hi,
I have been playing with the new beta app and am not sure if you are able to close a binary trade using the IGAPI_ClosePosition function as it doesn't have a limit field, and when I try to close a trade without it, the order is rejected "unable to fill a market order".
I can of course create a new CreatePosition in the opposite direction to achieve the close, but thought I'd mention it in case it was an oversight in the close function.
For binaries,
#1 Can you achieve the same as the manual "force open" with the CreatePosition function, so two offsetting positions are created and not netted?
#2 Is there a function to extract the instrument name from an individual EPIC code? For binaries, the strike is included in the instrumentname which would be useful info, which if if can't be extracted directly as a "strike" field, instrument name would be enough to determine it from the longer string in Excel.
For example, from the EPIC "QB.D.DOW2025UD.01.IP" I would like to be able to retrieve either the full instrument name "Wall Street to be above xx,xxx at 20:30" or the strike xx,xxx directly.
Thank you
Hi Chris, sounds great please could I have a copy many thanks - Mark
I just tried using this new spreadsheet, and I immediately had a problem, when I opened the spreadsheet it came up with "Compile Error Named Argument not Found" with Argumentdescriptions highlighted. I investigated what the problem was ( I am very familiar with VBA) and I discover that the problem is that Microsoft has changed the arguments that the MacroOptions method uses between EXCel 2007 which I am using and 2013.
See:
https://msdn.microsoft.com/en-us/library/office/ff838997.aspx
and
https://msdn.microsoft.com/en-us/library/bb209988%28v=office.12%29.aspx
To get around this I edited every macro in the Help module and deleted the code after ; Category:="IGAPI"
i:e: Category:="IGAPI", argumentDescriptions:=args
becomes: Category:="IGAPI"
I reckon the only effect of this change is on Help such that I won't get an argument description appearing.
The spreadsheet then completed the intialisation.
I haven't tried it yet but it looks useful
I have just started trying the new Excel Beta spreadsheet,
Firstly I needed to fix the EXCEL 2007 problem see my other post
Secondly on login I get the RTD server error, which I got over by setting going into VBA debug mode and setting the next statement to the “Common.log "Lightstreamer connection failure" statement.
Thus streaming wasn’t working.
The first obvious problem was that I get a continuous stream of error messages coming up in the LOG panel on the login sheet, the error is:
(“errorCode”:”error.public-api.exceeded-api-key=allowan) the rest of the text is not visible.
This error is coming up multiple times a second.
Occasionally the error is preceeded by (/markets/IX.D.FTSE.DAILY.IP)
I change the epic which is in B23 of the Markets sheets to IX.D.DAX.DAILY.IP and then the error message changed to (/markets/IX.D.DAX.DAILY.IP)
The market sheet, this is not very easy to use, but it could be made very easy to use by adding some data validation cells and and the match and offset functions.
I tried this to link the data in A33 to B61 with what appear below E33 to F 33
In C32 I added a data validation list pointing at B34 to B61. Then in D32 I put =MATCH(C32,B34:B61,0), in D 33 I put: =OFFSET(A34,D32-1,0) and then in E34 to F43 I changed the reference in the Array formula to D33.
This worked a treat. If the rest of the sheet worked in the same way it would make navigation a lot easier.
I have noticed that frequently the market Navigation data goes blank and returns zeros. This ties up with the exceeded error on the login page. If I wait 30 seconds and then force an update it returns values again. So I think all that is happening with the error is that the sheet is exceeding the maximum number of calls per minute allowed.
Overall this looks like it could be a great improvement on the existing api which is a bit of a pain to use in EXCEL.
I haven’t been able to use the streaming api, so that needs to be fixed.
One other suggestion is that it would be very good if the IGI_API functions where put into a VBA ADDin, rather than just one spreadsheet. This would allow users more flexibility in using the functions in any workbook.
offthelip,
While a solution to the RTD streaming 1004 error is found, you can prevent the streaming error from appearing by unhiding cells on the Login sheet, cell B7 can then be set to FALSE (Streaming enabled) and thus the system doesn't try to enable it.
CG
Embarrassingly, I had installed the 64 bit version, so I uninstalled, reinstalled, now in the correct (x86) directory and all is fine. Thanks to the guys at IG for the heads up! :-)
Hey cgapi,
Glad to hear it's all working now!
Chris
I managed to get the steaming to work. I had tried my demo account in my existing workbook which I had coded myself and the streaming worked OK, so I compared this with the new Beta demo so see the differences. I made the following mods to the Authentication login module:
If restClient.streamingAuthentication() Then
' If restClient.streamingAuthentication(maxPriceRequestsPerSecond) Then
Common.log "Lightstreamer connected"
' Application.WorksheetFunction.RTD "IG.api.excel.RTD.IGApiRTDServer", "", "refresh"
Else
Common.log "Lightstreamer connection failure"
End If
This allowed the streaming to work "sort of". It is severely limited by the max updates per minute of 30 minute, see http://labs.ig.com/faq. So the streaming works for a few seconds then stops. I also suffer from the problem that some streaming feeds update but most of them don't. It seems to vary each time I try as to which are working. Note I am using EXCEL 2007 on Win 7 pro.
Hi Chris,
I'm interested to be add to the list of beta testers.
Deal
Hi Chris,
Thanks for the Excel sample app.
Currently, no problem for me.
How to format "Charts/CUSTOM CONDITIONAL" ?
Thanks
Deal
Hi Deal
Glad it's working. Can you provide some more info on what you mean by formatting Charts/Custom Conditional?
Chris
Hi Chris,
I can't operate the custom conditional.
What syntax to use for this function ?
Deal
Hi
Would love to be included in this beta test. I am not a programmer but a decent excel user so hopefully I am the right sort of guinea pig!
Cheers
Steamerduck
Hi Chris,
Can you send me a beta test. I usualy work with EXCEL 2010 64 bits.
If you have resolved the streaming frozen problem, I'm very interrested.
Pascal
Hi Chris,
Could I also take a look please?
Thank you
Hi Chris,
I'm interested in receiving and test the new Excel document for the automated trading.
Please, could you send me this version?
Thanks in advance
Gianni
Hi Gianni,
Thanks for your interest. This has now been sent to you.
Chris
Hello Chris,
I am very interested in receiving and testing your beta version of Excel document.
I will give you my feedback after testing.
Thanks in advance.
KC
Hi ckc,
Thanks for your interest. This has now been sent to you.
Chris
Hi Chris,
Would it be possible for you to send me the sampe app as well please?
Thank you
Hi Chris,
Would it be possible for you to send me the sampe app as well please?
Thank you
Hi Chris.
I'd also like to beta test the new excel sheet.
Thanks
Hi Chris,
I am very interested in receiving and testing your beta version of Excel tool.
I will give you my feedback after testing.
Thanks in advance.
bigyocks
Also interested in testing.
Running Excel 2016, 32bit.
Hi Chris,
I am really interested in your new excel sample app and I'd like to take part.
Can you send it to me ?
I am running both excel 32bits and 64bits on 2 different machines.
Thanks in advance.
Christophe
Dear Chris -
I would be interested to take a look at the Excel app please.
I have not automated via API yet (either REST or Streaming) but presume
the Excel app works with both, hence live data?
I will also send feedback. Do you have an end date in mind for the beta phase?
Sincerely,
Royston
Hi Chris
According to the IG T&C's for the web API no one can use this new Excel app because they didn't write it themselves (apart from the author I suppose, but even then only if the author wrote it all.)
Hi Chris
That said, I'd also very gladly like to be part of the evaluation.
Regards
Neil
Hi chris,
I would like to beta-test your excel spreadsheet as well.
Kind regards
Kian
Hi Chris,
I am interested in beta-testing the spreadsheet, could you send it to me please?
Regards,
Andy
Hi Chris!
Has the new xls been released yet?
I am trying to "untangle" the current sample excel sheet for my own layout but am finding it quite tricky. So I would like to see the new spreadsheet.
Else should I post my requests/issues in another forum?
TNX
Corne
Please send me the beta sample excel app
Hi Chris
I'm really interested in your new excel/vba sheet sample.
Could you send it to me the new beta version ?
I use XL 32bits
thank you in advance
Best regards
Franck
Hi Chris,
I am interested in using the Excel app too. I use a 64-bit/Win10 PC, Excel 2010.
Many thanks,
Huan
Hi Chris, I would like to use the Excel app. Please can you send it to my email?
Many thanks, Huan
Hi Chris,
I'm interested to be a part of the beta testing.
Thanks
Hi Chris,
I'm interested to be a part of the beta testing.
Thanks