Solved

Data Generator

Posted on 2016-07-26
20
71 Views
Last Modified: 2016-07-29
Hi Experts,

This FollowUp Question which being solved by Sir.GowFlow.

1)i had some problem while fetching data for button "Index" it being leads to error most of the time ,,,need some changes in code i.e. data not available for specific date then go for next date.

2) Need Change in data formation as in current setting it download and produce data in below formatCurrent FormateSome ChangesOriginal .CSV file
See Attached
Incoporated-Download-File-Final-V27.xlsm
NSE-Index-Importer.xlsm
0
Comment
Question by:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
  • 2
20 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 41730213
Can you please clarify what is the second file for ?
NSE-Index-Importer.xlsm

gowflow
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 100 total points
ID: 41730333
In the NSD-Index_Importer.xlsm workbook, to replace the dashes with zeros, add this line of code before the WkBk.SaveAs command:
Cells.Replace What:="-", Replacement:="0", LookAt:=xlWhole, SearchOrder:=xlByRows

Open in new window


Regards,
-Glenn
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41731165
@Sir.GowFlow...

NSE-Index-Importer.xlsm ....just to recall what we had done in Index Button....thats it nothing more.

Thanks
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 8

Author Comment

by:Naresh Patel
ID: 41731167
@Mr.Glenn Ray.

i looking changes in WB Incorporated Download file.xlsm

Thanks
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41731711
Sure, but still, you should add that line of code so that the CSV files won't have dashes anymore.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41731741
yes I did that...

Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 41732005
Ok is the macro already in the original workbook or this is a newer version ??? why have a separate one ?
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41732254
Macro already there in original one..


Thanks
0
 
LVL 30

Accepted Solution

by:
gowflow earned 400 total points
ID: 41732839
ok here is it.

This procedure has been changed to cater for new requirements.

Function CreateTXTEquity(WS As Worksheet, sWBName As String, MaxRow As Long, sType As String) As String
On Error GoTo ErrCreateTXT
Dim Rng As Range
Dim rRow As Range
Dim I As Long
Dim dTmp As String
Dim vTmp

'---> Disable Trace
With Application
     .ScreenUpdating = False
End With

Select Case sType
        Case "Equity BSE"
            '---> Title In P1
            '"<TICKER>,<NAME>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>"
            WS.Range("P1") = "<TICKER>,<NAME>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>"
            
            '---> Formula in P2 and down
            'P2 "=A2&","&B2&","&TEXT(D2,"YYYYMMDD")&","&E2&","&F2&","&G2&","&H2&","&L2"
            WS.Range("P2:P" & MaxRow).Formula = "=A2&"",""&B2&"",""&TEXT(D2,""YYYYMMDD"")&"",""&E2&"",""&F2&"",""&G2&"",""&H2&"",""&L2"

            '---> Sort as per Col P Ascending
            WS.Range("A1:P" & MaxRow).Sort key1:=WS.Range("P1"), order1:=xlAscending, Header:=xlYes, MatchCase:=no
           
          Case "Equ"
            '---> Titel in P1
            '"<TICKER>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>"
            WS.Range("P1") = "<TICKER>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>"

            
            '---> Formula in P2 and down
            'P2 "=A2&","&TEXT(K2,"YYYYMMDD")&","&C2&","&D2&","&E2&","&F2&","&I2"
            WS.Range("P2:P" & MaxRow).Formula = "=A2&"",""&TEXT(K2,""YYYYMMDD"")&"",""&C2&"",""&D2&"",""&E2&"",""&F2&"",""&I2"

            '---> Sort as per Col P Ascending
            WS.Range("A1:P" & MaxRow).Sort key1:=WS.Range("P1"), order1:=xlAscending, Header:=xlYes, MatchCase:=no
        
        Case "Index"
            '---> Titel in P1
            '"<TICKER>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>"
            WS.Range("P1") = "<TICKER>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<P/E>,<P/B>"
            
            '---> Fix Date in B2 as it is DD/MM/YY it should be MM/DD/YY
            For I = 2 To MaxRow
                WS.Range("B" & I) = Replace(WS.Range("B" & I), "-", "/")
                vTmp = Split(WS.Range("B" & I), "/")
                dTmp = vTmp(1) & "/" & vTmp(0) & "/" & vTmp(2)
                WS.Range("B" & I) = dTmp
                vTmp = vbEmpty
                dTmp = vbEmpty
            Next I
                
            '---> Replace - by 0 in the whole file
            '     Replace ' ' space by 0 in Col K and L
            WS.Cells.Replace what:="-", Replacement:=0, lookat:=xlWhole
            WS.Range("K2:L" & MaxRow).Replace what:="", Replacement:=0, lookat:=xlWhole
            
            '---> Formula in P2 and down
            'If Val(Mid(WS.Range("B2"), 1, InStr(1, WS.Range("B2"), "/") - 1)) <= 12 Then
                'P2 =A2&","&TEXT(B2,"YYYYDDMM")&","&C2&","&D2&","&E2&","&F2&","&I2"
            '    WS.Range("P2:P" & MaxRow).Formula = "=A2&"",""&TEXT(B2,""YYYYDDMM"")&"",""&C2&"",""&D2&"",""&E2&"",""&F2&"",""&I2"
            'Else
                'P2 =A2&","&TEXT(B2,"YYYYMMDD")&","&C2&","&D2&","&E2&","&F2&","&I2&","&K2&","&L2"
                WS.Range("P2:P" & MaxRow).Formula = "=A2&"",""&TEXT(B2,""YYYYMMDD"")&"",""&C2&"",""&D2&"",""&E2&"",""&F2&"",""&I2&"",""&K2&"",""&L2"
            'End If
            
            '---> Sort as per Col P Ascending
            WS.Range("A1:P" & MaxRow).Sort key1:=WS.Range("P1"), order1:=xlAscending, Header:=xlYes, MatchCase:=no

            
