2
$\begingroup$

My goal is to curate the following data before I export it as a CSV to open in Excel.

I have downloaded financial data for more than one asset, like the following:

test2 = FinancialData[#, {{2000, 1, 1}, {2000, 1, 20}}] & /@ {"GS", 
"MS", "BAC"}

Step 1.

I would like to insert the asset's label to the start of each series, to read

{{GS, {{2000,1,3}, 88.313}, {{2000,1,4}, 82.75},...,
{{2000,1,20},85.938}},{MS,{{2000,1,3},56.0327},{{2000,1,4},51.8822},...,
{{2000,1,20},55.9555}},{BAC,{{2000,1,3},24.219},{{2000,1,4},22.782},...,{{2000,1,20},23.032}}}

I have tried

MapThread[Insert[test2, #1, #2] &, {{"GS", "MS", "BAC"}, {{1, 1}, {2, 1},{3, 1}}]

and many others, but nothing works.

Step 2. The second step is to organise the data in such a way that I can export it as a .csv to read like the following:

curated data

When I try the following, I manage to break apart dates and values, but all in two columns, as opposed to two columns for each asset (moreover, Transpose obviously stops working once I add the assets' labels).

Flatten[Transpose[test2], 1] // TableForm

How to manipulate the data to look like the spreadsheet above when I export it?

Afterthought: It occurred to me when typing up this question that it might be better to perform step2 before step1.

$\endgroup$
  • $\begingroup$ It occurred to me when typing up this question that it might be better to perform step2 before step1. $\endgroup$ – Thadeu Freitas Filho Jun 25 '18 at 8:12
  • $\begingroup$ Thread /@ Transpose[{{"GS", "MS", "BAC"}, test2}]? $\endgroup$ – kglr Jun 25 '18 at 8:19
  • $\begingroup$ @ThadeuFreitasFilho You are free to edit you question at every time (there should be a small gray link saying "edit" below your post). $\endgroup$ – Henrik Schumacher Jun 25 '18 at 8:37
2
$\begingroup$
Module[
 {
  names = {"GS", "MS", "BAC"},
  dates = {{2000, 1, 1}, {2000, 1, 20}},
  filename = "spreadsheet.xls",
  data
  },
 data = Map[
   Transpose[
     Prepend[FinancialData[#, 
       dates], {# <> " date", # <> " value"}]] &, names];
 File@Export[filename, Transpose[Flatten[data, 1]]]
 ]

Mathematica graphics

$\endgroup$

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.