question

Upvotes
Accepted
Robby.Stamper avatar image
7 ●2 ●1 ●2

Excel COM API returning blank records after valid records

Hi,

When I do the following query I get some added records at the end with no data. I have seen this on other queries before. The other query was a pricing query (date, close) How do I eliminate the seemingly random blank records at the end of the query? Of course I can just ignore them, but why are they there? I want to be as efficient as possible about using the API because I will be using this for multiple stocks.

=TR("MSFT.O","TR.DivAnnouncementDate;TR.DivExDate;TR.DivRecordDate;TR.DivPayDate;TR.DivUnadjustedGross;TR.DividendFrequency;TR.DivType","SDate=0 EDate=-20Y CH=Fd")

Also, Is it possible to put in a SDate= and a EDate= in the form of a "MM/DD/YYYY"?

Thanks in advance!

Robby

Dividend Announcement DateDividend Ex DateDividend Record DateDividend Pay DateGross Dividend AmountDividend FrequencyDividend Type3/9/20205/20/20205/21/20206/11/20200.51Quarterly dividendInterim12/4/20192/19/20202/20/20203/12/20200.51Quarterly dividendInterim9/18/201911/20/201911/21/201912/12/20190.51Quarterly dividendFinal6/12/20198/14/20198/15/20199/12/20190.46Quarterly dividendInterim3/12/20195/15/20195/16/20196/13/20190.46Quarterly dividendInterim11/28/20182/20/20192/21/20193/14/20190.46Quarterly dividendInterim9/19/201811/14/201811/15/201812/13/20180.46Quarterly dividendFinal6/13/20188/15/20188/16/20189/13/20180.42Quarterly dividendInterim3/13/20185/16/20185/17/20186/14/20180.42Quarterly dividendInterim12/1/20172/14/20182/15/20183/8/20180.42Quarterly dividendInterim9/19/201711/15/201711/16/201712/14/20170.42Quarterly dividendFinal6/14/20178/15/20178/17/20179/14/20170.39Quarterly dividendInterim3/14/20175/16/20175/18/20176/8/20170.39Quarterly dividendInterim12/1/20162/14/20172/16/20173/9/20170.39Quarterly dividendInterim9/20/201611/15/201611/17/201612/8/20160.39Quarterly dividendFinal6/15/20168/16/20168/18/20169/8/20160.36Quarterly dividendInterim3/16/20165/17/20165/19/20166/9/20160.36Quarterly dividendInterim12/3/20152/16/20162/18/20163/10/20160.36Quarterly dividendInterim9/15/201511/17/201511/19/201512/10/20150.36Quarterly dividendFinal6/9/20158/18/20158/20/20159/10/20150.31Quarterly dividendInterim3/10/20155/19/20155/21/20156/11/20150.31Quarterly dividendInterim12/3/20142/17/20152/19/20153/12/20150.31Quarterly dividendInterim9/16/201411/18/201411/20/201412/11/20140.31Quarterly dividendFinal6/10/20148/19/20148/21/20149/11/20140.28Quarterly dividendInterim3/11/20145/13/20145/15/20146/12/20140.28Quarterly dividendInterim11/19/20132/18/20142/20/20143/13/20140.28Quarterly dividendInterim9/17/201311/19/201311/21/201312/12/20130.28Quarterly dividendFinal6/12/20138/13/20138/15/20139/12/20130.23Quarterly dividendInterim3/11/20135/14/20135/16/20136/13/20130.23Quarterly dividendInterim11/28/20122/19/20132/21/20133/14/20130.23Quarterly dividendInterim9/18/201211/13/201211/15/201212/13/20120.23Quarterly dividendFinal6/13/20128/14/20128/16/20129/13/20120.2Quarterly dividendInterim3/13/20125/15/20125/17/20126/14/20120.2Quarterly dividendInterim12/14/20112/14/20122/16/20123/8/20120.2Quarterly dividendInterim9/20/201111/15/201111/17/201112/8/20110.2Quarterly dividendFinal6/15/20118/16/20118/18/20119/8/20110.16Quarterly dividendInterim3/14/20115/17/20115/19/20116/9/20110.16Quarterly dividendInterim12/15/20102/15/20112/17/20113/10/20110.16Quarterly dividendInterim9/21/201011/16/201011/18/201012/9/20100.16Quarterly dividendFinal6/16/20108/17/20108/19/20109/9/20100.13Quarterly dividendInterim3/8/20105/18/20105/20/20106/10/20100.13Quarterly dividendInterim12/9/20092/16/20102/18/20103/11/20100.13Quarterly dividendInterim9/18/200911/17/200911/19/200912/10/20090.13Quarterly dividendFinal6/10/20098/18/20098/20/20099/10/20090.13Quarterly dividendInterim3/9/20095/19/20095/21/20096/18/20090.13Quarterly dividendInterim12/11/20082/17/20092/19/20093/12/20090.13Quarterly dividendInterim9/22/200811/18/200811/20/200812/11/20080.13Quarterly dividendFinal6/12/20088/19/20088/21/20089/11/20080.11Quarterly dividendInterim3/17/20085/13/20085/15/20086/12/20080.11Quarterly dividendInterim12/19/20072/19/20082/21/20083/13/20080.11Quarterly dividendInterim9/12/200711/13/200711/15/200712/13/20070.11Quarterly dividendFinal6/27/20078/14/20078/16/20079/13/20070.1Quarterly dividendInterim3/26/20075/15/20075/17/20076/14/20070.1Quarterly dividendInterim12/21/20062/13/20072/15/20073/8/20070.1Quarterly dividendInterim9/14/200611/14/200611/16/200612/14/20060.1Quarterly dividendFinal6/22/20068/15/20068/17/20069/14/20060.09Quarterly dividendInterim3/27/20065/15/20065/17/20066/8/20060.09Quarterly dividendInterim12/15/20052/15/20062/17/20063/9/20060.09Quarterly dividendInterim9/23/200511/15/200511/17/200512/8/20050.08Quarterly dividendFinal6/16/20058/15/20058/17/20059/8/20050.08Quarterly dividendInterim3/23/20055/16/20055/18/20056/9/20050.08Quarterly dividendInterim12/9/20042/15/20052/17/20053/10/20050.08Quarterly dividendInterim9/16/200411/15/200411/17/200412/2/20040.08Quarterly dividendInterim7/21/200411/15/200411/17/200412/2/20043Dividend with no set frequencySpecial7/21/20048/23/20048/25/20049/14/20040.08Quarterly dividendInterim9/12/200310/15/200310/17/200311/7/20030.16Annual dividendFinal1/16/20032/19/20032/21/20033/7/20030.08Annual dividendFinal9/12/2007





