Replies

ruirib
I don’t know exactly what type of environment you are running on

At the moment my main PC and 2 test PC’s are Win XP, ex corporate office IBM, they get rid of them for a tax write-off and a song.

Major upgrade soon, just waiting for what goes with Win 10, no hurry, but it is inevitable because the server and the company, the public section have already stated that soon anything less than Win7 won’t be allowed and anything less than IE9 also won’t be allowed.
The “best” XP can do is IE8.

I am thinking of simply going to Win 7 wait around, see what goes.
Not really too keen on Win 10 and MS’s subscription policies etc.

FYI, this project has one entire objective; that is in real time gather/scrape information from a web page using VBA dynamic web query, crunch numbers, evaluate, statistics, graphs, strategies and a lot more, too much to add.

It is based on hands free wagering systems, the very first type was before the internet, it’s architecture was based around windows 95/98 environment. Before in it was DOS with GUI, everything had to be typed in.

There was a version of this type of number crunching, semi-auto using Assembly.

My point is, many have or are trying to successfully experiment using similar source of real-time web page data.
Since then, many of these types of “systems” have fallen over, downtime after downtime and IT evolving mush faster than most can keep up with, only IT academics stay in the play,( or those who can afford to pay for one), but as hobby or full on profession.
(In layman’s terms, a mechanical engineer leaves a stable job and decides to offer his/her profession in Formula 1 Racing, unstable because of the risk factor when the engineer has no control when someone else is in the driver’s seat).

For me this is not a hobby, but I am no way capable of learning new things all the time, therefore this VBA project is infinitely “proto-type”.
Meaning, if I can just get some sort of statistical evidence to allow me do what I do better than, get a home run without revealing too much of my own strategy, then I can show someone and say, “here, take a look at this architecture, how does it go to stand-alone, BUT, free open source?; so the constant evolutionary changes in IT on this particular interest is not cost bearing and limiting experiments of probability from a,theory, to b,practice, and then c,fact” ?

Well, I’ve done b and it appeared good enough to move on to c.
That’s why I am at, at the point of communicating with the server.

2 Years ago it was simple, the server’s propriety owners provided their customers with their own free software to communicate via a .txt file place in que inside a directory.
That free public software is soon to be decommissioned, so they provided an alternative, hence JSON which is a new addition as far a I’m aware of, till the next “new thing”, and then we start over all again.

That’s why I insisted on open source, things keep changing in this neck of the woods, hopefully the savvier IT people can pitch in from time to time. Bottom line, it’s sport just like NASCAR or Formula 1, ( probability and risk).
Hope this explained your question about, “environment”.

—,

but if you are considering Python, I would probably think using .Net would be a logical alternative.

I never knew anything about python or it’s existence till about a month ago.
All I found out is that VBA won’t “talk” directly to JSON that I am aware of.
—,

I would probably think using .Net would be a logical alternative. .Net supports multiple languages, including Visual Basic.Net (VB.Net), which shouldn’t be too strange to someone who uses VBA. .Net is a rich environment, with a lot of open source code available to consume and communicate JSON. Visual Studio, Microsoft’s premier development IDE, uses Json.NET, a free, open source framework.

I have zilch-zero-nada, no experience with any of those.
All I have is statistics and architecture from own research.
—,

I am not sure what you are trying to do and how you are trying to do it.

Neither am I !
—,

The JSON here is this bit:

Code:
{“Username”:”yourusername”, “Password”:”yourpassword”, “Dob”:””, “DeviceName”:””, “DeviceKey”:””, “Referrer”:””}
Body = “{“”Username””:””” & userName & “””, “”Password””:””” & userPass & “””, “”Dob””:””””, “”DeviceName””:””””, “”DeviceKey””:””””, “”Referrer””:””””}”
Is this all you need to send?

The rest of the code is mostly to communicate with the server.

The username and password, since posting here last, I got it to work, it got the sessionID, the log-in and the last bit.
All was by my instance to use a .txt file/s made by VBA, and python’s script reads the txt files and sends that data to the server.
That phase worked, it got a home run. The entire “Loop” joined, finally for this initial phase.
They insisted python “talk” directly to Excel bypassing the use of txt files.
Well I insisted No, because if that was to have been the case, I be “trapped” and have to use something I have no experience with. How the heck to I know what’s in those scripts ?

However there is more, one final “home run” which is, the communication back from the server after the login, is the next thing to do. It’s official code title is “RESPONSE”
This code sample is also publicly available.
http://media.tatts.com/documentations/ICustomer/Methods/SellWageringToteBets.html

