wtorek, 27 października 2015

Excel: Iterate through x, return y's to table

My own humble attempts in VBA:

Enter one x and record 3 outputs:
Sub simulate()
    Dim x, xStart, xEnd, xStep As Single
    Dim i  As Integer


 With Worksheets("Simulate")
     
     i = 1
     xStart = .Range("f4").Value
     xEnd = .Range("g4").Value
     xStep = .Range("h4").Value
    
    
    .ListObjects("tblSim").DataBodyRange.Delete 'Shift:=xlToUp
    
    
    For x = xStart To xEnd Step xStep
        Worksheets("ProductList").Range("D10").Value = x
        With .Range("a1")
        .Offset(i, 0).Value = x
        .Offset(i, 1).Value = Worksheets("Analysis").Range("N19").Value
        .Offset(i, 2).Value = Worksheets("Analysis").Range("N20").Value
        .Offset(i, 3).Value = Worksheets("Analysis").Range("N21").Value
        End With
        i = i + 1
    Next x
    
End With
    
End Sub


Enter two variables and record one output, in a table suitable for a 3d plot (eg. heat map).

Sub simulate()
    Dim x, xStart, xEnd, xStep as Double
    Dim i, j  As Integer
    Dim y, yStart, yEnd, yStep as Double

On Error Resume Next
  
 With Worksheets("Simulate")
 
'Get iteration parameters

     xStart = .Range("h4").Value
     xEnd = .Range("i4").Value
     xStep = .Range("j4").Value
     
     yStart = .Range("h5").Value
     yEnd = .Range("i5").Value
     yStep = .Range("j5").Value
     
   'Clear old data
       '.ListObjects("tblSim").DataBodyRange.Delete 'Shift:=xlToUp
    .Range("A10:L100").ClearContents
     
'Generate x axis
    
    i = 1
    
    For x = xStart To xEnd Step xStep
            .Range("a20").Offset(0, i).Value = x
            i = i + 1
    Next x
    
'Get results
   
    j = 1
    For y = yStart To yEnd Step yStep
        .Range("a20").Offset(j, 0).Value = y
     
        i = 1
        
        Worksheets("ProductList").Range("D11").Value = y
        
        For x = xStart To xEnd Step xStep
            Worksheets("ProductList").Range("D10").Value = x
            .Range("a20").Offset(j, i).Value = Worksheets("ProductList").Range("N21").Value
            i = i + 1
        Next x
        
     j = j + 1
        
     Next y
     
  End With
    
End Sub


What about more dimensions? I think it would be the best occasion to feed an array in vba (then... how to save it?) or feed a cube in MS SQL. About saving/loading an array: http://stackoverflow.com/questions/18114527/load-multidimensional-vba-array-from-disk http://www.vb-helper.com/howto_read_write_binary_file.html

poniedziałek, 26 października 2015

Telegraph on Elections in Poland

http://www.telegraph.co.uk/news/worldnews/europe/poland/11954733/Who-are-Polands-victorious-Law-and-Justice-party-and-what-do-they-want.html

piątek, 23 października 2015

Late night photography readings: superresolution

Stacking for superresolution:
Very simple, interesting and promising technology:
http://petapixel.com/2015/02/21/a-practical-guide-to-creating-superresolution-photos-with-photoshop/
https://en.wikipedia.org/wiki/Superresolution
http://xcorr.net/2011/09/15/video-super-resolution-coming-to-consumer-software/
http://www.gizmag.com/super-resolution-weizmann-institute/23486/

Free Stanford material - Intro To Statistical Learning

Read on LinkedIn group: https://www.linkedin.com/pulse/stanford-open-course-statistical-learning-very-good-who-lin-sun
and R-Bloggers http://www.r-bloggers.com/in-depth-introduction-to-machine-learning-in-15-hours-of-expert-videos/

Visited: http://www-bcf.usc.edu/~gareth/ISL/ and helped myself with the handbook. See also: http://statweb.stanford.edu/~tibs/ElemStatLearn/

