Formatting FO

Hi Experts,

I have one file which downloads data from web and formatted. Need to modification in code for I guess two lines. in context of this question.
in above link
Above is my existing code need to add more steps which below listed.
 Step 1 Add this text in Cell P1 "Ticker,Date,Open,High,Low,Close,Volume,OI"
 Step 2 Add this formula in cell P2 "=IF(OR(A2="FUTSTK",A2="FUTIDX"),B2&" "&C2&","&TEXT(O2,"DD-MMM-YY")&","&F2&","&G2&","&H2&","&I2&","&K2&","&M2,IF(OR(A2="OPTIDX",A2="OPTSTK"),B2&" "&C2&" "&D2&" "&E2&","&TEXT(O2,"DD-MMM-YY")&","&F2&","&G2&","&H2&","&I2&","&K2&","&M2,""))" and formula drop down till end.

I need to change Step 1 from Ticker,Date,Open,High,Low,Close,Volume,OI to <TICKER>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>


& Step 2 From TEXT(O2,"DD-MMM-YY") - TEXT(O2,"DD-MMM-YY") to TEXT(O2,"YYYYMMDD") - TEXT(O2,"YYYYMMDD")

Above link is very 1st phase of this formatting but after that many thing amended and added.
in attached I am refereeing Cell A6 & A7 Button which is Future and Option.

Thanks
Incoporated-Download-File-V23.xlsm
LVL 8
Naresh PatelFinancial AdviserAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naresh PatelFinancial AdviserAuthor Commented:
Basically Needed output in both the instance bit different. see attached .txt files.

Thanks
F-O-Old.txt
F-O-New.txt
0
gowflowCommented:
ok here are some questions:

1) In the formula there is nothing such as
TEXT(O2,"DD-MMM-YY") - TEXT(O2,"DD-MMM-YY")  ....

but rather
TEXT(O2,""DD-MMM-YY"")

there is no substraction !!! ( - ) between 2 dates.

2) In the routine CreateTXT this is where I suppose we need to make the change it affect all of
"Future"                     ---> Button A6
"Options"                   ---> Button A7
"Future & Options"  ---> Button A10
Are all 3 subject to the change ?

gowflow
0
Naresh PatelFinancial AdviserAuthor Commented:
Yes there is no subtraction between two dates .I had separated for show that there is two instance in formula.
if this the case TEXT(O2,""DD-MMM-YY") then change to TEXT(O2,""YYYYMMDD")

Yes Chang to all 3

Thanks
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

gowflowCommented:
ok fine here is the solution but you need to be careful and here is how to implement.

1) Make a new copy of the workbook and call it V24
2) Open VBA and doubleclick on Module aStartFO
3) Now on the right pane where you see code displayed at the bottom of this right window you will see 2 small icons. the right one is clicked (this means that it will show All the Subs in that module.) You need to click on the left one so it displays 1 sub at a time. From now on we will proceed this way as it is much easier to make replacements then to list the whole code in the module.
4) After you have clicked on the left icon on the top right of the window you have a drop down that once clicked you can select a sub select CreateTXT sub and it will list the code of this sub. It happens that this is a Function.
5) Select this whole function like all the code for that function and DELETE it.
6) Paste the below new function AFTER any END Sub or End Function in this module.

Function CreateTXT(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

'---> Title In P1
'Old Title
'"Ticker,Date,Open,High,Low,Close,Volume,OI"

'Change on 3/9/2015 to
'<TICKER>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>
WS.Range("P1") = "<TICKER>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>"

'---> Formula in P2 and down
'P2 "=IF(OR(A2="FUTSTK",A2="FUTIDX"),B2&" "&C2&","&TEXT(O2,"DD-MMM-YY")&","&F2&","&G2&","&H2&","&I2&","&K2&","&M2,IF(OR(A2="OPTIDX",A2="OPTSTK"),B2&" "&C2&" "&D2&" "&E2&","&TEXT(O2,"DD-MMM-YY")&","&F2&","&G2&","&H2&","&I2&","&K2&","&M2,""))
WS.Range("P2:P" & MaxRow).Formula = "=IF(OR(A2=""FUTSTK"",A2=""FUTIDX""),C2&"" ""&B2&"",""&TEXT(O2,""YYYYMMDD"")&"",""&F2&"",""&G2&"",""&H2&"",""&I2&"",""&K2&"",""&M2,IF(OR(A2=""OPTIDX"",A2=""OPTSTK""),C2&"" ""&B2&"" ""&D2&"" ""&E2&"",""&TEXT(O2,""YYYYMMDD"")&"",""&F2&"",""&G2&"",""&H2&"",""&I2&"",""&K2&"",""&M2,""""))"

Select Case sType
        Case "Future & Options"
            '---> Sort as per Col P Ascending
            WS.Range("A1:P" & MaxRow).Sort key1:=WS.Range("P1"), order1:=xlAscending, Header:=xlYes, MatchCase:=no
            
            
        Case "Future"
            '---> Sort as per Col E Ascending
            WS.Range("A1:P" & MaxRow).Sort key1:=WS.Range("E1"), order1:=xlAscending, Header:=xlYes, MatchCase:=no
            WS.Range("A1:P" & MaxRow).AutoFilter Field:=5, Criteria1:="<>xx"
            Set Rng = WS.Range("A2:P" & MaxRow).EntireRow.SpecialCells(xlCellTypeVisible)
            Rng.Delete
            Set Rng = Nothing
            WS.ShowAllData
            MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
            
        Case "Options"
            '---> Sort as per Col E Ascending
            WS.Range("A1:P" & MaxRow).Sort key1:=WS.Range("E1"), order1:=xlAscending, Header:=xlYes, MatchCase:=no
            WS.Range("A1:P" & MaxRow).AutoFilter Field:=5, Criteria1:="=xx"
            Set Rng = WS.Range("A2:P" & MaxRow).EntireRow.SpecialCells(xlCellTypeVisible)
            Rng.Delete
            Set Rng = Nothing
            WS.ShowAllData
            MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row

            
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

CreateTXT = sWBName

Set WS = Nothing
Set Rng = Nothing

Exit Function

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

Open in new window


7) SAVE and exit the workbook.
8) Give it a try.

gowflow
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naresh PatelFinancial AdviserAuthor Commented:
End Of Phase One. There Is More Of Back Testing And Prediction On Phase 2. Totally Based On Excel Only. It Will Be Highly Appreciated If You Be A Part Of It. May Be From Next Week.

Thanks
0
gowflowCommented:
Your welcome.
gowflow
0
Naresh PatelFinancial AdviserAuthor Commented:
Knock Knock need little modification in existing incorporated download file ..may I ?
0
gowflowCommented:
Sure let me know what is the link of the question
gowflow
0
Naresh PatelFinancial AdviserAuthor Commented:
may I now?
0
Naresh PatelFinancial AdviserAuthor Commented:
Here It Is.

Thanks
0
Naresh PatelFinancial AdviserAuthor Commented:
please help me out on above link.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.