3
$\begingroup$

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"}], 
   "Table"];

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?

Improve this question
$\endgroup$
7
  • 1
    $\begingroup$ What do you get instead? $\endgroup$
    – rcollyer
    Commented Feb 15, 2012 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
    Commented Feb 15, 2012 at 16:53
  • $\begingroup$ @Mr.Wizard Good to know, thanks for the heads up - will keep that in mind in the future. $\endgroup$
    – canadian_scholar
    Commented Feb 15, 2012 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
    Commented Feb 15, 2012 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
    Commented Feb 15, 2012 at 17:33

2 Answers 2

Reset to default
12
$\begingroup$

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@
  Riffle[Reverse[
    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
$\endgroup$
8
  • $\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
    Commented Feb 15, 2012 at 18:56
  • $\begingroup$ @Matariki you're right. My testing only included the first few points. Corrected. $\endgroup$
    – rcollyer
    Commented Feb 15, 2012 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
    Commented Feb 15, 2012 at 23:16
  • 1
    $\begingroup$ @500, I fixed it. $\endgroup$
    – rcollyer
    Commented Feb 16, 2012 at 0:49
  • 1
    $\begingroup$ @rcollyer, Sadly, I think I will keep you updated :-) $\endgroup$
    – 500
    Commented Feb 16, 2012 at 2:29
5
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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