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 at 4:38
1
$\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$
0
$\begingroup$

I used this website to get financial data to Excel. It is pretty convenient since they have a plugin to import data without leaving Excel.

Improve this answer
$\endgroup$
1
  • 1
    $\begingroup$ While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review $\endgroup$
    – bbgodfrey
    Sep 13 at 2:12

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.