0
$\begingroup$

I would like to pull 3 attributes (Open, Close, Volume) for a variety of stock symbols. I would like to pull the stock data within a specific range and export it to an excel spreadsheet. I would also like to append the next stock ticker and its attributes to the bottom of the former.

So far I have the following code:

filename = "Data.xls";
data = FinancialData[
   "GE", {"Open", "Close"}, {{2000, 1, 1}, {2021, 1, 1}}];
V = data // Normal;
Export[filename, V];

Several problems are easy to see: Data for Open and Close are saving in separate tabs (I do not know why), adding additional ticker symbols forces all ticker data for a symbol into the same row in the same tab (again I do not know why), I am getting a string e.g.

Quantity[50.29999923706055, "USDollars"]

when all I want is the numerical piece. Finally, I am not getting the headers for each attribute as I am looking for.

I would really appreciate some assistance here. I have looked at other solutions as Creating a Stock Dataset but it doesn't quite give me what I am looking for and my attempts to change it to meet my needs have failed.

Improve this question
$\endgroup$
1
  • 1
    $\begingroup$ Some excellent and exhaustive approaches can be found in the Q&A you linked. Could you include your code that was inspired by those approaches and that failed? It might be an easy fix, and faster than somebody having to re-invent the wheel. $\endgroup$
    – MarcoB
    Mar 28, 2021 at 4:38

1 Answer 1

Reset to default
2
$\begingroup$

Very easy. The reason you're not getting what you want is partly how you're downloading the data from Wolfram's servers (as a TimeSeries rather than as a list you can then combine how you want) and partly because you're not then transforming it correctly before exporting.

This does the job:

(*Download the data in legacy format rather than new TimeSeries format*) 

filename = "Data.xls";
raw = QuantityMagnitude@FinancialData[
    "GE", {"Open", "Close"}, {{2000, 1, 1}, {2021, 1, 1}}, 
    Method -> "Legacy"];
    
    (*Convert the raw dates to ISO Date format*)

dateList = DateString[#, "ISODate"] & /@ raw[[1, All, 1]];
    
    (*Combine the datelist with the open and close series in an association and then create a column-oriented dataset object for easy check and export*)

V = Transpose@
      Dataset@<|"Date" -> dl, "Open" -> raw[[1, All, 2]], 
        "Close" -> raw[[2, All, 2]]|>
    
    (*Export*)

Export[filename, V]

Hope that helps!

Improve this answer
$\endgroup$

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