Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I'm trying to import a file from Excel. In fact, I've been trying to do it all day. The problem is that it does not show up in the right format. Where columns in Mathematica represent columns in Excel. This is the output that I get in Mathematica.

The output should be "Name of company1","Name of company1","Name of company2","Name of company2" and so one.

If we look at the column 1 and 2 under company 1 it should be in column 1 the date and in column 2 the share price.

file={{{"ADS GY Equity", "ADS GY Equity", "ALV GY Equity", "ALV GY Equity", 
"BAS GY Equity", "BAS GY Equity"}, {{2000, 1, 4, 0, 0, 0.}, 
17.5, {2000, 1, 4, 0, 0, 0.}, 285.934, {2000, 1, 4, 0, 0, 0.}, 
23.925}, {{2000, 1, 5, 0, 0, 0.}, 17.5, {2000, 1, 5, 0, 0, 0.}, 
294.078, {2000, 1, 5, 0, 0, 0.}, 23.375}, {{2000, 1, 6, 0, 0, 0.}, 
18.25, {2000, 1, 6, 0, 0, 0.}, 297.698, {2000, 1, 6, 0, 0, 0.}, 
24.015}, {{2000, 1, 7, 0, 0, 0.}, 18., {2000, 1, 7, 0, 0, 0.}, 
305.977, {2000, 1, 7, 0, 0, 0.}, 25.}, {{2000, 1, 10, 0, 0, 0.}, 
18.272, {2000, 1, 10, 0, 0, 0.}, 307.742, {2000, 1, 10, 0, 0, 0.}, 
25.11}, {{2000, 1, 11, 0, 0, 0.}, 18.103, {2000, 1, 11, 0, 0, 0.}, 
304.936, {2000, 1, 11, 0, 0, 0.}, 23.995}, {{2000, 1, 12, 0, 0, 0.},
18., {2000, 1, 12, 0, 0, 0.}, 306.294, {2000, 1, 12, 0, 0, 0.}, 
24.}, {{2000, 1, 13, 0, 0, 0.}, 17.462, {2000, 1, 13, 0, 0, 0.}, 
311.27, {2000, 1, 13, 0, 0, 0.}, 23.75}, {{2000, 1, 14, 0, 0, 0.}, 
16.837, {2000, 1, 14, 0, 0, 0.}, 336.018, {2000, 1, 14, 0, 0, 0.}, 
23.75}, {{2000, 1, 17, 0, 0, 0.}, 17., {2000, 1, 17, 0, 0, 0.}, 
334.254, {2000, 1, 17, 0, 0, 0.}, 23.935}, {{2000, 1, 18, 0, 0, 0.},
17., {2000, 1, 18, 0, 0, 0.}, 325.051, {2000, 1, 18, 0, 0, 0.}, 
23.975}, {{2000, 1, 19, 0, 0, 0.}, 16.65, {2000, 1, 19, 0, 0, 0.}, 
325.748, {2000, 1, 19, 0, 0, 0.}, 24.92}, {{2000, 1, 20, 0, 0, 0.}, 
16.375, {2000, 1, 20, 0, 0, 0.}, 326.563, {2000, 1, 20, 0, 0, 0.}, 
24.7}}}

To be more precise. I want the data of the list to look like this! I am going to give an exmple from the first stock. This is the output format I want through all the stocks.

{{{ADS GY Equity}, ADS GY Equity} {{2013, 8, 1}, 904.22}, {{2013, 8, 2}, 906.57}, {{2013, 8, 5}, 905.}, {{2013, 8, 6}, 896.57}, {{2013, 8, 7}, 890.65}, {{2013, 8, 8}, 892.66}, {{2013, 8, 9}, 890.41}, {{2013, 8, 12}, 885.51}, {{2013, 8, 13}, 881.25}, {{2013, 8, 14}, 869.81}, {{2013, 8, 15}, 859.66}, {{2013, 8, 16}, 856.91}, {{2013, 8, 19}, 865.65}, {{2013, 8, 20}, 865.42}, {{2013, 8, 21}, 869.33}, {{2013, 8, 22}, 873.71}}

