Jump to content

Excel Help


Karl

Recommended Posts

Hi guys & ladies

I know many of you are using spreadsheets to record your poker data, and I'm hoping that someone out there may be able to help me with a formula.

Simple spreadsheet, Date, Game, Buyin, Win/Lost, profit and Total.

As I enter data, the total field is updated which is my bankroll, basically I want to copy the last current input of "Total" into another data field, so that I can use that area to display onto my Twitch stream.

As I know the Total will fluctuate up and down, and I just can't find a formula to just grab that last data input to display into another field.

Thanks for any help

Karl

Link to comment
Share on other sites

Hey @Karl. Not sure how tech-savvy you are, but this is how I'd do it.

First, if I've understood you correctly, you got a total column (column f), which is a running total, and the last value in column f will be the current total bankroll. If you want to get the last value in this column/table, you can use a simple offset formula like this: =OFFSET(F1,COUNTA(F:F)-1,0)

Now, I don't know how you're planning to pull it into your Twitch stream? You could of course just capture that particular part of the screen, but I don't think that's the GTO solution. Instead, I'd make Excel write this cell value to a txt file. You then have this txt file content imported in for instance OBS (when you add text, you also have the option to read from file).

It's been a while since I've worked with VBA, and I'm definitely no expert, but you can do it with the following code:


Sub WriteCellValueToTXTFile()
Dim strFile_Path As String
strFile_Path = "C:\Users\User\Documents\test.txt"
Open strFile_Path For Output As #1
Print #1, Worksheets("Sheet2").Range("A1")
Close #1
End Sub

This code basically takes the content of cell A1 on the sheet "Sheet2" and writes it to the txt file just above. In this example I'm assuming you've got the data table on sheet1, and just want this value for stream content on a separate sheet. 

Now, if you want this code to run every time (so update the value in the .txt file) a change is made on Sheet1, just add the following VBA to the sheet:

Private Sub Worksheet_Update(ByVal Target As Range)
Application.EnableEvents = False
WriteCellValueToTXTFile
Application.EnableEvents = True
End Sub

Check the latest poker release notes. Have a look at our poker promotions

Link to comment
Share on other sites

Another option for showing it on OBS without the crazy hackerman stuff is by using google sheets.

Keep track in a googlesheets spreadheet, make sure to make it public (share button top right, change button bottom left, make sure it's set on "anyone with a link" , hit done) .

Then on your spreadsheet, go to the cell that has your total bankroll, right click it, and "Get link to cell". Now that link should be in your clipboard.

In OBS sources ADD > Browser > Name it excel or whatever and hit ok > at URL paste the link you just copied from google sheets and hit ok. Now you have your online excell sheet on your screen.

Then just right click that Source that you just created in Sources > click Filters > bottom left click on the + > Crop/Pad > OK > and then just mess around with the Left Top Right Bottom cropping numbers until you have just your cell on screen.

Now any time that total gets updated in your sheet it gets updated on your screen too.

Yes this is pretty complex too, but if you follow the directions it shouldn't be too hard (assuming that you have OBS studio)

LE: you can use this method to have anything on your stream, maybe graphs that update if you'll learn how to do that, for example, without the need to have it open on your other screen and captured, with this method you don't even need the spreadsheet open as it's all online.

 

Link to comment
Share on other sites

I kind of understand you @Stubbe-Unibet, and the problem is that I have not used Excel for years and struggle to remember most of the stuff I knew lol

Anyhow here's a screen shot of what I'm up to, and you're right in what you understand what I'm talking about.

1239348587_spreadsheethelp.thumb.png.2af78c2e5b1f004f9af6552587346788.png

Just the simplest thing that got me losted.

G9 which is latest entry to be updated into I6, and I6 to be updated as more entries are entered

I6 is where I can capture that field to import onto my twitch stream

I can't believe that I had to resort to asking for help

Grateful for your help in this problem 😃

Link to comment
Share on other sites

@Karl  google sheets is very very similar to microsoft excel, same formulas, same basic stuff, probably missing some of the very advanced things but I doubt you need those.

For having the total in a specific cell, instead of "moving"  the last cell  , why don't you go to the target cell and type =SUM( and then select the entire profit column without the header, go as far down as you want, that should give you a fixed total cell that updates every time you add a new tournament. So in this case I guess it would be =SUM(F6:F1000) that you would put in the I6 cell

Link to comment
Share on other sites


@Karl wrote:

840558732_spreadsheethelp.thumb.png.de9e5ffdd3182cd0ccb5f0867d5392c4.png

G9 which is latest entry to be updated into I6, and I6 to be updated as more entries are entered

I6 is where I can capture that field to import onto my twitch stream


In that case, use the offset formula in my previous post. In your case just make it =OFFSET(G6,COUNTA(G:G)-1,0)

I might put together a small package tomorrow (I'm off today) with an Excel sheet and the txt file, so all you need to do is download the two files, start adding your own tournament data and pull the txt file into OBS/Streamlabs/xsplit/whatever :)

Check the latest poker release notes. Have a look at our poker promotions

Link to comment
Share on other sites

screengrab2.png.47c0fc83d136b9a4b2ea704bf1c2df13.png

 =sum just add everything up in the coloumn and doing a sum to include "F" & "G" coloumns gave different answer....confusing lol

which is why I wanted to have a formula or something that just gave me the last entry for "G" column, as that column has already been calculated up to the last entry :Smile:

For the life of me....it escapes me as to what needed to be in I6 to give me that answer

I've worked how to export to text file now, so that's an improvement :Rofl:

Link to comment
Share on other sites

@Karl, you're welcome :) As long as you don't change the format of the sheet, it'll indeed update automatically. What the formula does is basically count the number of cells with content in column G. It then uses this number to count down (offset) the number of rows from G6, from which it should return the value :)

The OFFSET formula format consists of REFERENCE,ROWS,COLUMNS, and in this case we got G6 as the reference, as this is the cell from which we count down and find the last value. For the rows, we use COUNTA(G:G), to count the number of cells with content. As you got a header in column g, we need to add -1. Columns is irrelevant in this case, as it's all in column G, so we end up with the following formula:
=OFFSET(G6,COUNTA(G:G)-1,0).

There're a few other ways you could do it. You could for instance use index (combined with count) or a simple lookup.

giphy

  • Like 1

Check the latest poker release notes. Have a look at our poker promotions

Link to comment
Share on other sites

Thanks @Stubbe-Unibet 

It's starting to come back to me with all this excel malarkey, it's hard to believe that I've got equivalent to Phd in computing......just shows that when you've not done things you've trained to do for a long long time, you forget things 😏

I knew what I wanted to do, but my mind was blank.

But it has been quite refreshing to know that staff at Unibet are very customer focused and damn right helpful.

Once again... a big thankyou for your help 🆗

  • Like 3
Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...