Signed up and accessed the Course at Stanford: https://lagunita.stanford.edu/courses/HumanitiesScience/StatLearning/Winter2014/courseware/f6eb0a2902904c6e8f74a2c15833d1ad/dbbe6095fa55431b8253590e2658dc3e/

Collection of datasets: https://cran.r-project.org/web/packages/ISLR/index.html

Another course based on the book: http://www.alsharif.info/#!iom530/c21o7 

Thank you Lin Sun for this great info!

OT. Also free. Old versions of Rapid Miner. Somehow it is difficult for me to localize and download  the old free versions at authors' site: http://www.win.tue.nl/~rmans/RapidMiner/doku.php?id=wiki:installation

czwartek, 22 października 2015

Excel vba: Best macro to remove empty rows from listobject tables.

Best reliable, highly recommended: 'http://www.excelforum.com/excel-programming-vba-macros/847446-auto-resize-shrink-table-on-an-active-sheet.html

Sub ert()
Dim lo As ListObject
'Application.ScreenUpdating = False
On Error Resume Next    'if SpecialCells(4) not exists
For Each lo In ActiveSheet.ListObjects
    With lo
        .ListColumns(1).Range.SpecialCells(4).Resize(, .ListColumns.Count).Delete shift:=xlUp
    End With
Next lo
'Application.ScreenUpdating = True
End Sub

wtorek, 20 października 2015

trx toys for my birthday?

http://www.youkits.com/

Leanpub bookshop: free books - R, statistics, data mining!

I highly recommend https://leanpub.com/
  • If you are poor, most books are free of charge. Initial price is a suggestion.
  • If you wish to contribute, move the slider to select the contribution. See how much author earns!
  • Buy the book when it is developed - author gets encouragement and early income!
  • Some books accompany free online courses, eg. coursera.
  • Select epub, mobi, pdf....
I selected a book R Programming for Data Science by Roger D. Peng

PS/OT This reminded me about a wonderful book about programming and C# fundamentals: http://www.introprogramming.info/english-intro-csharp-book/ 

Learn R, learn C#, it costs nothing!

Where to learn R... index 2015

Learning R: Index of Online R Courses, October 2015: http://blog.revolutionanalytics.com/beginner-tips/

niedziela, 18 października 2015

Sunday Coffee: workflow editors in R like MS Data Tools