share|improve this question
    
note sure, but maybe: Flatten[file, 1]//MatrixForm ? –  Pinguin Dirk Aug 23 '13 at 8:55
    
Al ready tried that, did not work, it does not create brackets around the the the stock prices –  ALEXANDER Aug 23 '13 at 9:00
    
Try to look at it with First@file//MatrixForm -- I think it looks the way you say it should look. –  Pickett Aug 23 '13 at 9:00
1  
Import imports sheets in an Excel file as a list of sheets. If there is only one sheet you get a list with one sheet. This explains the outer { } pair. The date gets converted to a DateList object, which is a list {y,m,d,h,s}. I don't see a good reason for enclosing the prices in curly brackets. –  Sjoerd C. de Vries Aug 23 '13 at 9:30
5  
This question appears to be off-topic because it is seems to be too localized and of limited use for future visitors. –  Yves Klett Aug 23 '13 at 11:52
show 3 more comments

closed as off-topic by Yves Klett, m_goldberg, Oleksandr R., Artes, Michael E2 Aug 24 '13 at 3:19

  • The question does not concern the technical computing software Mathematica by Wolfram Research. Please see the help center to find out about the topics that can be asked here.
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 5 down vote accepted

You can add those bracets via MapAt:

f = MapAt[{#} &, file[[ 1]], {2 ;;, 2 ;; ;; 2}];
Last@f
{{2000, 1, 20, 0, 0, 0.}, {16.375}, {2000, 1, 20, 0, 0, 0.}, 
 {326.563}, {2000, 1, 20, 0, 0, 0.}, {24.7}}

Why {2 ;;, 2 ;; ;; 2}, 2;; to skip the headers, 2;; ;;2 for every other column to the end begging from the second.


Instead of adding brackets you can convert this date back to a transparent form:

f = MapAt[DateString[#, {"Day", "/", "Month", "/", "Year"}] &, 
          file[[ 1]], {2 ;;, ;; ;; 2}];
f   // TableForm

enter image description here


Edit your edit and the expected result seem to be a bit different than what you've asked at the beggining. For this form you can use Partition:

g = Partition[#, 2] & /@ file[[ 1]]
g[[ 1]] = {{#}, #2} & @@@ g[[ 1]]; (*just to improve headers line to fit the form*)
g[[ 1 ;; 3]]
{{{{"ADS GY Equity"}, "ADS GY Equity"}, {{"ALV GY Equity"}, "ALV GY Equity"}, 
   {{"BAS GY Equity"}, "BAS GY Equity"}}, 
   {{{2000, 1, 4, 0, 0, 0.}, 17.5}, {{2000, 1, 4, 0, 0, 0.}, 285.934}, 
   {{2000, 1, 4, 0, 0, 0.}, 23.925}}, {{{2000, 1, 5, 0, 0, 0.}, 17.5}, 
   {{2000, 1, 5, 0, 0, 0.}, 294.078}, {{2000, 1, 5, 0, 0, 0.}, 23.375}}}

  • It's very likely that you can avoid this list manipulation by proper specification of Import.
share|improve this answer
    
Great thank you. Unbelievable, have been looking at MapAt for 3 times now and I still did not understand that it was possible! –  ALEXANDER Aug 23 '13 at 9:35
    
@ALEXANDER I'm glad it helps. Please see the edit reffering to you edit. –  Kuba Aug 23 '13 at 9:47
    
I have one more question for you. f = MapAt[{#} &, file[[ 1]], {2 ;;, 2 ;; ;; 2}]; In the user manual input= f = (3 + #) &, and in the output it becomes 3+#1 &. What is actually going on here. Im not able to see the logic. –  ALEXANDER Aug 23 '13 at 9:55
    
@ALEXANDER If you type f = (3 + #) & you just assign pure function to symbol f, so next time f[5] should result in 8. –  Kuba Aug 23 '13 at 10:01
1  
Thank you, makes sense. Just starting to get a tiny bit of understanding of the language, and I am loving Mathematica more and more! –  ALEXANDER Aug 23 '13 at 10:05
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.