End Select

'---> Copy Col P to A and set Rng the new Range
WS.Range("P2:P" & MaxRow).Copy
WS.Range("P2").PasteSpecial xlPasteValues

WS.Range("A:O").EntireColumn.Delete
Set Rng = WS.Range("A1:A" & MaxRow)

'---> Crete the TXT File
Open sWBName For Output As #1
For Each rRow In Rng
    Print #1, rRow.Value
Next rRow

Close #1

'WS.SaveAs Filename:=sWBName, FileFormat:=xlCSV

CreateTXTEquity = sWBName

Set WS = Nothing
Set Rng = Nothing

'---> Enable Trace
With Application
     .ScreenUpdating = True
End With


Exit Function

ErrCreateTXT:
CreateTXTEquity = ""
Err = 0
Resume
End Function

Open in new window


What has been modified is added P/E and P/B header and data
Fixed issue of - to be replaced by 0
Fixed issue of empty cells to be replaced by 0 in Col P/E and P/B

Kindly test the attached workbook and advise your comments.
gowflow
Incoporated-Download-File-Final-V28.xlsm
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41733008
File Seems Perfect if i download data for few days but if i go for whole one year it wont download for index button....Dont know why? i.e. if i put start date 1 jan 2016 to 31 Jan 2016...it will download but if i go for 1 jan 2016 to 28 jul 2016...it wont work.

Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 41733727
I tested it, it works perfectly the only thing when you open the workbook just before you click on Index button make sure that row 14 shows most probably you were showing further down rows this is why you had the impression nothing was happening.

So scroll till row 14 shows then activate the index button.
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41734076
That is not the case .....i had clicked for both duration I. e.  one month and 12 months..... One month worked well and 12 months not..... I waited for 10 minutes but nothing happens for longer duration.....

And this problem not happen after changing codes for PE PB but it happens in original code too....

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41734110
i had tried in MS Excel 2016 better i will try on MS Excel 2010 and get back to you. On my way to office....will test and let you know..

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41734175
Very Strange .....When i use this file in my laptop - which have Intel processor 2.16GHz with 2 GB RAM - Excel 2010.....it is working perfect..But when same file i use in my Desktop - i5 Processor with 4 GB RAM - Excel 2016 ....for long duration it wont work (Only good for short duration)....bit confused it is problem with Office version or with my machine problems ...

Thanks
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 41734176
Perfect & Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 41734484
No clue. Maybe 2016 strange behavior ??
Try the long version from the first time not after a trial of an other date like open the workbook and put the long time and try.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41734489
I guess 2016 is main cause as I can't see trace while downloading other files too.... but I get data at the end..... May I ask follow up?
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41734490
I guess 2016 is main cause as I can't see trace while downloading other files too.... but I get data at the end..... May I ask follow up?
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41734491
I guess 2016 is main cause as I can't see trace while downloading other files too.... but I get data at the end..... May I ask follow up?
0
 
LVL 30

Expert Comment

by:gowflow
ID: 41734498
ok
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question