cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Rank 11
Rank 11

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

0 Likes
Reply
14 REPLIES 14
Highlighted
Unibet Poker Expert
Unibet Poker Expert

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

0 Likes
Reply
Highlighted
Unibet Poker Expert
Unibet Poker Expert
If the above is like latin to you, feel free to just send me the sheet and the file path of your text file, and I'll quickly make the changes for you Smile
0 Likes
Reply
Highlighted
Rank 20
Rank 20

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.

 

0 Likes
Reply
Highlighted
Rank 11
Rank 11

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.

spreadsheet help.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 Smile

0 Likes
Reply
Highlighted
Rank 11
Rank 11

Thanks for that @AdrianN_MP 

Being old school excel, it's easier for me to stick with what I know.....old guy here needs to be talked to like he's a five year old

But later I will endover to try your method to improve my twitch stream Cool

0 Likes
Reply
Highlighted
Rank 20
Rank 20

@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

0 Likes
Reply
Highlighted
Unibet Poker Expert
Unibet Poker Expert

@Karl wrote:

spreadsheet help.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 Smile

0 Likes
Reply
Highlighted
Rank 11
Rank 11

screengrab2.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

0 Likes
Reply
Highlighted
Unibet Poker Expert
Unibet Poker Expert
@Karl just put the previously posted formula in cell i6 Smile
=OFFSET(G6,COUNTA(G:G)-1,0)
0 Likes
Reply