Infront Excel VBA API : InfCommand

The macro Excel command "InfCommand" was created to make it easier to use VBA macros. Those can be used with personal models or predefined ones to automate functions and commands to use within Excel. "InfCommand" can help you to:

Transform a company name into company code.
Download a list of instruments.
Refresh a Quick Report or a Time Series.
Refresh your workbook and create or update charts ...

Syntax

To run a macro, you need to call "Run" followed by the name of the Excel command "InfCommand".

"InfCommand" has 2 parameters:

The name of the command you want to run (such as: "CodeLookUp").
A parameter-set that can be either an Excel array or a string (Json format). This parameter-set can define a set of multiple arguments and their values.

In some case the Excel command returns a result that depends on the command called. In that case, you will just need to enclose the Excel command, the name of the macro you want to run and the parameters in parentheses.

Example

VB

Sub RefreshActiveWorkbook()
	Run "InfCommand", "RefreshActiveQR", "{'targetCell':'B1', 'postMacro': 'popUpMessage'}"
End Sub

Or

VB

Sub CodeLookupViaRange()
	Run "InfCommand", "CodeLookUp", Range("P2:Q4")
End Sub

Or

VB

Function IsConnected() As Boolean
    IsConnected = Run("InfCommand", "IsConnected")
End Function

Command name

The command name is a string that define the type of the command requested.

Example: "CodeLookUp", "DownloadUserList"....

For more information, see "Command details".

Parameter-set

The arguments have to be set as a string (Json format = surrounded by brackets) or a 2-dimensional array. Allowed arguments are:

Parameter	Value	Description
`_targetCell`	'A1'	The cell where you want to upload or write the data / execute the macro. Default: the active cell
`synchronous`	True / False	Use if you want your macro to be called synchronously or not. Default: False
`Interactive`	True / False	If 'true', it opens the form to let you choose: The company in the CodeLookUp. The list in the DownloadList. and so on... Default: False
`Instrument`	'Apple' '30015NU' 'LSE:VOD'	The name or code of the instrument you will use in your macro. Could also be set by a cell reference. Example: `Run..... "{'instrument':'30015NU'}"` Default: empty
`Index`	'CAC 40'	The name of the index you want to use in your macro. Default: empty
`UserList`	'MyList'	The name of the list you want to download. Default: empty
`PostMacro`	'PopUpMsg'	The name of the macro you want to call once the one you are running is finished. Default: empty
`PgScope`	'International' 'Regional' 'Domestic'	The scope of the peer group you want to download. Default: empty
`DisplayTitle`	True / False	If you want to add titles to your downloaded table (userList, PeerGroup). Will add the name of the list downloaded and the name of the downloaded list and the name of the columns. Default: False
`OutputLayout`	All, Code, Name	Implemented since V3.28 Option to display columns or not depending on the value: All = code, name, ISIN, ticker, country… All the columns / information available Name = code & company name Code = only company codes Default: Code
`timeOut`	'30'	If you want the synchronicity to last, the window, displaying the download in process, will close automatically at the end of the timeout. Default: 30 seconds
`SymbolFormatCode`	True / False	The format of the value you want to retrieve using the 'GetSymbolType' command: True = the number of the code False = the text description Default: False
`InstrumentType`	CompanyCode InstrumentCode All Stock…	The type of instrument you want to retrieve using the 'CodeLookUp' on interactive or non-interactive mode. Default: All – meaning the first instrument retrieved

To call the parameters via a cell reference:

VB

Sub TestQuickLookUpViaRange()
	Run "InfCommand", "CodeLookUp", Range("P2:Q4")
End Sub

Command details

IsConnected

Returns 'true' if you are connected, 'false' otherwise.

This macro doesn’t handle any parameters. You will need to run another call to InfCommand to run a separate macro.

Example

VB

Function IsConnected() As Boolean
    IsConnected = Run("InfCommand", "IsConnected")
End Function

RefreshActiveWorkbook / RefreshActiveWorksheet

Will refresh the active workbook / worksheet. Can handle a call to a postMacro. This macro does not handle to be run synchronously. Return 'true' if the command is run asynchronously, otherwise 'false'.

Example

VB

Sub RefreshActiveWorkbook ()
	Run "InfCommand", "RefreshActiveWorkbook", "{'postMacro':'popUpMessage'}"
End Sub

RefreshActiveQR / RefreshActiveTS

Will refresh the 'Quick Report' / 'Time Series' in the worksheet. Can handle a call to a postMacro. It cannot be run synchronously.

You will then need to enter the cell reference for the refresh button in the parameters.

Example

VB

Sub RefreshQR()
	Dim ret As Boolean
	ret = Run("InfCommand", "RefreshActiveQR", "{'targetCell':'B1', 'postMacro':'Hello'}")
End Sub

Sub RefreshTS()
	Run "InfCommand", "RefreshActiveTS", "{'targetCell':'G1'}"
End Sub

Sub Hello()
	MsgBox ("Hello")
End Sub

GetSymbolType

Can tell you if the code passed in the instrument parameter is a:

