search

quandl / quandl-excel-windows

Quandl Excel Addin for Windows
https://www.quandl.com/tools/excel
13 stars 11 forks source link

Excel addin returns data as string, not number (Excel beta addin v3.80.1) #17

Closed gilesm2 closed 7 years ago

gilesm2 commented 7 years ago

Test: =ISNUMBER(QSERIES("CURRFX/GBPUSD/RATE",42767,"daily",,,,FALSE,FALSE)) Returns FALSE; it should be TRUE. This is GBP/USD currency data for Wednesday 1st Feb 2017, and the Quandl result is "1.2658867835999".

These are numerical rates (and show in the graph in Quandl as numerical), but they come back as strings in Excel. Therefore number styles (e.g. displaying to n decimal places) will not work.

theAngeLopez commented 7 years ago

Hello gilesm2,

By design, QSERIES writes data directly into a cell as opposed to returning values to be used within other functions. This means that if it’s not null, it’s a string that Excel can then convert. This ties into why you’re getting FALSE from the query. Because QSERIES returns a string, when you call ISNUMBER on the original query, Excel hasn’t had the opportunity to convert it yet. For the time being, you can format the data type on the whole column in your spreadsheet or use the Excel VALUE function chained in your call as per: https://support.office.com/en-us/article/VALUE-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2

Hope this answers your question and thanks for using our Excel Addin! Angel

gilesm2 commented 7 years ago

hi Angel, Thank you for the reply, and explaining that QSERIES is designed to write the data directly into the cell as text, even if I think it should be a number.

I'm using QSERIES because the (rather elegant) QDATA function was removed. Is there a better function I should be using, which returns a number as a typical user would expect, please?

Regards, Giles.

On 6 March 2017 at 18:47, AngeLopez notifications@github.com wrote:

Closed #17 https://github.com/quandl/quandl-excel-windows/issues/17.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/quandl/quandl-excel-windows/issues/17#event-988262542, or mute the thread https://github.com/notifications/unsubscribe-auth/ATEW2o6OBwHTtWLCnVqWm5EUXrZ3R8m-ks5rjFTbgaJpZM4MC8xO .