Getting data in to C++ from excel, any help would be greatly appreciated
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 4 1234 LastLast
Results 1 to 15 of 54

Thread: Getting data in to C++ from excel, any help would be greatly appreciated

Hybrid View

  1. #1
    Join Date
    Jan 2013
    Posts
    27

    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

  2. #2
    Join Date
    Apr 1999
    Posts
    27,427

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Surreall View Post
    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

  3. #3
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Paul McKenzie View Post
    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

  4. #4
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Paul McKenzie View Post
    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

  5. #5
    GCDEF is offline Elite Member Power Poster
    Join Date
    Nov 2003
    Posts
    12,088

    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.

  6. #6
    Join Date
    Jul 2005
    Location
    Netherlands
    Posts
    2,013

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Surreall View Post
    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

  7. #7
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by D_Drmmr View Post
    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

  8. #8
    Join Date
    Apr 1999
    Posts
    27,427

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Surreall View Post
    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 05:02 AM.

  9. #9
    Join Date
    Jan 2013
    Posts
    27

    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

  10. #10
    Join Date
    Apr 1999
    Posts
    27,427

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Surreall View Post
    What i am acheiving with the arrays, is comparing corresponding 1's and 0's in each column of dataarray.
    I know this was an earlier post of yours, but I'll respond:

    I don't really understand your explanation with the indices you're using the example. Why [1][1], [2][1], and [0][0]? Unless this is a typo, don't you mean (row, col) [0][1], [1][1], [2][1]? If not, maybe you should first rearrange the data so that you're not jumping around all over the place like that, since there really is no coherence to the small example you gave. Again, if your original explanation is a typo, it would be good if you corrected it.
    I know i am not explaining too well its hard to get ya head round it
    If you attempted to explain in general terms what it is you're really doing, then you would get many more responses with much more efficient and maintainable ways of doing what you're doing. Some of those methods may not have even have been considered by yourself, such as using bits, bitsets, etc. to represent 1,0, and "don't care" columns.

    Maybe that's the problem -- you're looking at this in a "hard-coded" fashion, and not seeing the general pattern. Once you see the general pattern, then you have all the power of the C++ algorithms and containers to do much of this grunt work. This is what nuzzle was referring to earlier, and that is the algorithm. You are using the worst case loop of n^2 times when actually you may only need to loop log(n) times, speeding up the whole process logarithmically (which would be much better than tweaking a loop variable here or there).

    But only if we have a clear understanding of the process in general terms could we really look at and suggest a better approach.

    In your code, I see a lot of repeated or close to repeated code. If you're writing 10 lines of code that is the same except for an index or a variable, or the variable type is different, etc. then that code should be refactored into a general approach to get rid of the redundancies. A well-written C++ program shouldn't have 2 or more sections that look exactly the same except for a variable or two.
    Code:
    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;
    Why aren't these arrays themselves?
    Code:
    int indicatorposition[] = {4,5,6,7};
    int indicatorvalue[] = {1,1,1,1};
    int tradeposition[] = {4,5,6,7};
    int tradevalue[] = {1,1,1,1};
    Just this one change can make the difference between seeing the problem in a general approach, as opposed to the hard-coded, ad-hoc approach you seem to be taking. Now you're forced to think in terms of arrays and indices and the relationship between indicatorposition[x], indicatorvalue[x], etc. where x is some number.

    Regards,

    Paul McKenzie

  11. #11
    Join Date
    Jan 2013
    Posts
    27

    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

  12. #12
    Join Date
    Apr 2000
    Location
    Belgium (Europe)
    Posts
    3,877

    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.

  13. #13
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by OReubens View Post
    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

  14. #14
    Join Date
    Apr 1999
    Posts
    27,427

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Surreall View Post
    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).
    Forget about C++ for a moment and think about a generic approach. That's why I asked about the magic numbers -- those numbers should be data that drives the generic approach, not hardcoded.

    Too many times, persons write the code so that it becomes inflexible, just to get something working. I never take that approach. Right now, you are using 2, 15, 45, etc. But what if you get the same setup, but the columns are now 3, 13, and 27? Are you going to continually change the code to accommodate those new numbers?

    For example, you have a matrix with n columns, and for each iteration up to n, you want to compare columns that are not n.
    Code:
    int currentColumn = someValue;
    for ( int i = 0; i < numTotalColumnsInArray; ++i )
    {
        if ( i == currentColumn )
           continue;
        if ( CompareMatrix(currentColumn, i) == whatever )
        {
            // do whatever
        }
    }
    I whittled down your explanation to those few lines.

    Regards,

    Paul McKenzie
    Last edited by Paul McKenzie; January 31st, 2013 at 12:24 PM.

  15. #15
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Code:
    int currentColumn = someValue;
    for ( int i = 0; i < numTotalColumnsInArray; ++i )
    {
        if ( i == currentColumn )
           continue;
        if ( CompareMatrix(currentColumn, i) == whatever )
        {
            // do whatever
        }
    }
    Yeah thats pretty much it, should i change the two for loops (q and c) for something similar to this?

    As for the 15's and 30's etc, i cannot forsee needing to change them. Same with the 1's or 0's or 2's or headers. At least not for the next 6 months.
    Last edited by Surreall; January 31st, 2013 at 01:20 PM.

Page 1 of 4 1234 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center