6/27/2007





3/26/2007





12/21/2006





9/14/2006





6/22/2006





3/27/2006





12/15/2005





9/23/2005





6/16/2005





3/23/2005





12/9/2004





9/16/2004





7/21/2004





7/21/2004





9/12/2003





eikoneikon-com-api
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jirapongse avatar image   Jirapongse

@Robby.Stamper

Hi,

Thank you for your participation in the forum.

Are any of the replies below satisfactory in resolving your query?

If yes please click the 'Accept' text next to the most appropriate reply. This will guide all community members who have a similar question.

Otherwise please post again offering further insight into your question.

Thanks,

AHS

3 Answers

· Write an Answer
Upvotes
Accepted
Alex Putkov.1 avatar image
39.4k ●77 ●11 ●27

I do not reproduce the issue you described. I copied the exact formula from your post, and the result I received was the range of 68 rows (including the column header row) by 7 columns. The last record in the range was the final annual dividend for 2002 announced on 16-Jan-2003 and paid on 7-Mar-2003. Could it be that the additional rows in your case are actually a leftover from some data that was in the worksheet before the retrieval of the dividend history for Microsoft?

It is indeed possible to use exact date values for SDate and EDate parameters in the format MM/DD/YYYY (or DD/MM/YYYY depending on your regional settings) or YYYYMMDD.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
Robby.Stamper avatar image
7 ●2 ●1 ●2

FYI,

The table showing the extra records did not show up well. This image shows what I am talking about. Thanks.


1593100301890.png (63.0 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
Robby.Stamper avatar image
7 ●2 ●1 ●2

Thank you. I put it into another blank tab and did not have any extra data. Sorry to bother you with such a trivial error on my part.

Thanks again,

Robby

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.