Posts by A9192Shark

Re: trendline gaussian

Dave,

Quote from Dave.Kimble@Liz

Why did Hubbert use a probability function to fit a times series? It was a case of trying the easiest thing first.

Not previously knowing anything about this science I have just done a web search. I know I am being pedantic, but Hubbert did not use a probability distribution to fit a time series. He most likely used simple arithmetic to calculate the data and then plotted the points on a graph and draw a curve through them that had a 'characteristic' shape. No formulae have been given (that I can find). To fit the curve subsequent analyses have used a Gauss or Normal curve, but it is not a distribution, it is simply a mathematical equation that 'fits' the data.

The key words are 'probability distribution' vs 'curve fit' they are not the same.

In fact the drawings of his original curve suggest that a very wide stdev is required to get the wide curve at the top of the curve, but there are the problems that the tails are ill defined. Can it be considered that oil production had a specific start date? I suspect that it is a long way before the data that is being analysed.

I could keep going, because it intrigues me, but it will not help you solve your problem and it is not a primary issue for this forum (and I am sure there are many others more qualified in this subject to comment)!

However, on the use of R2 have a look at this article it gives an example of why simply searching for a function taht fits is not necessarily the best approach.

With respect to your problem I presume that you are doing an analysis for several data sets and will hen combine the curves to provide a revised estimate of the global or regional peak oil prediction. An interesting problem and one that does appeal to my analytical mind!

With respect to your question on developing the solver, have a look at the attached workbook. You should only edit coloured cells. Note that the formulae used for the curves are all filled down the coloured columns that correspond to the graph and the titles of the curves.

I have put starting values in that help the solver. It is clear that the solver has limitations, I had to do a lot of the adjustments manually, especially for the Gamma distribution.

Note that you can not modify a cell other than the cell holding a function, therefore the solver will always need to be run as a macro.

Good luck,

A.

Re: VBA code can't find Access Form

Ski 52,

Try me.[frmSubFrmName].Form.Recordsource

A.

G,

Having opened the template I note that the four boxes you refer to all have the expression RecordSetClone.RecordCount in them, eg:

=IIf([Print Invoice Time Subform].Form.[B]RecordsetClone[/B].RecordCount=0,0,[Print Invoice Time Subform].Form![Total Hours])

I did a search on the MS Help and found that these are blocked in Sandbox mode.

I have not come across Sandbox Mode before and can not advise on anything about it. If you do not get any further responses on this thread then open another thread asking about Sandbox mode.

HTH,

Alan.

Re: import data from asp page into ms-excel

Your subject and message contradict eachother. Do you want to Import or export?

Assuming that you mean import from a web page then:
Open the page in a web browser
Open Excel with a blank workbook
Select Data > Import External Data > New Web Query
Copy the URL from the address bar in your browser and paste it into the address bar of the query browser.

You should hopefully see the page in the query browser and alot of small arrows showing you the meta data areas that you can download. Click on the ones you want.

Click OK.

Note also that MS Excel already has a query for doing this that gets the data from MS Money web site.

To use this make a list of your ticker labels in a single column.
Select Data> Import External Data > Import Data and select the Stock quotes one.

You will hopefully be able to follow through from the wizard.

Good luck,

A.

Re: Excel object nesting

Do you mean can you lock a graph to a cell? For example can yousize a graph such that it is the size of a cell and then lock it in place?

If so then yes you can.

Place the object on the worksheet. Select the object by left clicking. Then press Alt and drag the pull handles to size the object. The pull handles will align to the rows and columns on the sheet. Now when the cell is resized the object will be resized.

HTH,

Alan.

Re: Relating the inventory to a specific location

Tai Chi,

Ceck your PMs, I have sent you my e-mail address. I think what you are asking is quite simple to implement (but perhaps difficult to explain). It will depend on how you have stored the data, but I think that we should be able to get a list of staff who have signed out a specific key (an the number that should be in the key locker).

Cheers,

Alan.

Re: Relating the inventory to a specific location

TaiChi,

Remove any reports and forms that do not relate to your problem. That will reduce the file size significantly.

If that does not work (or you have already done it) then PM me and we will sort out an e-mail for you to send it to.

A.

Re: Errors in sample database

Where do the errors occur?

A.

Re: trendline gaussian

Dave,

Unfortunately my company will not let me view your web site so I can not see the examples you have pointed us to.

