import - Importing .txt or .csv financial data

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 :

Using the following code to import :

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

   "Table"];

I have the following problem:

While I can Change to expression each element,

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?

Answer

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[, "TABLE"][[2 ;;]] 

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

{DateList[{fixDate[#1], {"MonthNameShort", "YearShort"}}],
   Sequence @@ ToExpression[ killCommas /@ {##2}]} & @@@ dat