MS Excel / VBA help thread

I'd be tempted to look at an api like this https://the-odds-api.com/ or similar. You may need to look around for one that contains more than match odds.

Otherwise, when I've done this in the past it's been a matter of searching round different websites to either find something with a static web address or one that is easily recreated (e.g. the date, team names that follow a constant format, etc)

yep - thought as much. will have a look at those links and see if anything good comes up! cheers @aggi and @Sam_w

Why did I put off learning powerpivot for so long, fml
Last few years battling horrendous formulas and performance issues in excel 2013 which would of been solved in many cases by just learning to use the inbuilt functionality properly

I just installed R-Suite.

That means I'm a data scientist now, right?

Is that like R Studio but 7 better?

Can you use R? If not, dataquest have their new R courses free this week

Trying to teach some lawyers how to do PivotTables. FML.

I've never used it.

I'll do what I do with SAS, and learn enough to parse SQL, and leave it at that.

I honestly have no idea - it was / is the easiest to install with least authorisation required on the company dollar.

It's installed as I need to figure out what someone's underlying SQL queries are doing

Still. Data science tho

In science and data stuff, I'm trying to work out spatial neighbours.

There's a SQL r package, I can't remember its name.

Use r! Is running atm too.

And finally, mango solutions

I have a table of products (rows) and price per month (cols) - Jan to Aug

I want to identify the products that have moved the most over time. What is the best formula for this? More of a statistics question really....

Do you care about absolute change (gone up £5) or relative (gone up 5%), also do you care about month to month changes or Jan to Aug change?

relative - some products cost pence, some cost hundreds of pounds

jan - aug - month on month changes

Calculate your month on month percentage changes [ (m2/m1)-1 ] , then use StDev on each time series of percentage changes, will give you the most volatile prices. Not sure if that's what you want?

i found the max, min and ave then took the (max-ave) + (ave-min) / ave as a percentage

not sure how meaningful this is

am really just killing time until lunch, but it would be nice to have something vaguely correct

that's kind of like the range as a percentage of average price, so not too far off. the top line of your formula has cancelling avg's. you could do range as a percentage of starting price (replace avg in the bottom with start price), which would perhaps be more accurate if you were talking about how much they have moved since x point in time

So you have a standard starting price for each product (jan?)
say that's in cell a1
then feb - aug in b1 - whatever1

so
b1-$a$1 will give you the monthly difference

(b1-$a$1)/$a$1 will give you percentage difference

you can use this page (if you want) to get moving averages
https://www.ablebits.com/office-addins-blog/2015/09/25/moving-average-excel/

I'm hoping someone can help me with a bit of VBA code, which for some reason I am struggling with.
I am looking to select a range in excel.

The starting row is row 15 (this is fixed, won't change)
The starting column contains the word "Coefficient" in row 15

The end row contains the word "Xaxis" in column A
The end column is the same as the starting column

There is not continuous data in the column I am selecting so can't use End(XLDown).

This feels like it should be really simple, but I can't get my head round it, Help!!

Use index(match()) to find the indices for startCol and endRow?

endCol = startCol
ActiveSheet.Range(Cells(startCol, 15), Cells(endCol, endRow)).Select

Edit: actually @Sam_w I'm not sure I understand your lookups. If end column is the same as starting column, then all your cells are in the same column, fine, starting at row 15 and reading downwards, but then you say "end row contains "Xaxis" in column A" which doesn't make sense. Got an example file?

I think you can just use match. I'm not in front of a computer to test but something like

 EndRow = Application.WorksheetFunction.Match("xaxis", Range("A:A"), 0)

If the cell has other stuff than just the word xaxis and you're on the latest version of Excel then you can use xmatch with a wildcard.

EDIT: Actually Match also has a wildcard function. This should do it:

Sub Macro1()

Dim endcol As Long
Dim endrow As Long

Dim rowrng As Range
Set rowrng = Application.Range("A:A")
Dim colrng As Range
Set colrng = Application.Range("15:15")

endrow = Application.Match("*Xaxis*", rowrng, 0)
endcol = Application.Match("*Coefficient*", colrng, 0)
Range(Cells(15, 1), Cells(endrow, endcol)).Select
    
    
End Sub

thanks for the pointers, worked it out from a combination of them and further googling, I am using

        Const WHAT_TO_FINDa As String = "Xaxis"
        Const WHAT_TO_FINDb As String = "Coefficient"

        Set FoundCella = ws.Range("A:A").Find(What:=WHAT_TO_FINDa)
        Set FoundCellb = ws.Range("11:11").Find(What:=WHAT_TO_FINDb)
            
     With ws
    .Cells(15, FoundCellb.Column).Select
    Set selectrange = .Range(.Cells(15, FoundCellb.Column), .Cells(FoundCella.Row, FoundCellb.Column))
  End With
  
        selectrange.Select