How to use date related function when data column is integer

Question

This is a function to pull dividend information from Yahoo Finance.

=importdata("http://real-chart.finance.yahoo.com/table.csv?s=T&a=1&b=1&c=2010&d="&
month(today())&"&e="&DAY(today())&"&f="&year(today())&"&g=v&ignore=.csv")

This returns...

  A       B
Date    Dividends
42648   0.48
42557   0.48
42466   0.48
42375   0.48
42284   0.47
42193   0.47
42102   0.47
42011   0.47
41920   0.46
41828   0.46
41737   0.46
41647   0.46
41555   0.45
41463   0.45
41372   0.45
41282   0.45
41187   0.44
41096   0.44
41004   0.44
40914   0.44
40821   0.43
40730   0.43
40639   0.43
40549   0.43
40457   0.42
40366   0.42
40275   0.42

What I'm trying to do is to group by year of each transaction. I found a workaround to achieve this.

=QUERY( A:B, 
"Select year(A) , sum(B) Where A is not null Group by year(A) Label year(A) 'Year',  
sum(B) 'Total'" , 1)

*Result

Year    Total
2010    70.530003
2011    85.077798
2012    85.877801
2013    99.133401
2014    90.649999
2015    87.259999
2016    104.349998

Although this works great if I manually change the cell format of Column A into date format, I'd like to make it into one function. More of something like this.

=query(importdata("http://real-chart.finance.yahoo.com/table.csv?s=T&a=00&b=3&c=2000&d="&
month(today())&"&e="&DAY(today())&"&f="&year(today())&"&g=v&ignore=.csv"),
"Select year(Col1) , sum(Col2) Where Col1 is not null Group by year(Col1) 
Label year(Col1) 'Year',  sum(Col2) 'Total'")

This function gives me an error message saying

Can't perform the function year on a column that is not a Date or a DateTime column

I guess it is because Col1 is integer, not a date format.

Any workaround to make this work?

Answer 1

Short answer

Convert column A number values to dates by using TO_DATE built-in function.

Formula

=ArrayFormula(query({TO_DATE(A:A),B:B},
"Select year(Col1) , sum(Col2) Where Col1 is not null Group by year(Col1) Label year(Col1) 
'Year',  sum(Col2) 'Total'" , 1))

Explanation

Simplified case

• The following formula assumes that the imported data is on cells A2:B28.
• TO_DATE is applied to Column A values, then an array is built to be used as the first argument of QUERY.

=ArrayFormula(query({TO_DATE(A2:A28),B2:B28},"select year(Col1)"))

Single formula

Although this could be possible, this will require a very complex (hard to read and hard to maintain, with repeated nested functions) that it's very likely that a script will be a better alternative than a single formula.