Company code.
Instrument code.
Index code.
VWD code.
Or if the code is unknown or incorrect.

You can choose to retrieve the response via a code or via the text description. The parameters needed are:

The code you want to test set as 'instrument':'codeToTest'.
If you want to retrieve the response as code or not by setting 'SymbolFormatCode':'true'. By default it would be set to 'false' and will return the response by text.

Example

VB

Sub SymbolTypeCode()

	Dim cell As String
	Dim ret As Integer

	cell = Range("A3").Value
	ret = Run("InfCommand", "GetSymbolType", "{'instrument':'" + cell + "','SymbolFormatCode':'TRUE'}")
	Range("B3").Value = ret

End Sub

Sub SymbolTypeString()

	Dim cell As String
	Dim ret As String

	cell = Range("D3").Value
	ret = Run("InfCommand", "GetSymbolType", "{'instrument':'" + cell + "','SymbolFormatCode':'FALSE'}")
	Range("E3").Value = ret

End Sub

Sub SymbolType()
	Dim ret As Integer
	ret = Run ("InfCommand", "GetSymbolType", "{'instrument':'30015NU', 'SymbolFormatCode':'true'}")
End Sub

DownloadIndexList

Displays the list of all index names via an array starting on the active cell by default or starting on the cell passed into the parameters. Does not need any other parameters.

Example

VB

Sub TestIndexList()
	Run "InfCommand", "DownloadIndexList", "{'targetCell':'A1'}"
End Sub

DownloadCurrencyList

Displays the list of all currency codes via an array starting on the active cell by default or starting on the cell passed into the parameters. Does not need any other parameters.

Example

VB

Sub TestCurrencyList()
	Run "InfCommand", "DownloadCurrencyList"
End Sub

DownloadIndex

Displays the list of all companies included in the index with the parameters. If the index name is not set, the command will run interactively and ask to select the index constituents to be downloaded. In which case, the interactive parameter will be set automatically to 'true'.

Parameters allowed: index, interactive, targetCell, synchronous, postMacro, DisplayTitle, OutputLayout.

Example

VB

Sub TestIndexConstituentInteractive()
	Run "InfCommand", "DownloadIndex", "{'interactive':true}"
End Sub

Sub TestIndexConstituentNamed()
	Run "InfCommand", "DownloadIndex", "{'index':'CAC 40'}"
End Sub

CodeLookUp

Will change the company name in a cell or directly into the form (if interactive) into a company code.

Parameters allowed: interactive, targetCell, synchronous, postMacro, instrumentType.

Example

VB

Sub TestQuickLookUp()
	Run "InfCommand", "CodeLookUp", "{'synchronous':true, 'interactive':true, 'timeout':40}"
End Sub

CodeLookUp with InstrumentType

You can choose the type of instrument you want to retrieve. To do so, use the "InstrumentType" parameter. You can choose to retrieve:

A company code using "companyCode".
An instrument code using "InstrumentCode".
A Bond.
A Stock.
.... any asset type present on the CodeLookUp window :

Example

VB

Sub TestQuickLookUpWithCompanyType()
	Run "InfCommand", "CodeLookUp", "{'interactive':true, 'instrumentType':'CompanyCode'}"
End Sub

Sub TestQuickLookUpWithInstrumentType()
	Run "InfCommand", "CodeLookUp", "{'interactive':false, 'instrumentType':'Stock'}"
End Sub

DownloadUserList

Will allow the download of a list previously uploaded by the user (via a peer group list or not). If the userList is not referenced, the macro will be interactive, and the form will allow you to select the list you want to download.

Parameters allowed: interactive, targetCell, userList, synchronous, postMacro, displayTitle, OutputLayout.

Example

VB

Sub UserList()
	Run "InfCommand", "DownloadUserList"
End Sub

Sub UserListNamed()
	Run "InfCommand", "DownloadUserList", "{'UserList':'PG_Axa'}"
End Sub

DownloadPeerGroup

Will allow the download of a peer group list. If the instrument is not referenced, the macro will be interactive, and the form will allow you to select the instrument and the PgScope.

Parameters allowed: interactive, targetCell, instrument, pgScope, synchronous, postMacro, displayTitle, OutputLayout.

Example

VB

Sub PeerGroupListWithTitles()
	Run "InfCommand", "DownloadPeerGroup", "{'instrument':'30015NU','interactive':'false','pgScope':'Regional', 'displayTitle':true,'OutputLayout':'All'}"
End Sub

Sub PeerGroupInteractive()
	Run "InfCommand", "DownloadPeerGroup"
End Sub

How to create / use macro in Excel

<ALT>+<F11>

To create a macro, open a workbook (new or a model you want to improve with some macros). Press <ALT>+<F11>, it will open the VBA environment windows.

Select "Insert" and click on "Module".

It will open a new "Module" that you will be able to name in the properties window.

In this module you will be able to add as many macros as you want. You can test them in Debug mode by clicking in the code of one macro and pressing <F8>.

<ALT>+<F8>

After having saved your macros, you can use them by pressing <ALT>+<F8> in your workbook and select the macro you want to trigger.