MarketView ® ExcelTools TM PRO Documentation
MarketView ExcelTools PRO provides an easy way to get MarketView historic data and quotes from dynamic languages such as VBA, VBS, JScript or Python.
Examples in this code will be given in Visual Basic for Automation, you'll need to adjust the syntax for other languages.
1. Connection
The main object needed to establish a connection to the server has a Program ID GvIpc.Com.ServerConnection. After it's created you need to call its Connect method and pass it your usenrname and password:
Set con = CreateObject("GvIpc.Com.ServerConnection")
con.Connect username, password
2. History data manipulation
Once you have this object, you can call a plethora of methods on it. The methods retrieve either last N data points of daily/weekly/monthly/quarterly/yearly data or a specific data range:
Array GetDailyTail(string symbol, int days,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
Array GetWeeklyTail(string symbol, int weeks,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
Array GetMonthlyTail(string symbol, int months,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
Array GetQuarterlyTail(string symbol, int quarters,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
Array GetYearlyTail(string symbol, int years,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
Array GetDailyRange(string symbol, DateTime from, DateTime to,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
Array GetWeeklyRange(string symbol, DateTime from, DateTime to,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
Array GetMonthlyRange(string symbol, DateTime from, DateTime to,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
Array GetQuarterlyRange(string symbol, DateTime from, DateTime to,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
Array GetYearlyRange(string symbol, DateTime from, DateTime to,
LeadLagOptions leadLagOptions = null,
string currency = null,
string currencySource = null,
UnitConversion unit = null,
FillMethod fillMethod = FillMethod.NoFill,
FillFrequency fillFrequency = FillFrequency.SevenDays)
These methods return an array of TimeSeriesElement objects, each of which contains the following properties:
DateTime
Open
High
Low
Close
Volume
OpenInterest
Here's a VBA example of a call:
array = con.GetDailyRange(Symbol:="/GCL", from:=DateSerial(2011, 5, 1), to:=DateSerial(2011, 5, 10))
Then, for each element of the array you can ask for the DateTime, Open, High, Low, Volume and OpenInterest properties.
If you need to do various conversions on the time series data, use the leadLagOptions, currencySource, currency and unit parameters:
con.GetDailyTail(Symbol:="/GCL", days:=5, leadLagOptions:=con.CalendarDays(5))
con.GetDailyTail(Symbol:="/GCL", days:=5, Currency:="EUR", currencySource:="USF")
con.GetDailyTail(Symbol:="/GCL", days:=5, unit:=con.Barrels)
The available unit conversions are Barrels, Liters, Kiloliters, CubicMeters, Gallons, ThouStdCubicFt, MetricTons, ShortTons, MMBTUs, Therms, Gigajoules, GigawattHours, KilowattHours and MegawattHours.
You can also ask for specific fill method for missing data and chose fill frequency, that lets you specify amount of days in a week you need data for. Default is NoFill for fill method, and SevenDays for fill frequency.
con.GetDailyTail(Symbol:="/GCL", days:=7, fillMethod:=con.Interpolate)
con.GetDailyTail(Symbol:="/GCL", days:=7, fillMethod:=con.Interpolate, fillFrequency:=con.SixDays)
con.GetDailyTail(Symbol:="/GCL", days:=7, fillMethod:=con.Average, fillFrequency:=con.SixDays)
The available fill methods are NoFill, FillForward, FillBackward, Average and Interpolate, and fill frequencies are Business, SixDays and SevenDays.
You can also get the current quote for a symbol:
Set quote = con.GetQuote("/GCL")
The returned quote object has the following properties:
Last
NetChange
PercentChange
High
Low
Open
Close
Settle
Bid
Ask
TradeSize
OpenInterest
TradeDateTimeUtc
Volume
PrevPrice
TickCount
ContractDate
ExpirationDate
MidPoint
CloseDate
Currency
LotUnit
PutCall
Strike
SettleDate
Underlier
BidDateTimeUtc
BidSize
AskDateTimeUtc
AskSize
PrevHigh
PrevLow
PrevOpen
PrevClose
PrevVol
MostRecentValue
MostRecentValueDate
3. MarketScript related functions
Two additional methods allow loading and saving user defined formulas from and into Excel sheet:
Array GetUserDefinedFormulas()
bool SaveUserDefinedFormula(string symbol, string description, string folder, string definition)
4. Disposal
Finally, when you are done with the connection object and don't want to retrieve any more data, call its Dispose method:
con.Dispose
Note:
You can use built-in VBA support to schedule executing some of the tasks to run at specific time by creating your own macros using following code:
Application.OnTime TimeValue("18:00:00"), "MyMacro"
This code schedules a particular macro to execute at 18:00 each day. For additional info, take a look at the following link
