I am trying to import some financial data exported from Excel. You can find the relevant file here : Text File or CSV File

This is what the data looks like in Excel : enter image description here

Using the following code to import :

BankShadowFed = 
  Import[FileNameJoin[{NotebookDirectory[], "BankShadowFed.txt"}], 

I have the following problem:

enter image description here

While I can Change to expression each element,

enter image description here

I can't get it all right including the dates...

What is the right way to Import[] those files ? Or Export[] them in the first place?

  • 1
    $\begingroup$ What do you get instead? $\endgroup$ – rcollyer Feb 15 '12 at 16:53
  • $\begingroup$ @ian.milligan I approved your edit, but I don't think it is helpful to add [] brackets to function names. $\endgroup$ – Mr.Wizard Feb 15 '12 at 16:53
  • $\begingroup$ @Mr.Wizard Good to know, thanks for the heads up - will keep that in mind in the future. $\endgroup$ – canadian_historian Feb 15 '12 at 16:57
  • $\begingroup$ @rcollyer, instead of ? The Problem is the Numbers are yet string. I tried all solution offered in my question on plotting list with e. unsuccessfully. $\endgroup$ – 500 Feb 15 '12 at 17:06
  • $\begingroup$ This looks like the same date format that was covered in stackoverflow.com/questions/8896674. Szabolcs's answer looks particularly useful. $\endgroup$ – Brett Champion Feb 15 '12 at 17:33

Overall, your data is just badly formatted. For instance, later in the list your dates look similar to "3-Mar" which is interpreted as the third of March not March 2003, as you intended. For the most part, this is not your fault, but Excel arbitrarily formats data, and you have to be vigilant that it doesn't misinterpret it. Towards that end, I've rewritten my answer to work around the misformatting.

First things first, your numeric data is formatted with commas, so we need to get rid of them.

killCommas[a_String] := StringReplace[a, "," -> ""]
killCommas[a_] := a

Second, your dates, as I said above, are misformatted. The code, below, splits the date at "-", checks if the numeric part has length 1 and prepends it with a "0" if it does, sorts the two strings by length, reverses the sort, and puts them back together with "-" between.

fixDate[a_String] := StringJoin@
    If[StringLength[#] == 1, "0" <> #, #] & /@ 
     SortBy[StringSplit[a, "-"], StringLength]],

Now, I strip off the header from the list

dat = Import[<txt url>, "TABLE"][[2 ;;]] 

and using the helper functions, I fix all of the data:

{DateList[{fixDate[#1], {"MonthNameShort", "YearShort"}}],
   Sequence @@ ToExpression[ killCommas /@ {##2}]} & @@@ dat
| improve this answer | |
  • $\begingroup$ Nice and concise. The comma problem also exists in the second column just the example choosen had only a 3 digit number but if you look further down the list you see the 4 digit numbers appearing. $\endgroup$ – Matariki Feb 15 '12 at 18:56
  • $\begingroup$ @Matariki you're right. My testing only included the first few points. Corrected. $\endgroup$ – rcollyer Feb 15 '12 at 19:03
  • $\begingroup$ @rcollyer, did your solution work for you ? I am sorry, but I still can`t get it right using your solution. $\endgroup$ – 500 Feb 15 '12 at 23:16
  • 1
    $\begingroup$ @500, I fixed it. $\endgroup$ – rcollyer Feb 16 '12 at 0:49
  • 1
    $\begingroup$ @rcollyer, Sadly, I think I will keep you updated :-) $\endgroup$ – 500 Feb 16 '12 at 2:29

Assuming I understand correctly your question, here's what I would do: First, let's assign the second column to a variable a=BankShadowFed[[All,2]]. Now let's remove every , from the numbers in the list, using aa=StringReplace[a, "," -> ""].

We have a list of strings, which we can now turn into a list of expressions using aaa=Table[ToExpression[aa[[i]]], {i, 2, Length[aa]}]. This can now be plotted using ListPlot[aaa] to give

enter image description here

Of course, you can do this for every column, and re-create a table from scratch. I hope this was useful. If not, precise your question!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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