The discussion on whether or not the missing SD is needed should be resolved by simply using teh NormDist function. You should use this for two reasons.
1. It is a clear function that others can easily see what you have done. It will not have the same limitations of approximations (even if the impact is limited on teh results).
2. Someone who looks at your worksheet may see the missing SD and question the validity of the analysis. Whilst you may be correct that teh scaling simply gets around the mathematical issues it does not get around the quality and confidence issues.

A.

Re: trendline gaussian

Dave,

I do not want to detract from your excitement, but are you using the normal distribution in a sensible manner?

What you have done is:
Assumed that the oil production is following a definable function f(x) where x is the year. You are trying to find that function, presumably to predict what the production would be in a given future year.

Firstly this is a time series not a distribution. You are therefore not plotting the normal or Gaussian disribution, but plotting the curve defined by the same equation. Why is this important? A distribution is related to probability of occurrence, you are analysing a time series. To assume a distribution would be to assume that the oil production next year could be 3 000 000. Clearly this is contrary to the time series.

I appreciate that you are trying to obtain a curve that best describes the data and would (presumably) allow a prediction of the future oil production.

A few boundaries exist for the curve. As the year gets larger we assume production decreases (this assumes that no significant new reserves are tapped into). Also the production can not be less than zero. Several curves spring to mind as possibly describing the data. However we must understand why we think the data fits and what is the real world reason for the data fitting that curve.

To undertake this analysis I would search for a comparitively simple curve. Have a look at the different types of trend lines that you can put on the graph. By allocating a 3rd order polynomial I think that you will probably get a much better fit. The question will then be "Why is a 3rd order poly the best fit to use?" It is not sufficient to say because it matches the data, we must understand why it matches the data.

I suggest therefore that you takeone of the following approaches.
1. Use the trend tool in Excel to examine the fit for many different types of curves as given in the chart tool.

2. Transform your data such that it would appear linear. This is fraught with problems for anything but an easy transformation and I would avoid this.

The key to understanding all of this is "Why does the data behave the way it does?" If you can not explain why production went up and then started to decrease then you have to be very careful of using a curve that may fit the data verywell now but is no good for forecasting.

I hope that the above helps, I do want to see your analysis work, but it must be valid.

Cheers,

Alan.

PS (Ithink, but need to check, that your equation for normal distribution is missing a division by the standard deviation. I have used the NormDist() function in Excel.)
A.

Re: trendline gaussian

Why are you doing this?

If what you are actually trying to do is find out whether or not some observations are from a particular distribution then you should be doing the correct statistical test.

For example. If you have 100 samples and you want to estimate the true mean and standard deviation of the population then we should be using a t-test (although if you have many more observations then we can simplify this).

To do what you are trying to do would require either a transformation of the data or some sort of iterative solution as suggested. Although you would then be able to calculate an r-sq value you would still need to test if it was a significant value. You could start to build a worksheet to do this, but the actual test is much easier.

If I can (time permitting) I will build a simple demo sheet.

A.

Re: Split address details

Try testing something like

if ucase(strX ) = strX then
do something because strX must all be uppercase.

A.

Re: Need help reducing execution time

Blush,

I have had another quick look. I am sure that this could be done witha few queries and then data output very quickly using a normal Access report. You appear to pass through one recordset (rsHead) and then add data into specific sections of the form from the appropriate tables.

Can you post the mdb structure? If you remove all data apart from perhaps one head and the associated data I am sure that we can get this working properly such that run ti e is a few seconds.

Why is the report output to a text file?

A.

Re: Need help reducing execution time

Not sure how much of an improvement you will get as it is difficult to estimate without knowing what the data that is being output looks like.

Here are some suggestions and modified code.

1. Reduce the number of If statements in FPut
2. Use Space rather than a while loop to add spaces to the numbers.
3. Only output to a file if a new line is to be written.

I am not sure if the way you have created a text file and then output to it is slower or faster than using the Writeln and Print statements.

Also, I suspect that the fastest way would be to create the data in a report that is formatted to print exactly as you want. Even if all you did was create a single field that has all of teh data in it.

Try this code and see if there is any improvement.

Note, you may also find it useful to add some code that prints the run time to the debug window. Then you can also start to see where the time is being taken by printing it out every now and then from different procedures.

Good luck,

A.

Option Compare Database
Option Explicit
Public fs, f
Public rsGeologReport As Recordset
Public i%
 
[B]Public strTextLine      As String[/B]


