Click here to Login





                                                   Export to CSV the whole database including symbols

  1

0 1 2 - Next
Raja Gupta
2010-07-31 11:18:14



I am new user for this software. Is there a way to export to whole database file to ascii file to be further imported into an Oracle database. I know there is a way
to export to CSV file for every symbol. I want to dump the whole file includung symbols to a CSV file.

I am sorry if this should be very common knowledge to anyone using this software but I could not find the answer in the forum.
Thanks,



QuantShare
2010-08-02 06:24:29

  0

Best Answer
A whole copy of the database to a CSV file can be performed using the scripting language. Tools -> Script Editor.

Here is the script to perform this:

Symbol[] symbols = Symbols.GetSymbols();
System.Text.StringBuilder text = new System.Text.StringBuilder("");
for(int i=0;i < symbols.Length;i++)
{
Symbol sym = symbols[i];
HistoricalQuotes quotes = Quotes.GetHistoricalQuotes(sym.Name);
for(int j=0;j < quotes.Close.Length;j++)
{
text.AppendLine(sym.Name + ";" + quotes.Date[j].ToString("dd/MM/yyyy") + ";" + quotes.Close[j]+ ";" + quotes.Open[j] + ";" + quotes.High[j] + ";" + quotes.Low[j] + ";" + quotes.Volume[j]);
}
}

System.IO.File.WriteAllText(@"c:\quotes.csv", text.ToString());


- The last line reference the file.
- The format is: Symbol;Date;Close;Open;High;Low;Volume



Raja Gupta
2010-08-02 13:33:17

  0

Thanks much. What if the database , i am trying to convert to ascii, is a custom database instead of quotes database.


QuantShare
2010-08-02 14:14:44

  0

You must change
HistoricalQuotes quotes = Quotes.GetHistoricalQuotes(sym.Name);
by
DatabasesData data = Databases.GetDatabasesData(sym.Name, "database name", "field name");
for(int j=0;j < data.Value.Length;j++)
{
text.AppendLine(sym.Name + ";" + data.Date[j].ToString("dd/MM/yyyy") + ";" + data.Value[j].ToString());
}



Raja Gupta
2010-08-02 14:49:17

  0

Thanks! it worked and I must say that the program is really fast. It dumped 42000 rows in less than 2 seconds.


ariel
2012-04-11 10:47:13

  0

I tried the script you suggested and got a few error msgs. Do you know if the script/functions have to be updated in any way? Thanks.


QuantShare
2012-04-12 06:41:00

  0

Ariel,

What error message are you getting?
Can you post the script here?

Also, do not forget to update ("database name" and "field name") with the appropriate database name and field name.



ariel
2012-04-12 07:44:20

  0

Thanks for your reply!