Look for the first code sample heading with the words that includes RESPONSE, the first sample is REQUEST.
I have up to REQUEST in Python scripts and it all worked as mention earlier in this post.
It may be interesting to see what .NET scripting may look like script form for, RESPONSE ?
To evaluate which which way to go with the project at this point
—,

Thanks.

I can’t say if this is literally solved, but some help from a referrer got it to work using python as the “go between”, between VBA and JSON.

VBA and JSON, don’t “talk” to each other.

VBA and python do “talk” to each other

python “talks” to JSON.

VBA makes txt files from data inside workbook cell references, the data JSON requires.

python “reads” the txt files made by VBA
or, sends that data within, to JSON.

JSON then works.

However, to get JSON to communicate back is another hill to climb, it has to eventually
get back to VBA, via python, again ?
So VBA can log.txt what JSON is doing, done, or if there was an error.

Conclusion from what I gather and understand,
It was suggested we now learn python.
Personally I do not want to learn python, I can hardly do VBA, what I do is rather basic to say the least.

But if this project with Excel/VBA is to continue, some sort of language other than python needs to be learnt to communicate to JSON via Excel VBA .txt files ?
If not python, what other is there ?

Wish list
Can VBA “talk” directly to JSON ?

But it worked, the result was positive as in the, functions of all manner of things using python as the go-between

“dumb” question.

If then JSON, “new kid on the block” becomes the chosen one for what it supersedes, and what it supersedes currently is on “talking terms” with VBA, what then if VBA and JSON don’t communicate ?

RG, I tested your code changes, but, the point about 2 of the files the 3rd party program reads, has to be specific, which is out of my control and that’s ok.

The original LOGMESSAGE code has time and date stamps, OK, Good

Code:

‘// ‘Open’ will create the file if it doesn’t already exist
        Open zLogFile For Append As #iFileNumber
        Write #iFileNumber, Format$(Now, “yyyymmdd-hh:nn:ss”) & “>” & strMessage

Because the 3rd party program uses the minimal amount of required data, that section of the original LOGMESSAGER has been cut out, it looks like:

Code:

 // ‘Open’ will create the file if it doesn’t already exist
        Open strFileName For Output As #iFileNumber
         Print #iFileNumber, strMessage

What I was hoping, if both choices can be implemented in the one single code, by way of adding a point of reference/match when the code is Run
So if it is a time/date stamp logmessage, it’s that, but if it is for the 3rd party it has time/date omitted.
Can the VBA be written in such a way to omit the time/date stamp if not required ?

And 2ndly,
if the 3rd party program requires a 3rd, more or less .txt files, I simply add another of the same lines of code with reference pertaining to the txt files name.

So it may be
LOGMESSAGE_ORIGINAL’ ( uses the time/date stamp)

