-
January 30th, 2013, 03:46 AM
#1
Getting data in to C++ from excel, any help would be greatly appreciated
Hi there,
I am very very new to C++. A bit of background. I have been writing in excel vba for large number crunching, and the code is now taking quite a while to run. A friend of mine suggested i start writing in C++, so i read up on it. And downloaded Code:Blocks.
My VBA Code is:
Code:
Sub arrayss()
Dim NameArray As Variant
Dim datarray As Byte
Dim DirectionArray As Variant
Dim WinArr As Variant
Dim Results As Variant
Dim iColumns As Long
Dim jRows As Long
Dim a As Long
Dim b As Long
Dim c As Long
Dim q As Long
Dim tradevalue As Long
Dim noofrowsWin As Long
Dim noofcolumnsWin As Long
Dim cell1position As Long
Dim cell2position As Long
Dim cell3position As Long
Dim cell4position As Long
Dim cell1value As Long
Dim cell2value As Long
Dim cell3value As Long
Dim cell4value As Long
Dim y As Long
Dim z As Long
'Range("AN:BBB").Delete
Application.Calculation = xlCalculationManual
dataarray = ThisWorkbook.Names("Data1").RefersToRange.Value
NameArray = ThisWorkbook.Names("NameArray").RefersToRange.Value
DirectionArray = ThisWorkbook.Names("DirectionArray").RefersToRange.Value
cell1position = Sheets("Test").Cells(2, 3)
cell2position = Sheets("Test").Cells(3, 3)
cell3position = Sheets("Test").Cells(4, 3)
cell4position = Sheets("Test").Cells(5, 3)
cell1value = Sheets("Test").Cells(7, 3)
cell2value = Sheets("Test").Cells(8, 3)
cell3value = Sheets("Test").Cells(9, 3)
cell4value = Sheets("Test").Cells(10, 3)
tradevalue = Sheets("Test").Cells(12, 3)
jRows = 1
iColumns = 1
a = 3
b = 1
y = 0
z = 0
i = 1
j = 1
Sheets("Final").Cells(3, 1) = "=Counta(2:2)-1"
Sheets("Final").Cells(1, 2) = "=COUNTA(A:A)"
noofcolumnsWin = ((Sheets("Final").Cells(3, 1)) - 1) ^ 2 - (Sheets("Final").Cells(3, 1) - 1)
noofrowsWin = Sheets("Final").Cells(1, 2) + 1
ReDim Results(1 To 500, 1 To 16)
For q = 1 To UBound(dataarray, 2) - 1
For c = 1 To UBound(dataarray, 2) - 1
If c = q Then
GoTo labelend3
End If
For jRows = 1 To UBound(dataarray, 1) - cell4position
If IsEmpty(dataarray(jRows, q)) Or DirectionArray(jRows, 1) = 15 Or DirectionArray(jRows, 1) = 45 Then
ElseIf dataarray(jRows + cell1position, c) = cell1value And dataarray(jRows + cell2position, c) = cell2value And dataarray(jRows + cell3position, c) = cell3value And dataarray(jRows + cell4position, c) = cell4value Then ' cell check
If tradevalue = dataarray(jRows, q) Then ' win or lose
y = y + 1
Else
z = z + 1
End If
End If
a = a + 1
labelend:
Next
If z = 0 Then
z = 1
End If
If y = 0 Then
y = 1
End If
If y > 20 And y / z > 5 Then
Results(i, j) = NameArray(1, q)
Results(i, j + 1) = NameArray(1, c)
Results(i, j + 2) = y
Results(i, j + 3) = z
Results(i, j + 4) = y / z
Results(i, j + 5) = cell1position
Results(i, j + 6) = cell2position
Results(i, j + 7) = cell3position
Results(i, j + 8) = cell4position
Results(i, j + 9) = ""
Results(i, j + 10) = cell1value
Results(i, j + 11) = cell2value
Results(i, j + 12) = cell3value
Results(i, j + 13) = cell4value
Results(i, j + 14) = ""
Results(i, j + 15) = tradevalue
i = i + 1
End If
y = 0
z = 0
a = 3
b = b + 1
''' PUT IN HERE THE TEST FOR COUNTING 1's and zeros or below lol
''''
labelend3:
Next
a = 3
labelend1:
Next
Sheets("Test").Select
Range("an:an").End(xlDown).Offset(1, 0).Select
a = ActiveCell.Row
Range(Range("an" & a & ""), Range("an" & a & "").Offset(500, 16)).Value = Results
'Range(Range("m1"), Range("m1").Offset(10000, 16)).Value = Results
'Range(Range("g1"), Range("g1").Offset(noofrowsWin - 1, noofcolumnsWin - 1)).Value = WinArr
'Range(Range("g1"), Range("g1").Offset(noofrowsWin - 1, noofrowsWin - 1)).Value = Application.Transpose(WinArr)
Application.Calculation = xlCalculationAutomatic
End Sub
Ultimately i would like to recode this to C++, but my first and probably silly question is how do i get the data from Excel to use in C++. I was thinking either to put the data in 3 csv files and convert into three Arrays in C++. Or maybe create a library of the data in C++. Ultimately it is speed i am looking for, so before i start recoding i wanted to start with the best way.
Does that make sense?
The data is like this in excel: (don't know how to create a table)
ABC DCA GFE THE
15 0 1 0 1
30 1 0 1 0
45 1 1 0
00 0 0 1
15 1 1 0
15 1 0 0 1
15 0 1 1 1
30 1 0 0
45 1 1 1
So the headers would be in one array, the 15's, 30's etc would be in another array and the 1's and 0's and Empty ( i need it to record an empty cell) would be in another array
Rgds
Surreall
PS and thankyou for your time
-
January 30th, 2013, 06:18 AM
#2
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Originally Posted by Surreall
Hi there,
I am very very new to C++. A bit of background. I have been writing in excel vba for large number crunching, and the code is now taking quite a while to run. A friend of mine suggested i start writing in C++, so i read up on it. And downloaded Code:Blocks.
C++ is not a trivial language. You should learn very basics first before even thinking about "speed".
Second, C++ isn't like VBA or some other language, where you have a lot of firewalls and escape hatches when you make a mistake. You make a mistake in VBA such as an out of bounds access, you get a nice error message. You make the same type of mistake in C++, you may not get any error, and your program seems to run, but with that fault in it that you don't know about.
Ultimately it is speed i am looking for, so before i start recoding i wanted to start with the best way.
Honestly, that whole idea of trying to recode something in C++ without knowing C++ is IMO wrought with all sorts of problems. Again, C++ is not a trivial language, it has a lot of nuances that the beginner is not aware of, and requires experience to understand how to write code that is not only efficient, but maintainable.
There are tons of CSV parsers written in C++, but you need to understand C++ to use them.
Regards,
Paul McKenzie
-
January 30th, 2013, 06:31 AM
#3
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Originally Posted by Paul McKenzie
C++ is not a trivial language. You should learn very basics first before even thinking about "speed".
Second, C++ isn't like VBA or some other language, where you have a lot of firewalls and escape hatches when you make a mistake. You make a mistake in VBA such as an out of bounds access, you get a nice error message. You make the same type of mistake in C++, you may not get any error, and your program seems to run, but with that fault in it that you don't know about.
Honestly, that whole idea of trying to recode something in C++ without knowing C++ is IMO wrought with all sorts of problems. Again, C++ is not a trivial language, it has a lot of nuances that the beginner is not aware of, and requires experience to understand how to write code that is not only efficient, but maintainable.
There are tons of CSV parsers written in C++, but you need to understand C++ to use them.
Regards,
Paul McKenzie
I understand i need to learn C++ first
But not to worry i have been reading up about reading from csv file. And have written code that reads all the values from the csv file, still a long way to go. Was just wondering what would be quick way of doing it for future reference as the data is getting big quick. But as you say i need to learn C++ first. I will go away and learn it. Sorry to bother you with silly questions, my mistake
Rgds
Surreall
-
January 30th, 2013, 06:31 AM
#4
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Originally Posted by Paul McKenzie
C++ is not a trivial language. You should learn very basics first before even thinking about "speed".
Second, C++ isn't like VBA or some other language, where you have a lot of firewalls and escape hatches when you make a mistake. You make a mistake in VBA such as an out of bounds access, you get a nice error message. You make the same type of mistake in C++, you may not get any error, and your program seems to run, but with that fault in it that you don't know about.
Honestly, that whole idea of trying to recode something in C++ without knowing C++ is IMO wrought with all sorts of problems. Again, C++ is not a trivial language, it has a lot of nuances that the beginner is not aware of, and requires experience to understand how to write code that is not only efficient, but maintainable.
There are tons of CSV parsers written in C++, but you need to understand C++ to use them.
Regards,
Paul McKenzie
I understand i need to learn C++ first
But not to worry i have been reading up about reading from csv file. And have written code that reads all the values from the csv file, still a long way to go. Was just wondering what would be quick way of doing it for future reference as the data is getting big quick. But as you say i need to learn C++ first. I will go away and learn it. Sorry to bother you with silly questions, my mistake
Rgds
Surreall
-
January 30th, 2013, 08:11 AM
#5
Re: Getting data in to C++ from excel, any help would be greatly appreciated
So do you need to read CSV files or directly from Excel? If it's CSV, mentioning Excel is irrelevant, so first define your problem properly.
If you're using Visual C++, you can use ODBC and the CRecordset class to read directly, but as has been mentioned, you'll have a really steep learning curve.
-
January 30th, 2013, 08:16 AM
#6
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Originally Posted by Surreall
Ultimately i would like to recode this to C++, but my first and probably silly question is how do i get the data from Excel to use in C++. I was thinking either to put the data in 3 csv files and convert into three Arrays in C++. Or maybe create a library of the data in C++. Ultimately it is speed i am looking for, so before i start recoding i wanted to start with the best way.
There are three possibilities.
1. Loose coupling. You create a program in C++ that reads some files and processes the data in them. You can generate the input files with VB or whatever. This is the easiest option to implement, but it requires data exchange via disk and a fixed format for the data.
2. Run C++ DLL from VBA. You can create a C++ DLL that exports a function that does the data processing. You call this function from VBA and pass it the required data.
3. Run from C++. You can read the data from an Excel file in your C++ program. This requires some interop with Excel, or to use a library which can read Excel files.
Depending on how complex your data processing is, you may be able to achieve the first option with only a limited knowledge of C++. However, you need to master the basics well in order to be able to write highly efficient programs.
Cheers, D Drmmr
Please put [code][/code] tags around your code to preserve indentation and make it more readable.
As long as man ascribes to himself what is merely a posibility, he will not work for the attainment of it. - P. D. Ouspensky
-
January 31st, 2013, 05:00 AM
#7
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Originally Posted by D_Drmmr
There are three possibilities.
1. Loose coupling. You create a program in C++ that reads some files and processes the data in them. You can generate the input files with VB or whatever. This is the easiest option to implement, but it requires data exchange via disk and a fixed format for the data.
2. Run C++ DLL from VBA. You can create a C++ DLL that exports a function that does the data processing. You call this function from VBA and pass it the required data.
3. Run from C++. You can read the data from an Excel file in your C++ program. This requires some interop with Excel, or to use a library which can read Excel files.
Depending on how complex your data processing is, you may be able to achieve the first option with only a limited knowledge of C++. However, you need to master the basics well in order to be able to write highly efficient programs.
Many thanks for your help.
I have written some code now....and the reading in doesnt actually take long less than half a second. So all the time is going to be taken up with anaylising the arrays i have created. So not too worried about it.
Anyways here is where i have got to now, i did make functions initally to do the each array build, but it didnt make any difference in time. So i left them now in the main body.
The part that takes a little while, although it is gone from approx 6 seconds in vba to around 0.1 seconds in C++ (very very very chuffed about c++). I would still like to get this quicker if possible.
So any suggestions for speeding up the following code would be greatly appreciated:
Code:
#include <fstream>
#include <iostream>
#include <sstream>
#include <string>
using namespace std;
int main()
{
// this code finds the number of rows in a csv file
ifstream data("Data1.csv");
string line;
int rows = 0;
while (getline(data,line,'\n'))
{
rows++;
}
//cout << "Counted " << rows << " rows." << endl;
data.close();
// this code finds the number of rows in a csv file
ifstream data1("Data1.csv");
string line1;
int columns = 0;
getline(data1,line1);
stringstream ss(line1);
string field;
while (getline(ss,field,','))
columns++;
//cout << "Counted " << columns << " columns." << endl;
data.close();
/////This populats the dataarray with data1.csv crap
int dataarray[rows][columns];
ifstream data2("Data1.csv");
string line2;
int rows1= 0;
while (getline(data2,line2,'\n'))
{
stringstream bb(line2);
string field1;
int columns1 = 0;
while (getline(bb,field1,','))
{
istringstream buffer(field1);
int value = 2;
buffer >> value;
dataarray[rows1][columns1] = value;
columns1++;
}
rows1++;
}
//cout << "Datarray element, " << dataarray[3][0] << endl;
data.close();
// do the same for directionarray
int directionarray[rows];
ifstream direction("direction.csv");
string line3;
int rows2 = 0;
while (getline(direction,line3, '\n'))
{
istringstream buffer1(line3);
int value1 = 0;
buffer1 >> value1;
directionarray[rows2] = value1;
rows2++;
}
//cout << "Directionarray element, " << directionarray[0] << endl;
// do the same for headerarray
//int headerarray[columns];
string headerarray[columns];
ifstream header("Header.csv");
string line4;
int columns2 = 0;
while (getline(header,line4, ','))
{
headerarray[columns2] = line4;
columns2++;
}
//cout << "Headerarray element, " << headerarray[36] << endl;
//i have now created arrays, dataarray, directionarray & headerarray with correct elements
int indicator1position = 4;
int indicator2position = 5;
int indicator3position = 6;
int indicator4position = 7;
int indicator1value = 1;
int indicator2value = 1;
int indicator3value = 1;
int indicator4value = 1;
int trade1position = 4;
int trade2position = 5;
int trade3position = 6;
int trade4position = 7;
int trade1value = 1;
int trade2value = 1;
int trade3value = 1;
int trade4value = 1;
int tradevalue = 1;
int numberofcells = 4;
float y = 0;
float z = 0;
//string results[50000][18];
//for (trade1position = 4;trade1position < 12;trade1position++){
float resultsarraynumbers[3000][20];
string resultsarraywords[3000][20];
int resultsrow=0;
int resultscolumn=0;
//for (trade1position = 4;trade1position<13;trade1position++)
//{
for (trade2position = 1;trade2position<9;trade2position++)
{
for (int q = 0;q < columns - 1; q++)
{
for (int c = 0;c < columns - 1; c++)
{
if (q==c) {} else
{
for (int jRows = 0;jRows < rows - 1; jRows++)
{
if (dataarray[jRows][q]==2 || directionarray[jRows]==15 || directionarray[jRows]==45){}
else if (numberofcells == 4)
{ if (dataarray[jRows + indicator1position][c]==indicator1value && \
dataarray[jRows + indicator2position][c]==indicator2value && \
dataarray[jRows + trade1position][q]==trade1value && \
dataarray[jRows + trade2position][q]==trade2value)
{
if (tradevalue == dataarray[jRows][q])
{y++;} else {z++;}
}
}
}
if(y==0){y = 1;} else if (z==0){z = 1;}
if ( y/z > 1.5 && y > 10 )
{
resultsarraynumbers[resultsrow][resultscolumn] = y/z;
resultsarraynumbers[resultsrow][resultscolumn +1] = y;
resultsarraynumbers[resultsrow][resultscolumn +2] = z;
resultsarraynumbers[resultsrow][resultscolumn +3] = indicator1position;
resultsarraynumbers[resultsrow][resultscolumn +4] = indicator2position;
resultsarraynumbers[resultsrow][resultscolumn +5] = trade1position;
resultsarraynumbers[resultsrow][resultscolumn +6] = trade2position;
resultsarraynumbers[resultsrow][resultscolumn +7] = numberofcells;
resultsarraynumbers[resultsrow][resultscolumn +8] = indicator1value;
resultsarraynumbers[resultsrow][resultscolumn +9] = indicator2value;
resultsarraynumbers[resultsrow][resultscolumn +10] = trade1value;
resultsarraynumbers[resultsrow][resultscolumn +11] = trade2value;
resultsarraynumbers[resultsrow][resultscolumn +12] = tradevalue;
resultsarraywords[resultsrow][resultscolumn] = headerarray[q];
resultsarraywords[resultsrow][resultscolumn + 1] = headerarray[c];
resultsrow++;
}
y = 0;
z = 0;
}
}
}
}
//}
cout << "Results number, y/z=" << resultsarraynumbers[0][0] << endl;
cout << "Results number, y=" << resultsarraynumbers[0][1] << endl;
cout << "Results number, z=" << resultsarraynumbers[0][2] << endl;
cout << "Results trade, " << resultsarraywords[0][0] << endl;
cout << "Results indicator, " << resultsarraywords[0][1] << endl;
cout << "Results, y= " << y << endl;
return 0;
}
As far as i can figure the bottle neck is here:
Code:
if (dataarray[jRows][q]==2 || directionarray[jRows]==15 || directionarray[jRows]==45){}
else if (numberofcells == 4)
{ if (dataarray[jRows + indicator1position][c]==indicator1value && \
dataarray[jRows + indicator2position][c]==indicator2value && \
dataarray[jRows + trade1position][q]==trade1value && \
dataarray[jRows + trade2position][q]==trade2value)
{
if (tradevalue == dataarray[jRows][q])
{y++;} else {z++;}
}
}
By the way i am loving C++ for its quickness, great stuff. And thanks for the help previously. I know my code is butchered but i have had one day of learning it so far so bear with me please
Rgds
Surreall
-
January 31st, 2013, 05:51 AM
#8
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Originally Posted by Surreall
I have written some code now....and the reading in doesnt actually take long less than half a second. So all the time is going to be taken up with anaylising the arrays i have created. So not too worried about it.
What exactly are you trying to accomplish with the arrays? There are probably better ways to handle the code so that it is maintainable.
Here is the issue I have with your code.
1) You have hard-coded magic numbers. What significance is 2, 15, 45, etc.?
2) This is not legal C++:
Code:
int dataarray[rows][columns];
//..
string headerarray[columns];
You cannot declare arrays using a variable as the number of rows and columns. What you are probably using is some extension of C++, not ANSI C++. You are more than likely using gcc or some compiler based on gcc. If you set the compiler switches to ANSI for that compiler, you will see that your code will fail to compile. It will definitely not compile with any version of Visual C++. Since it is an extension, you now need to read up on all the quirks and caveats of using such an extension.
The std::vector<T> class is used by standard C++ to declare a dynamic array. This works across all ANSI C++ compilers, is standard, is guaranteed to be on every ANSI C++ compiler, is documented on how it must work, etc.
3)
Code:
resultsarraynumbers[resultsrow][resultscolumn] = y/z;
resultsarraynumbers[resultsrow][resultscolumn +1] = y;
resultsarraynumbers[resultsrow][resultscolumn +2] = z;
resultsarraynumbers[resultsrow][resultscolumn +3] = indicator1position;
resultsarraynumbers[resultsrow][resultscolumn +4] = indicator2position;
resultsarraynumbers[resultsrow][resultscolumn +5] = trade1position;
resultsarraynumbers[resultsrow][resultscolumn +6] = trade2position;
resultsarraynumbers[resultsrow][resultscolumn +7] = numberofcells;
resultsarraynumbers[resultsrow][resultscolumn +8] = indicator1value;
resultsarraynumbers[resultsrow][resultscolumn +9] = indicator2value;
resultsarraynumbers[resultsrow][resultscolumn +10] = trade1value;
resultsarraynumbers[resultsrow][resultscolumn +11] = trade2value;
resultsarraynumbers[resultsrow][resultscolumn +12] = tradevalue;
If you know the layout of the data, then you define a single struct and create an array of that struct instead of a two dimenisonal array.
Code:
#include <vector>
struct MyData
{
int y;
int z;
int indicator1position;
//...
};
typedef std::vector<MyData> AllMyData;
//...
AllMyData mData( number_of_data_elements );
mData[resultsrow].indicator1position = indicator1.position;
//...etc.
This eliminates the +1, +2, +3, etc. magic numbers.
Regards,
Paul McKenzie
Last edited by Paul McKenzie; January 31st, 2013 at 06:02 AM.
-
January 31st, 2013, 06:33 AM
#9
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Thats a lot to take in, thankyou for your time.
I am using Code::Blocks editor and on the compiler settings menu, it had automatically selected GNU GCC Compiler. Which compiler should i chose, there are over 30 different types. Is gcc bad? I don't mean bad, but i presume i want to be coding in basic c++? (sorry i am a little confused)
for the magic numbers 2, 15, and 45.
What i am acheiving with the arrays, is comparing corresponding 1's and 0's in each column of dataarray.
or matrix of dataarray example:
011
110
101
Eg. if element[1][1] = 1 and element[2][1] = 1 & if element[0][0] = 1, the set (as above) y = 1. Adding in the magic numbers ignore all of this if the corresponding direction array is equal to 15 or 45
In the main dataarray, it is filled with 0's, 1's and 2's. On each iteration i want to ignore it when the dataarray == 2. The same with 15 and 45 in the direction array. I am only interested in the other ones. I cannot delete them as i need the rest of the 0's and 1's that are in the other corresponding rows.
I know i am not explaining too well its hard to get ya head round it
Rgds
Surreall
-
January 31st, 2013, 07:46 AM
#10
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Okay after lots of searching, got the -ANSI now. And as you said it doesnt like variable length for array
-
January 31st, 2013, 08:20 AM
#11
Re: Getting data in to C++ from excel, any help would be greatly appreciated
you have 4 nested 4 loops.
From a quick glance, it doesn't look like the innermost code has any obvious shortcuts or way to speed things up (much).
If you want even more speed, then the trick now is to figure out a better method for achieving the same results rather than finding a few tweaks here and there.
Without knowing what your code actually tries to accomplish, there's not much we can do there to help though.
q and c both iterating over columns with an exception if q and c are identical makes me believe you are probably doing things that can be removed.
-
January 31st, 2013, 08:37 AM
#12
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Originally Posted by OReubens
you have 4 nested 4 loops.
From a quick glance, it doesn't look like the innermost code has any obvious shortcuts or way to speed things up (much).
If you want even more speed, then the trick now is to figure out a better method for achieving the same results rather than finding a few tweaks here and there.
Without knowing what your code actually tries to accomplish, there's not much we can do there to help though.
q and c both iterating over columns with an exception if q and c are identical makes me believe you are probably doing things that can be removed.
Sounds fair enough. With the q and c if you look at my little 3x3 matrix above. I compare column1(ie q) with the second and third column (ie c). I then compare column 2 (q) with the first and third column (ie c) and lastely compare column3 (q) with the first and second column(c).
The actual array has 39 columns, so lots and lots of iterations
Rgds
Surreall
-
January 31st, 2013, 10:14 AM
#13
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Here is the updated code:
Code:
#include <fstream>
#include <iostream>
#include <sstream>
#include <string>
#include <vector>
using namespace std;
int main()
{
/////This populats the dataarray with data1.csv crap
int dataarray[6077][37];
ifstream data2("Data1.csv");
string line2;
int rows1= 0;
while (getline(data2,line2,'\n'))
{
stringstream bb(line2);
string field1;
int columns1 = 0;
while (getline(bb,field1,','))
{
istringstream buffer(field1);
int value = 2;
buffer >> value;
dataarray[rows1][columns1] = value;
columns1++;
}
rows1++;
}
// do the same for directionarray
int directionarray[6077];
ifstream direction("direction.csv");
string line3;
int rows2 = 0;
while (getline(direction,line3, '\n'))
{
istringstream buffer1(line3);
int value1 = 0;
buffer1 >> value1;
directionarray[rows2] = value1;
rows2++;
}
//cout << "Directionarray element, " << directionarray[0] << endl;
// do the same for headerarray
//int headerarray[columns];
string headerarray[37];
ifstream header("Header.csv");
string line4;
int columns2 = 0;
while (getline(header,line4, ','))
{
headerarray[columns2] = line4;
columns2++;
}
//i have now created arrays, dataarray, directionarray & headerarray with correct elements
int indicator1position = 4;
int indicator2position = 5;
int indicator1value = 0;
int indicator2value = 1;
int trade1position = 4;
int trade2position = 5;
int trade1value = 1;
int trade2value = 1;
int tradevalue = 1;
int numberofcells = 4;
int q = 0;
int c = 0;
float y = 0;
float z = 0;
//float resultsarraynumbers[5000][18];
vector<float> resultsarraynumbers;
vector<string> resultsarraywords;
int jRows = 0;
int rows = 6077;
int columns = 37;
int rows3 = 0;
for (indicator1position = 4;indicator1position < 13; indicator1position++)
{
for (indicator2position = 1 + indicator1position;indicator2position < 9+ indicator1position; indicator2position++)
{
for (trade1position = 4;trade1position < 13; trade1position++)
{
for (trade2position = 1 + trade1position;trade2position < 9 + trade1position; trade2position++)
{
for (q = 0;q < columns - 1; q++)
{
for (c = 0;c < columns - 1; c++)
{
if (q==c) {} else
{
for (jRows = 0;jRows < rows - 50; jRows++)
{
if (dataarray[jRows][q]==2 || directionarray[jRows]==15 || directionarray[jRows]==45){}
else if (numberofcells == 4)
{ if (dataarray[jRows + indicator1position][c]==indicator1value && \
dataarray[jRows + indicator2position][c]==indicator2value && \
dataarray[jRows + trade1position][q]==trade1value && \
dataarray[jRows + trade2position][q]==trade2value)
{
if (dataarray[jRows][q]==tradevalue)
{y++;} else {z++;}
}
}
}
float t = (y/z);
if(y==0){y = 1;} else if (z==0){z = 1;}
if ( t > 4 && y > 10 )
{
//cout << t << endl;
resultsarraynumbers.push_back(t);
resultsarraynumbers.push_back(y);
resultsarraynumbers.push_back(z);
//rows3++;
resultsarraywords.push_back (headerarray[q]);
resultsarraywords.push_back (headerarray[c]);
}
}
y = 0;
z = 0;
}
}
}
}
}
}
//for (vector<int>::iterator it = v.begin(); it!=v.end(); ++it) {
//printout
// cout << *it << ", " << endl;
//}
cout << "Results number, t=" << resultsarraynumbers[0] << endl;
cout << "Results number, y=" << resultsarraynumbers[1] << endl;
cout << "Results number, z=" << resultsarraynumbers[2] << endl;
cout << "Results trade, " << resultsarraywords[0] << endl;
cout << "Results indicator, " << resultsarraywords[1] << endl;
cout << "Results, y= " << y << endl;
cout << "Results, z= " << z << endl;
return 0;
}
Which runs pretty darn well, albeit takes around 6 minutes to finish. And i still have one more for loop to add :/
But question is i would now like to output the results into a csv file. But stupidly i have coded it so that resultsarraynumbers is a float and resultsarraywords is a string. (only way i could get it to work)
But i need to list the first 3 elements of the float and then the first 2 elements of the string in first line of a csv file.
Then on the next line in the csv i need to put the next 3 of float followed by the next two of elements etc till the end of the vectors.
Is there a way to combine the vectors in this manner? And then export that to csv file?
Rgds
Surreall
-
January 31st, 2013, 12:20 PM
#14
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Perhaps the easiest way is to define a struct to contain the 3 numbers and the two strings and then declare a vector of this struct. The example below illustrates a possible method
Code:
#include <vector>
#include <string>
#include <fstream>
using namespace std;
//define structure to contain what is required for each line in the csv file
struct results {
double t, y, z;
string q, c;
};
int main()
{
vector<results> resultsarray;
results oneres;
//Open output file for the csv
ofstream ofs("results.csv");
//Put some data into the result vector for test
for (int l = 1; l < 20; l++) {
oneres.t = l + 3.4;
oneres.y = l + 3.5;
oneres.z = l + 3.6;
oneres.q = "s1";
oneres.c = "s2";
resultsarray.push_back(oneres);
}
//Write result vector to output csv file
for (vector<results>::iterator it = resultsarray.begin(); it != resultsarray.end(); ++it) {
ofs << it->t << "," << it->y << "," << it->z << "," << it->q << "," << it->c << endl;
}
ofs.close();
return 0;
}
I note that you are now using fixed size arrays and that there is no error detection in case your data contains more rows/columns than expected. As c++ doesn't as standard provide bounds checking for arrays, may I suggest that when you populate these arrays you check that the bounds aren't exceeded?
-
January 31st, 2013, 01:13 PM
#15
Re: Getting data in to C++ from excel, any help would be greatly appreciated
Originally Posted by Surreall
Which runs pretty darn well, albeit takes around 6 minutes to finish.
First, are you running an optimized build, not a debug build? If you're running a debug build, it should not be used for timing purposes. Always time a fully optimized build.
6 minutes to do something means you have either:
1) a huge amount of data (millions of records), or
2) the code you wrote is ineffficient, or
3) you're running a debug build, or
4) some combination of the above 3 items.
Regards,
Paul McKenzie
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|