I am trying to export the entire custom database "earnings_cal" (all tickers and all fields). So I just copy-pasted the script you posted above and made the changes that you suggested. (btw, I wasn't sure what is the "field name" in the case of the earning_cal database, since I am interested in all fields. Also, am I supposed to leave the inverted commas for the filed and database names?). In any event, I get a few error msgs after running it. Here is the script and the error msgs I get:

SCRIPT:

Symbol[] symbols = Symbols.GetSymbols();
System.Text.StringBuilder text = new System.Text.StringBuilder("");
for(int i=0;i < symbols.Length;i++)
{
Symbol sym = symbols[i];
DatabasesData data = Databases.GetDatabasesData(sym.Name, "Custom", "earnings_cal");
for(int j=0;j < data.Value.Length;j++)
{
text.AppendLine(sym.Name + ";" + data.Date[j].ToString("dd/MM/yyyy") + ";" + data.Value[j].ToString());
}
}

System.IO.File.WriteAllText(@"c:\quotes.csv", text.ToString());



ERROR MSGS:

Error(s)
L0 C1378 : Expected ';'
L1 C1 : The list of attributes does not apply to the current context
L2 C9 : Expected ';'
L2 C31 : Expected ')'
L2 C35 : Expected ';'
L4 C1 : The list of attributes does not apply to the current context
L5 C1 : The list of attributes does not apply to the current context
L6 C9 : Expected ';'
L6 C34 : Expected ')'
L6 C38 : Expected ';'
L12 C30 : Invalid character
L0 C1369 : Expression has no effect
L0 C1378 : Variable 'symbols' has not been declared
L2 C9 : Variable 'i' has not been declared
L6 C9 : Variable 'j' has not been declared
L6 C17 : Variable 'data' has not been declared
L8 C1 : Variable 'text' has not been declared
L8 C17 : Variable 'sym' has not been declared




QuantShare
2012-04-12 08:22:10

  0

At the bottom the script editor, set "C#" instead of "JScript".

Also, set the database and field names here:
"Custom", "earnings_cal"




tomshortt
2012-12-10 14:03:07

  0

Is there a way to select just certain dates to export. Like from 1/1/2000 to 12/31/2004?

Thank you for this it has been very helpful. I just have one issue. The script crashes if I export more than 5 years from my US database with all of the tickers. This makes me create separate databases for each date period (ie, 2000 to 2005, etc).

Thanks



QuantShare
2012-12-10 14:10:00

  0

Yes, you can do that.

Here is how:

Symbol[] symbols = Symbols.GetSymbols();
System.Text.StringBuilder text = new System.Text.StringBuilder("");
for(int i=0;i < symbols.Length;i++)
{
Symbol sym = symbols[i];
DatabasesData data = Databases.GetDatabasesData(sym.Name, "Custom", "earnings_cal");
for(int j=0;j < data.Value.Length;j++)
{
double year = data.Date[j].Year;
if(year >= 2000 && year <= 2004) // Specify Period of data exporting
{
text.AppendLine(sym.Name + ";" + data.Date[j].ToString("dd/MM/yyyy") + ";" + data.Value[j].ToString());
}
}
}

System.IO.File.WriteAllText(@"c:\quotes.csv", text.ToString());




tomshortt
2012-12-10 15:21:22

  0

Thanks. For anyone else, I edited it to work with the original as follows:

Symbol[] symbols = Symbols.GetSymbols();
System.Text.StringBuilder text = new System.Text.StringBuilder("");
for(int i=0;i < symbols.Length;i++)
{
Symbol sym = symbols[i];
HistoricalQuotes quotes = Quotes.GetHistoricalQuotes(sym.Name);
for(int j=0;j < quotes.Close.Length;j++)
{
double year = quotes.Date[j].Year;
if(year >= 2000 && year <= 2004) // Specify Period of data exporting
{
text.AppendLine(sym.Name + ";" + quotes.Date[j].ToString("dd/MM/yyyy") + ";" + quotes.Close[j]+ ";" + quotes.Open[j] + ";" + quotes.High[j] + ";" + quotes.Low[j] + ";" + quotes.Volume[j]);
}
}
}

System.IO.File.WriteAllText(@"C:\quotes.csv", text.ToString());



tomshortt
2012-12-12 19:22:26

  0

I've no downloaded the last 10 days at the minute detail. How do I convert the above to export the date and time to a CSV? I tried below but didn't get any data for hours or minutes. I just got 00/00. Thanks

Symbol[] symbols = Symbols.GetSymbols();
System.Text.StringBuilder text = new System.Text.StringBuilder("");
for(int i=0;i < symbols.Length;i++)
{
Symbol sym = symbols[i];
HistoricalQuotes quotes = Quotes.GetHistoricalQuotes(sym.Name);
for(int j=0;j < quotes.Close.Length;j++)
{
double year = quotes.Date[j].Year;
if(year > 2011 && year <= 2012) // Specify Period of data exporting
{
text.AppendLine(sym.Name + ";" + sym.FullName + ";" + quotes.Date[j].ToString("MMM/dd/yyyy/HH/mm") + ";" + quotes.Close[j]+ ";" + quotes.Open[j] + ";" + quotes.High[j] + ";" + quotes.Low[j] + ";" + quotes.Volume[j]);
}
}
}

System.IO.File.WriteAllText(@"C:\quotes_USA_2012.csv", text.ToString());



QuantShare
2012-12-12 19:45:47

  0

Use the following date/time format:

"dd/MM/yyyy hh:mm"



tomshortt
2012-12-13 22:09:00

  0

Thanks. I have 15 days worth of quotes that have minute details. When I run the script below I only receive one record per day as follows:

A;Agilent Technologies Inc.;Dec/03/2012 12:00;37.92;38.6;38.9;37.88;2714500

So I get a record at noon and I'm not getting the intraday details. I can see the intraday details in my charts.

How do I adjust this to get the intraday details. Thanks Tom

Symbol[] symbols = Symbols.GetSymbols();
System.Text.StringBuilder text = new System.Text.StringBuilder("");
for(int i=0;i < symbols.Length;i++)
{
Symbol sym = symbols[i];
HistoricalQuotes quotes = Quotes.GetHistoricalQuotes(sym.Name);
for(int j=0;j < quotes.Close.Length;j++)
{
double day = quotes.Date[j].Day;
double month = quotes.Date[j].Month;
double year = quotes.Date[j].Year;
if(month >= 12 && day <=13 && year >= 2012) // Specify Period of data exporting
{
text.AppendLine(sym.Name + ";" + sym.FullName + ";" + quotes.Date[j].ToString("MMM/dd/yyyy hh:mm") + ";" + quotes.Close[j]+ ";" + quotes.Open[j] + ";" + quotes.High[j] + ";" + quotes.Low[j] + ";" + quotes.Volume[j]);
}
}
}

System.IO.File.WriteAllText(@"C:\quotes_USA_201212.csv", text.ToString());



QuantShare
2012-12-14 09:55:55

  0

The script reads EOD data (Quotes.GetHistoricalQuotes(sym.Name)). You should replace that by: Quotes.GetIntradayQuotes(sym.Name, 60)


Max Tower
2013-02-02 01:00:21

  0

How would I modify the above scripts to export all of the Yahoo dividend data? I tried the following, but got an error.

Symbol[] symbols = Symbols.GetSymbols();
System.Text.StringBuilder dividends = new System.Text.StringBuilder("");
System.IO.File.WriteAllText(@"c:\dividends.csv", dividends.ToString());

for(int i=0;i < symbols.Length;i++)
{
Symbol sym = symbols[i];

DatabasesData data = Databases.GetDatabasesData(sym.Name, "Custom", "dividend");
for(int j=0;j < data.Value.Length; j++)
{
dividends.AppendLine(sym.Name + "," + data.Date[j].ToString("dd/MM/yyyy") + "," + data.Value[j].ToString());
}
System.IO.File.WriteAllText(@"c:\dividends.csv", dividends.ToString());
dividends.Length = 0;

}

The error was:
Object reference not set to instance of object.



QuantShare
2013-02-02 11:07:01

  0

The issue is probably here:
Databases.GetDatabasesData(sym.Name, "Custom", "dividend");

It should be:
Databases.GetDatabasesData(sym.Name, "Type database name here", "Type field name here");



vernon
2013-04-29 16:15:08

  0

How would you modify above to just export all min data for just one stock?


QuantShare
2013-04-29 18:26:48

  0

Select "Data -> Edit Databases", select your stock then click on "Export".


No more messages
0 1 2 - Next




Reply:

No html code. URLs turn into links automatically.

Type in the trading objects you want to include: - Add Objects
To add a trading object in your message, type in the object name, select it and then click on "Add Objects"










QuantShare

Trading Items
Ticker Symbols List of the National Stock Exchange, NSE, India
Export Indicators Data - Custom Time Frame
Leading Index for the United States
Tweet Sentiment Index for the Forex and Stock Market
Update the Stock Exchange Associated With U.S. Stocks - Market Fi...

How-to Lessons
How to quickly select stocks based on the last value of a databas...
How to export trading data to a CSV file
How to import trading data from CSV files
How to quickly download the most recent EOD data for your stocks
How to display the number of notes per stock

Related Forum Threads
Create and Export each database for each market
How to import to database from a csv file?
Export database by industry list
How to export historical data for a list of symbols
Delete symbols from Database

Blog Posts
How to Create a List of Ticker Symbols
Different Ticker Symbols for Each Data Source
Ranking stocks based on their correlation with the S&P 500 Index
Sentiment Analysis: How to measure the sentiment score of your st...
How to download the history of dividend payments for stocks in th...









QuantShare
Product
QuantShare
Features
Create an account
Affiliate Program
Support
Contact Us
Trading Forum
How-to Lessons
Manual
Company
About Us
Privacy
Terms of Use

Copyright 2019 QuantShare.com
Social Media
Follow us on Facebook
Twitter Follow us on Twitter
Google+
Follow us on Google+
RSS Trading Items



Trading financial instruments, including foreign exchange on margin, carries a high level of risk and is not suitable for all investors. The high degree of leverage can work against you as well as for you. Before deciding to invest in financial instruments or foreign exchange you should carefully consider your investment objectives, level of experience, and risk appetite. The possibility exists that you could sustain a loss of some or all of your initial investment and therefore you should not invest money that you cannot afford to lose. You should be aware of all the risks associated with trading and seek advice from an independent financial advisor if you have any doubts.