How do I get data for holidays and weekends in Excel VBA via RHistoryAPI

Hi,

I use the Basic AdxHistory.xlsm Makro in order to download daily closing price for some instrument. I get only the dates, when the price was published. However, I need the complete time series, where holidays and weekends are also present. What do I do? I would expect the .Mode property, but it does not help. Small example how I want to see it:

Friday - 100

Saturday - NA

Sunday - NA

Monday - 110

Instead I see:

Friday - 100

Monday - 110

Here is the macro.

Option Explicit

Dim WithEvents myAdxRtHist As AdfinXRtLib.AdxRtHistory

'*********INTERDAY*********************
Private Sub cmdGetInterday_Click()
Dim arrFlds() As Variant

ActiveCell.Select

' Note the use of CreateReutersObject - function in the PLVbaApis module.
If myAdxRtHist Is Nothing Then Set myAdxRtHist = CreateReutersObject("AdfinXRtLib.AdxRtHistory")

On Error GoTo errHndlr
With myAdxRtHist
.FlushData
.ErrorMode = EXCEPTION ' EXCEPTION, DialogBox, NO_EXCEPTION
.Source = "IDN"
.ItemName = [C7].Value
.Mode = [H8].Value
.RequestHistory ("DATE,CLOSE") 'NOTE USE OF OLD FIELD NAMES, NOT ("TRDPRC_1.TIMESTAMP,TRDPRC_1.CLOSE,TRDPRC_1.VOLUME")

'arrFlds = Array("DATE","CLOSE","VOLUME")
'.RequestHistory ()arrFlds
'.RequestHistory ("*") ' "*" requests all fields.
End With
Exit Sub

errHndlr:
[F13].Value = Err.Number & " " & Err.Description
End Sub

Find more posts tagged with

python

eikon

vba

workspace

eikon-com-api

eikon-data-api

refinitiv-dataplatform-eikon

workspace-data-api

Accepted answers

Richard.Lyons1

@aleksei.shvetsov

Your code is referencing the AdfinX history module. I think you need to use the RHistory API, which has the same parameters and arguments as the RHistory function; you can then specify NULL:NA and DAY:A to get a table like this (which is, I think, what you want to achieve):

All comments

SupDev1

Hi @aleksei.shvetsov,

For RHistory function you can add 'DAY:A' as display parameters in formula to shows all days including the weekend. I don't know if it works for AdxRtHistory but you can try in .Mode.

What you can add to .Mode (and maybe it will work):

NULL:NA ->The Null attribute dictates how empty entries should be handled. The NULL keyword defines the action taken only where date entries with null data are encountered. Dates before the start date or following the end date of the time series are always ignored.

aleksei.shvetsov

Hi, here is my RequestMode:

START:060120 END:061720 SORT:ASC FRQ:D NULL:NA

The DAY:A returns me an error "invalid keyword"

You can pick up any underlying, for Example .SPX

So I basically do not get empty cells at all. Is there any solution to this? I do not want to guess, whether it was a holiday, but retrieve the data directly from Reuters.

Thank you!

Richard.Lyons1

@aleksei.shvetsov

EXPLORE OUR SITES