I am looking at various data workflow plugins and addins for R that would be equivalent to superb Microsoft Data Tools. (https://msdn.microsoft.com/en-us/library/mt204009.aspx My complaint: these tools are superb as experienced during job training, but not yet working in my MS SQL 2014 Developer at home). That is why I am looking for alternatives and so far I have identified the following:

1. Red-R
http://decisionstats.com/2010/04/20/using-red-r-r-with-a-visual-interface/

2. EF-Prime
http://www.ef-prime.com/products/ranalyticflow_en/quicktour.html

wtorek, 6 października 2015

Disapponted with Microsoft, lost money, time and stomach ache...(Data Mining, SSAS, Data Tools )

Short version:
MS SQL 2014 Developer's Version plus Data Tools (SSAS, SSIS) plus Excel Data Mining Plugin equals lots of hopes and big disappointment involving loss of valuable time and money. 

=========

These will be mild words after some anger reduction meditation.



I was preparing my laptop for Business Intel and Data Mining training. I had  already lost one night  for installing recently purchased MS SQL 2014 Developers Version over unknown issues and error messages  giving no clue. Then I found on some blog there are two or three files left over by earlier version of MS SQL, innocent setup files left over after an uninstall, preventing the new version from installing. I hoped it was over.

I intended to use my computer instead of the virtual environment offered during the BI course, so that I could bring home and experiment with the examples. It appeared that neither Visual Studio Data Tools for SSAS and SSIS, nor the Excel Data Mining plugin will work.
I tried another night to resolve the issue, reading posts, with no luck. I discovered I am not alone with questions and problems that noone could successfuly address. I also studied professional forums only to learn that now I should install Data Tools separately and try Excel plugin for data mining version ver. 2012 SP 2 with no guarantee for a success.
The instructor of the Data Mining course tried to assist me but after a couple of minutes gave up and proposed I should rather work on a virtual machine. I tried to update Windows with all the current patches, uninstall and then  reinstall Visual Studio and MS SQL Server. With no luck

The software is now  installed but it is not functional as expected. The Excel data mining plugin will not work and the Data Tools are not present in MS Visual Studio.

I also tried to contact MS Tech Support, describing the problem. I went through automatic machines to reach a human. He requested I should run a scan for integrity of the system. There were no errors. He offered to call next day which I did, I prepared a description of my problem and requested an email where I can send it... but I was not given the option on the next day. I also tried to fill in the form and describe the issue in as much detail as possible (environment, software versions, error messages). I pasted it in to learn there was a length limit for text and my message was cut after several sentences. I was really pissed off with Microsoft.

I am loosing my patience. I have alreday purchased a product that is not functional... It does not do what it promises, what was expected.  Gosh! 

PS: More reading:
Google:
data mining add in for microsoft excel
"object reference not set to an instance of an object"

https://social.msdn.microsoft.com/Forums/en-US/8d1d3c3b-be6a-4a3e-9fc0-7f97be291432/data-mining-excel-2013-error-object-reference-not-set-to-an-instance-of-an-object?forum=sqldatamining

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3abbf7d2-5b3a-47ab-b742-bb38835f0492/error-with-excel-data-mining-and-windows-81?forum=sqldatamining

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f6e52e58-6a8a-4b96-8f2d-e1278777a825/excel-data-mining-addin-error-object-not-set-to-an-instance-of-object?forum=sqldatamining

https://social.msdn.microsoft.com/Forums/windowsdesktop/en-US/86a42e38-051a-4d85-bb73-601b35492eb6/object-reference-not-set-to-an-instance-of-an-object?forum=sqldatamining



https://social.msdn.microsoft.com/Forums/sqlserver/en-US/65d11506-b005-44ba-b9e5-4d7053374505/sql-server-data-tools-for-visual-studio-2013?forum=ssdt

http://blog.nwcadence.com/sql-server-data-tools-clearing-up-the-confusion/

And so on... a lot of time and nerves lost by others. 


PS 2... a letter to Microsoft (but no address to send it - in Polish):
Szanowni Państwo,
W związku z moim uczestnictwem w kursie dotyczącym Data Mining w środiwisku MS SQL chcialem skorzystać z możliwości nauki własnej. Zakupiłem więc Excel Microsoft Office Professional oraz MS SQL Server 2014 wersje developerską.
Moje środowisko:
MS SQL 2014 Developer SP1 64bit
MS Office 2013  64bit
Windows 7 Profesional SP 1 64 bit
MS Data Mining Addin for MS Excel  ver 2012 sp2 64 bit
===========
Wystąpił u mnie problem:
"Object Reference not set to an instance of an object.


lub

Excel wyświetla komunikat że MS Data Mining Addin is causing errors
i proponuje jego wyłączenie.
===================
Dzwoniłem pod nr Pomocy Technicznej Microsoftu i polecono mi wykonać polecenie,

sfc /scannow
NIe zwróciło ono żadnych informacji o problemach

does not return nie zwraca żadnych naruszeń integralności.

Dostęp do danych ze strony MS SQL i dodatku   jest prawidłowo skonfigurowany (pod okiem trenera w czasie zajęć
z MS SQL Business Intelligence i Data Mining


=============
W sieci można znaleźć szereg zapytań o rozwiązanie tego problemu.
Występuje on prawdopodobnie dość często . Nie ma na nie odpowiedzi ze strony Microsoft ani innych użytkowników
Google:
data mining add in for microsoft excel, "object reference not set to an instance of an object" etc.