LOGMESSAGE_3RD PARTY_1, ( it’s .txt file name would be accordingly so 3rd party “knows” the file.
eg: 3rd_party_1TEXT.txt

LOGMESSAGE_3RD PARTY_2, ( it’s .txt file name would be accordingly so 3rd party “knows” the file.
eg: 3rd_party_2TEXT.txt

LOGMESSAGE_3RD PARTY_3, ( it’s .txt file name would be accordingly so 3rd party “knows” the file.
eg: 3rd_party_3TEXT.txt

all in one Module.
———,

If not, then the above example would have to use 4 separate Modules, 1 Module; LOGMESSAGE / original and 3 more Modules as the requested requirements by the 3rd party software makers.

( 3rd party software makers don’t give a kahoot on how to make the .txt files, they just want the data inside the .txt files a certain way so it’s picked up and read.

The current way I have it now works,
just thought if there is a way so to lessen the WorkBook’s excesses, or
“if it ain’t broken, don’t fix it” ?

Thanks

Where is the JSON code written on ?
In a Excel VBA module ?
I have provided a workbook to try this and it errors all over the module in reds.

Here is a complete code provided publicly, but no information on where it goes.
This I think this is exactly what’s required.

Code:

Sub Tatts_Login()

Dim sURL As String, sHTML As String
Dim oHttp As Object
Dim userName As String
Dim userPass As String
Dim Body As String
Dim sessionID As String ‘Add this variable to the General Declarations section for use outside of this subroutine

‘‘Change the details below to access your account
‘userName = ‘Your user Number / ID / Email here’
‘userPass = “Your Account Password here”

‘‘Add a reference to the MSXML type library
Set oHttp = CreateObject(“MSXML2.XMLHTTP”)
‘or
‘Set objRequest = New MSXML2.XMLHTTP

‘‘The website to login to your account
sURL = “https://api.tatts.com/sales/vmax/web/account/login”

‘‘ The json string to send. It is paramount that the syntax is correct, the result must return: {“Username”:”yourusername”, “Password”:”yourpassword”, “Dob”:””, “DeviceName”:””, “DeviceKey”:””, “Referrer”:””}
Body = “{“”Username””:””” & userName & “””, “”Password””:””” & userPass & “””, “”Dob””:””””, “”DeviceName””:””””, “”DeviceKey””:””””, “”Referrer””:””””}”

oHttp.Open “POST”, sURL, False
oHttp.setRequestHeader “Content – type”, “application / json”
oHttp.setRequestHeader “Accept”, “application / json”

‘‘Send the login string to the server. Adding parentheses around (Body) forces it to be passed By Value.
oHttp.send (Body)

‘‘Server response.
sHTML = oHttp.responseText

‘‘The sessionID is required for a majority of the calls to the server
sessionID = Mid(sHTML, InStr(1, sHTML, “SessionId”) + 12, 36) ‘InStr(1, sHTML, “}”) – 35)

‘‘Verify that the login was successful.
If Mid(sHTML, Len(sHTML) – 5, 6) = “:true}” Then
MsgBox “Logged ON, SessionID: ” & sessionID

Else

Set oHttp = Nothing
MsgBox “An error occured logging on to the Tatts site. Please re-enter your login details.”
Exit Sub
End If

Set oHttp = Nothing

End Sub

OK

here it is..

http://media.tatts.com/documentations/ICustomer/Methods/SellWageringToteBets.html

The, WP, MR, 5, 4, 10 and 0.0 with passwords etc in the code below would be ready in a Sheet in whatever Cell Ranges to suit.
In other words, stopping the process to type this data, (WP, MR, 5, 4, 10 and 0.0 with passwords etc) manually won’t do.

“BetType”: “WP”,
“MeetingCode”: “MR”,
“RaceNumber”: 5,
“Runners”: [4]
“WinInvestment”: 10,
“PlaceInvestment”: 0.0

{
“CustomerSession”: {
“SessionId”: “29012788-71e0-4042-8609-d31a02ea32eb”
},
“Bets”: [
{
“BetType”: “WP”,
“MeetingCode”: “MR”,
“IsPresale”: false,
“RaceNumber”: 5,
“IsRover”: false,
“Selections”: [
{
“Field”: false,
“Runners”: [4]
}
],
“WinInvestment”: 10,
“PlaceInvestment”: 0.0
}
]
}

Are you able to post the JSON code or description?

Sent some interim info

Thanks

I fear I cannot help much, as I don’t use JSON from Excel

No prob,

It was un-forseen change, but expected. The company was once thriving with VBA coders to access the server, then it went to XML, (I still stuck around with my proto-type development on test mode in VBA, just to sort out the logic and do the stats.), now that my initial tests and tweaking of the entire workbook, works, and was hoping some of the company’s old stand-alone software was the next step in the process, they jumped onto this JASON.
Perfectly annoyingly normal, gotten used to constant changes in this part of the IT neck of the woods.
I’m not the only one who’s going, “ohh shhh…,”

I did read somewhere on those links, they illustrated where Excel/VBA does OK with XML, but not with the JASON language, for now. From what I understand, has to be converted, I think.
Then I tried to log into their forum to ask specificaly the issue, but first have to become a Google member.

I don’t want to become a Google member, I vote “No”, and wait. :rolleyes:

Thanks.

ruirib , thanks.
I’ll just keep reading, might “get it”

Thanks Paul T
I checked out the links, it has xml and stuff I am not familiar with, but thank you.

My current project in the Excel Workbook uses VBA, the web query is VBA from that same company’s public web page which is the data I use, mostly numbers.
This does not need to be changed. The Owners of the server are not asking me or their clients to change anything I do.
What has change, or changing is the way the company wants it’s clients/customers to access the server.
They have provided a specific script to do this, basically.
There are fields to add my username/email/member number;, password and then a sessionID, whatever that is and I don’t know where to get one from, except it’s somewhere accessible.

At the end of *my* processing, lets say on Sheet10, I have the required data they ask for, except this sessionID they also require, for now.

So if my required details are on Sheet10, what else is required of me to do to let their provided script in JASON know what is the Ranges within Sheet10 ? How do each of the languages “talk” ?

Thanks
:rolleyes:

Sub macrodfdsc()

‘ Interior.Color = 65535
‘ActiveCell.Offset(0, -2#).Interior.Color = 65535

Selection.Offset(0, -3).Resize(1, 8).Interior.Color = 65535 ‘*Down*
End Sub

Selection.Offset(0, 1).Select ‘*Right*
Selection.Offset(0, -1).Select ‘*Left*
Selection.Offset(-1, 0).Select ‘*Up*
Selection.Offset(1, 0).Select ‘*Down*

Selection.Offset(2, 0).Resize(5, 8).Select ‘*Down*

Maudibe
XP, Can you change the formula in I 13 from

=IFERROR((D13-$H13)*100/$D13,””)

to

=IFERROR((D13-$H13)*100/$D13,0)

Thanks,

The workaround I think is to use some VBA, to remove formulas if data rows are blank.
There is usually a Minimum of 7 to a max of 16, in this case it was 15 items in the example sheet.
Then “re-set” the formula using some VBA

If I correct the formula on that entire column ( from to $D to $H)
=IFERROR((D13-$H13)*100/$H13,0)
then it puts the 13 at the very top, showing (100)

It’s preferred in the middle, but I really don’t know what is correct at this stage till some tests are done over time.

Is it
=IFERROR((D13-$H13)*100/$D13,0)
or
=IFERROR((D13-$H13)*100/$H13,0)

kweaver
When you divide by D13 (which is a 0 in your sheet), this is an error so you get “” in the cell.

Thanks for the reply,

Overall, the sheet is not “static”, the entire workbook is not static either, it is real time web query.
The updated numerical values may occur from every 30 seconds to 1-2 minutes, depends.
It only updates because there was a change. Meaning, it may query several times within the minute and receive the exact same data, each query is processed to calculate if there was a change. If there is a change, it does the next thing. Hence the titles in Rows 17 and 18.

The Value in D13 is not actually an error, it is a previous calculation from 2 sets of data and doing a percent comparison of those 2 sets, yet the item number/s corresponding in A1 are relevant overall
If both sets of data are exactly the same value, it becomes a 0, and that’s fine as an indicator,
there was no change from one update to the next during the web query on that particular item, in this case 13.

Also, the SMALL function compares numeric values. If you have a blank in the range, the comparison will return an error which you than turn into a “”.
What do you want it to be?

Yes, I thought so to, a choice has to be made.
The reason it’s “” is because it leaves the cell blank and looks neater rather than have unnecessary clutter.

Why wouldn’t you use VLOOKUP rather than index & match?

Not sure how VLOOKUP work in this case.

Couldn’t you also use ROW() rather than ROW(1:1) ?

I used the same formula from another sheet I learnt here on this forum.
I am not sure why the ROW() is there either other than it works and every time I change things it messes up.
So I left it as is, it seems to work.
—————
In conclusion, not every web query update of real time data calculates a 0.

I just thought if there is a way to over come this not so often glitch, I’d ask.

Thanks

I see, had the shoe on the wrong foot !
The RG say’s I had the wrong shoes for the right feet, backwards to !

Thnaks !

Thanks Rory, it don’t seem to work.
The formula is in Cell L20

Cell A4 is actually a 0 or 1 type of “switch”.
So if it’s 1, then the first part of the formula works, if it’s 0 then the second If has to show the value
in Cell F4

Thanks

Record Macro ??

Code:

Sub Macro2()

    ActiveCell.FormulaR1C1 = _
        “=IFERROR(INDEX(‘Raw Data’!R2C3:R5000C3,SMALL(IF((‘Raw Data’!R2C1:R5000C1=R1C[-2])*(‘Raw Data’!R2C2:R5000C2″”””)*(IF((‘Raw Data’!R2C2:R5000C2=R2C27)+(‘Raw Data’!R2C2:R5000C2=R3C27)+(‘Raw Data’!R2C2:R5000C2=R4C27)+(‘Raw Data’!R2C2:R5000C2=R5C27)+(‘Raw Data’!R2C2:R5000C2=R6C27)+(‘Raw Data’!R2C2:R5000C2=R7C27)+(‘Raw Data’!R2C2:R5000C2=R8C27)+(‘Raw Data’!R2C2:R5000C2=R” & _
        “Raw Data’!R2C2:R5000C2=R10C27)+(‘Raw Data’!R2C2:R5000C2=R11C27)+(‘Raw Data’!R2C2:R5000C2=R12C27)+(‘Raw Data’!R2C2:R5000C2=R13C27)+(‘Raw Data’!R2C2:R5000C2=R14C27)+(‘Raw Data’!R2C2:R5000C2=R15C27)+(‘Raw Data’!R2C2:R5000C2=R16C27)=1,0,1)),ROW(‘Raw Data’!R2C3:R5000C3)-ROW(‘Raw Data’!R2C3)+1),ROWS(‘Raw Data’!R2C3:’Raw Data’!R[-1]C3))),””””)”
    Range(“D4”).Select
End Sub