Public Function StartOutput() As Boolean
    Dim rsHEAD As Recordset
    Dim sHole As String
    Dim TableName$(1 To 5)
    Dim sSQL As String
    Dim rs As Recordset
     
    TableName(1) = "DEVIAT"
    TableName(2) = "HEAD"
    TableName(3) = "SIMGEO"
    TableName(4) = "ASSAY"
    TableName(5) = "GEOLOGREPORT"
     
    StartOutput = False 'set default return value to FALSE
    For i = 1 To 5
        If Not IsTableExist(TableName(i)) Then Exit Function 'Check for existence of tables
    Next i
    If IsFileExist(CurrentProject.Path & "\GEOLOG.GEO") Then Kill (CurrentProject.Path & "\GEOLOG.GEO") 'Delete GEOLOG.GEO if it exists - EEC 1/4/2006
    Set fs = CreateObject("Scripting.FileSystemObject") 'create text file object
    Set f = fs.CreateTextFile(CurrentProject.Path & "\GEOLOG.GEO", True) 'create text file
    Set rsHEAD = CurrentDb.OpenRecordset("HEAD") 'open table HEAD
    If rsHEAD.RecordCount = 0 Then Exit Function
    Set rsGeologReport = CurrentDb.OpenRecordset("GEOLOGREPORT") 'Open table GEOLOGREPORT
    CurrentDb().Execute "DELETE * FROM GEOLOGREPORT" 'Empty GEOLOGREPORT Table
     
[B]    strTextLine = ""[/B]
    rsHEAD.MoveFirst
    Do While Not rsHEAD.EOF
        rsGeologReport.AddNew 'Write data to GeologReport
        rsGeologReport("Suppressed") = True 'set default value
        rsGeologReport("HOLE") = rsHEAD("Hole")
        If rsHEAD("Suppress") = "N" Then 'Print hole data to file if "Supress" is set to "N"
            Call FPut("IDEN", 1, 24) 'Print head data about hole
            Call FPut(rsHEAD("Hole"), 1, 8)
            Call FPut(rsHEAD("Northing"), 3, 15, 2)
            Call FPut(rsHEAD("Easting"), 3, 16, 2)
            Call FPut(rsHEAD("Elev"), 3, 17, 2, True)
            Call FPut("S000", 1, 15) 'Print collar info
            Call FPut(rsHEAD("Depth"), 3, 8, 2)
            Call FPut(rsHEAD("AZIM"), 3, 16, 2)
            Call FPut(rsHEAD("DIP"), 3, 8, 2, True)
             
            sHole = rsHEAD("HOLE") 'Assign current HEAD->Hole to variable sHole
             
            TableName(2) = "SIMGEO1"
            TableName(3) = "SIMGEO2"
             
            For i = 1 To 4
                If i = 2 Or i = 3 Then sSQL = "SELECT * FROM [SIMGEO] WHERE Hole = '" & sHole & "';" Else sSQL = "SELECT * FROM [" & TableName(i) & "] WHERE Hole = '" & sHole & "';"
                Set rs = CurrentDb.OpenRecordset(sSQL) 'execute SQL and retrieve result
                Application.Run TableName(i), rs 'call appropriate sub
                rsGeologReport(TableName(i) & " LINES") = rs.RecordCount 'write number of records to GeologReport
                rs.Close
            Next i
             
            Call FPut("/END", 1, 24, , True) 'print end hole info
        End If
        rsGeologReport.Update 'Update (Flush) data to GeologReport table
        rsHEAD.MoveNext
    Loop
     
    rsHEAD.Close
    f.Close
    Set rsHEAD = Nothing
    Set f = Nothing
    Set fs = Nothing
     
    MsgBox ("OUTPUT PROCESS COMPLETED.")
     
    StartOutput = True
End Function
 
Sub FPut(s As Variant, Align%, nLen%, Optional nDec% [B]= 0[/B], Optional NewLine As Boolean [B]= False[/B])
     's: entry to print
     'Align: 1 = left, 2 = centre, 3 = right
     'nLen: column width on the output form (GEOLOG.GEO)
     'nDec: number of decimal places for entry to have
     'NewLine: whether to start a new line
     
    Dim sTemp As String
     
    If IsNull(s) Then s = ""
    sTemp = Trim(CStr(s)) 'convert s into string
    [B]Select Case nDec
        Case 0: sTemp = Format(sTemp, "0")
        Case 1: sTemp = Format(sTemp, "#.0")
        Case 2: sTemp = Format(sTemp, "#.00")
        Case Else: sTemp = sTemp
     End Select
    If Align = 1 Then
        sTemp = sTemp & Space(nLen - Len(sTemp))
      Else          ' Align must = 3
        sTemp = Space(nLen - Len(sTemp)) & sTemp
    End If[/B]
     
    [B]strTextLine = strTextLine & sTemp[/B]
    
    If NewLine Then
        f.Writeln (strTextLine)
        strTextLine = ""
    End If
