Hey all,
I've got to analyse some financial data which I got in a an Excel Spredsheet. To test my code I use a spredsheet with only one dataset which contains 4 columns. The first one has the date (dd-mmm-jjjj), the other three got numeric data from shares (like share price). Later on there will be much more, since I do analyse the data of a whole stock market for a longer time period.
This is how I started and where I got my first problem:
dataFromTable=readtable('Test_Apple.xlsx')
after getting the 2nd and 3rd columns as string arrays I tried this:
dataFromTable=importdata('Test_Apple.xlsx')dataFromTable=dataFromTable.data
but this gave the numbers in a way out I didn't expected. The matrix did change the numbers somehow from 3,000,000.00 to 0.3*10^7 for smaller numbers it changed to 0.0000 which isn't helpful, therfore I wanted to stay with readtable, since I want to use the date column as well it seems necessary to do so.
The table did show the numbers correct, but the 2nd and 3rd columns where given as cell arrays. Changing those with cell2mat for each column helped to get numeric arrays, but only if I did it for a few rows, otherwise I got the Error "Error using cat Dimensions of arrays being concatenated are not consistent." Is this because of the NaN in the Dataset?
This gave the error:
dataFromTable.APPLE_TURNOVERBYVALUE=cell2mat(dataFromTable.APPLE_TURNOVERBYVALUE)
This worked without an error, but doesn't help since it isn't the whole dataset:
dataFromTable=dataFromTable(1:4,1:end)dataFromTable.APPLE_TURNOVERBYVALUE=cell2mat(dataFromTable.APPLE_TURNOVERBYVALUE)dataFromTable.APPLE_TURNOVERBYVOLUME=cell2mat(dataFromTable.APPLE_TURNOVERBYVOLUME)
This helped to get rid of the cell arrays and works for the example, but if there are hundreds of shares I'm gonna analyse it should be easier somehow – but I didn't figure out how yet. Also it did not work for the whole dataset, somehow it should be possible to replace the cell arrays in the table with numeric arrays or get the correct type of arrays right away with readtable.
Any help is much appreciated!
Best Answer