Yes, I wasn’t happy at all with the previous code so I changed it. It improved in processing time, coming down to 13 minutes and 59 seconds to aggregate all the files into a sole one (tough it increased size up to 2.62GB). Moreover, I have modified the structure so it’ll be easier to introduce the data into a database. Now the new file is divided into 4 (CSV) columns: movieid, userid, rating, date.
Here’s the VBA code:
Sub GroupData()
Dim T As Date
T = Now
Dim N As Double
Dim Text1 As String
Dim Text2 As String
Dim Text3 As String
Open “C:\Netflix\training_set.txt” For Output Access Write As #1
For N = 1 To 17770
Open “C:\Netflix\training_set\mv_00″ & Format(N, “00000″) & “.txt” For Input Access Read As #2
‘For the first line.
Input #2, Text1, Text2, Text3
Print #1, N & “,” & Right(Text1, Len(Text1) - (Len(CStr(N)) + 2)) & “,” & Text2 & “,” & Left(Text3, 10)
‘For the rest of lines.
Do While Not EOF(2)
Input #2, Text1, Text2
Print #1, N & “,” & Right(Text3, Len(Text3) - 11) & “,” & Text1 & “,” & Left(Text2, 10)
Text3 = Text2
Loop
Almost from my starting knowledge on economics…, well, from my starting knowledge on academical economics (actually, economics is in the air from the first time we have to make a decision!), I have been using IDEAS as my preferred platform for knowledge base, complementing it with Econlit, from the American Economic Association. It was a surprise for me when, after being using for a while IAOR (the equivalent in OR for Econlit) for getting info on OR published papers, yesterday I found my Or-equivalent for IDEAS: Optimization Online, from the Mathematical Programming Society.
Well, it’s taken quite a long time, some letters to the ORMS editor, quite a few posts in OR-related blogs, all from well-behaved operations researchers wanting to spread the gospel (”The Science of Better“). Now it’s for real; a new user, INFORMSpromotion, has appeared on the YouTube universe. Is that you mate?.
When analysing investment projects someone has to deal with the golden question “is it going to be profitable?”, followed by, “when?”. There are so many ways of answering these questions, but at the moment I’m going to focus on 3, very briefly:
1) Net Present Value (NPV).-
Get all the cash flows the project is going to generate (money invested, money spent, money earned), choose a reference interest rate (i.e. weighted average cost of capital, risk-free interest rate…), discount’em to present, and add’em up. If this value is positive then the project is worthwhile.
2) Internal Rate of Return (IRR).-
It is the interest rate that makes the NPV equal zero (IRR | NPV=0). If this rate is above your reference interest rate, then the project will have a positive NPV. Both measures are complementary, and both accept at the same time the project, rejecting the project at the same time, as well.
3) Pay-back Period.-
It is the time that takes the project to pay itself, so we’re talking from a cash-flow perspective exclusively. So, for instance, if the value is 2.25 years, that means the project will start generating profit after 2 years and a quarter.
When calculating this period I like to first discount all the cash-flows using the reference interest rate, so $1 is $1 for every flow (you know the first principle in finance: how much?-when?).
For easing the calculations I’ve prepared a spreadsheet to help. You can download it from the link below.