End Sub

Re: Auto complete like winamp's media library

SoulBlade,

You need to change the SQL behind the list box such that the Like expression has a * infront of it.

Like "*" & nz([Forms]![Form1]![Text0].[text],"") & "*"

HTH,

Alan.

Re: Transpose table

How much data are you working with?

It could be a red herring, but if the data set is small then you might want to consider using the Excel Copy and Paste Special>Transpose.

A.

Re: Auto complete like winamp's media library

SoulBlade,

Are you suggesting that the user types into a text box and as the user types the list box is updated?

If so then I think you need to add an OnChange event to the list box that will refresh the list box record source. The List box record source would be a SQL statement that links to the text box.

Have a look at the attached mdb.

Try typing
A and the list shortens to Alan
*e and the list shortens to George and Neil

Is this what you need?

A.

Re: Correlation Matrix in VBA

Bobby,

Try this function. I think it should work but do not have suitable test data. If you have some suitable test data and known/expected results then I would test this a little more.

Have you had a look at the Correlation matrix in the data analysis add-in? It may do the task for you.

Function CorrmatK(arrIn() As Variant) As Variant
    'On Error GoTo 20
    Dim r As Integer, n As Integer, rr As Integer, i As Integer, j As Integer, k As Integer, doit As Integer
    Dim x() As Variant, mc() As Double, ss() As Double, m() As Double
    r = UBound(arrIn) - LBound(arrIn)               'Number of rows
    n = UBound(arrIn, 2) - LBound(arrIn, 2)         'Number of cols
    ReDim ss(1 To n, 1 To n), m(1 To n), x(1 To n), mc(1 To n, 1 To n)
    rr = r
    For i = 1 To r
        doit = 0
        For j = 1 To n
            doit = doit + -(Not IsNull(arrIn(i, j)))
            x(j) = arrIn(i, j)
        Next j
        If doit = n Then
            For j = 1 To n
                m(j) = m(j) + x(j)
                For k = 1 To j
                    ss(j, k) = ss(j, k) + x(j) * x(k)
                Next k
            Next j
        Else
            rr = rr - 1
        End If
    Next i
    For j = 1 To n
        ss(j, j) = (ss(j, j) - m(j) * m(j) / rr) ^ 0.5
        mc(j, j) = 1
        For k = 1 To j - 1
            ss(j, k) = ss(j, k) - m(j) * m(k) / rr
            mc(j, k) = ss(j, k) / (ss(j, j) * ss(k, k))
            mc(k, j) = mc(j, k)
        Next k
    Next j
    CorrmatK = mc
    Exit Function
20     CorrmatK = CVErr(xlErrValue)
End Function

Re: Split address details

Edgar,

I just did a Google search on Address Matching and found loads of hits. The first one looks worthwhile.

http://www.qas.com/uk/products/business/cleaning.asp

Good luck,

A.

Re: Split address details

Edgar,

What Will and I are saying is that every time you process data you need to compare the amount of time you spend coding and developing a process with how long it would take you to do it manually. I think that the trick with this data is to develop something that gets you 50% say quickly, but the remainder requires work.

To answer your questions:

Quote

If I had two tables with City and County names. Would it be possible to match these fields to the address field so I could determine which city or county each address is in?

This might help in some cases, but what about if the address is on Guildford Road, or the address gives the name of a house or estate/subburb that could be misinterpreted. You could spend ages coding tocatch all of these possibles and actually not help the overall process.

Quote

I have to say I am bit confused by your solution using a word processor. Could you explain in a bit more detail?

The method basically assumes that you remove the commas and replace them with another character to delimit the different lines of the address.

If this is genuinely a large job that will be done frequently then you may want to research 3rd party software as Will has suggested. This may at least help you get many more correct. Nonetheless, I suspect that you will still have to have a human to look at the results and correct any mistakes.

I think that your solution needs a little more thought by myself before an acceptable solution can be found.

HTH,

A.